Skip to main content

Materialized Views in 12.2


There are two steps to create Materialized Views in 12.2
  1. Create Logical View
  2. Upgrade to Materialized View
Create Logical View
CREATE OR REPLACE <Custom Schema>.XXBLOG_CREATE_LOGICAL_MV#
AS
<SELECT Statement>;


Upgrade Logical View to Materialized View
exec ad_zd_mview.upgrade('<custom schema>', 'XXBLOG_CREATE_LOGICAL_MV');

 How to check if the Materialized Views got created properly in 12.2
SELECT object_type, object_name
FROM dba_objects
WHERE object_name like 'XXBLOG_CREATE_LOGICAL_MV%';

 -- 4 rows Selected

 These 4 Rows get created in the process for
  1. Logical View (Ending with '#') in Custom Schema
  2. Materialized View in Custom Schema
  3. Table in Custom Schema
  4. Synonym in APPS

Create mview gives the following error when the mview definition query refers to editioned function:
ORA-38818: illegal reference to editioned object %.%


12.2 E-Business Suite Upgrade Of Custom Materialized Views Fails Due To Changes Required For 12.2 ADOP Editioned Objects (Doc ID 2205375.1)
ORA-38818: Illegal Reference To Editioned Object When Creating Materialized View (Doc ID 1556379.1)
    How to Refresh Materialized Views:
    exec DBMS_MVIEW.REFRESH('XXBLOG_HOWTOREFRESH_MV', method => '?',  atomic_refresh => FALSE, out_of_place => TRUE);


    Parameters in detail:
    Refresh Method
    		ParameterDescription
    COMPLETECRefreshes by recalculating the defining query of the materialized view.
    FASTFRefreshes by incrementally applying changes to the materialized view.
    For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.
    FAST_PCTPRefreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
    FORCE?Attempts a fast refresh. If that is not possible, it does a complete refresh.
    For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.


    Out-of-place refresh: It uses outside tables during refresh as opposed to the existing "in-place" refresh that directly applies changes to the materialized view container table. Out-of-place refresh is particularly effective when handling situations with large amounts of data changes, where conventional DML statements do not scale well.






    Labels:

    Comments

    Post a Comment

    Popular posts from this blog

    HRMS Payroll

    Payroll Introduction Processing payroll is a very typical and fundamental business requirement across enterprises. If we have human resource, we will have to pay them. This sounds pretty simple. When we have 10 odd employees working in our firm, and we write checks for them; this can be done with the help of a spreadsheet and it won’t take a lot of time or resource to get something of this sort to be done. But let's think about a big enterprise. Processing payroll for a big enterprise is a gigantic task to do. It takes a lot of preparation just for the payroll processing and again a lot of post execution steps to make sure all the data are accurate and stored for further usage. Do we simply write checks? What exactly do we do in the entire payroll process? We do a lot of stuff, like calculating an employee's salary, calculating the amount to be paid per pay period. Determining how to pay, by check or by direct deposit into a bank? ...
    Post a Comment
    Read more

    Workflow Important Debug Queries

    deq_time is not always populated in WF_DEFERRED. The best way to monitor is to check if there are any READY events select msg_state,count(*) from applsys.aq$wf_deferred  group by msg_state; For getting Item_Type and Display name for Runnable processes. SELECT WFA_ACT.ITEM_TYPE ITEM_TYPE ,   WFA_ACT.NAME PROCESS_NAME ,   WFA_ACT.DISPLAY_NAME DISPLAY_NAME FROM wf_activities_vl wfa_act WHERE wfa_act.runnable_flag = 'Y' AND wfa_act. type            = 'PROCESS' AND sysdate BETWEEN wfa_act.begin_date AND NVL(wfa_act.end_date, sysdate); Query to find records that are pending in each of the workflow agent listener queues SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||        ')''||'' Count: ''||count(*) c from ' || p.owner || '.' || .queue_table ||        ' where deq_time is null and nvl(delay,enq_time)<sy...
    Post a Comment
    Read more

    How to setup and use AME - Approval Management Engine

    Approval Management Engine - AME For Purchase Requisition Approvals Purchase Requisitions can be routed for approval using the AME Approval Management Engine. This whitepaper describes how to setup AME for use with requisition approvals, and shows how a requisition approval list is built based on the AME setup. Approvers in the AME based approver list are assigned to the requisition based on the AME rules setup for the Purchase Requisition Approval transaction. Similar setup can be done for Requester Change Order Approval and for Internal Requisition Approval, although those are not specifically covered in this whitepaper. The screenshots provided are based on 11i.AME.B, and some of the navigation details are specific to 11i.AME.B. However, most of the details provided are applicable to 11i.AME.A and higher including R12. Assign AME Roles and Responsibilities AME responsibilities in 11i.AME.A are assigned directly to the users. However, In R12 or 11i.AME.B and higher, AME respons...
    Post a Comment
    Read more