R12.2.X – Materialized Views Migration/Implementation in R12.2.X

 

1. Drop the existing Materialized view and underlying table of the materialized view

DROP MATERIALIZED VIEW XXCUST.XXCUST_TEST_MV;

 

 

2. Create the Logical View in your development database.

The Logical View name must be the desired materialized view name with a '#' character appended to it.

A materialized view name must be unique within the first 29 bytes.

A materialized view definition must be stored in an ordinary view called MV_NAME||'#'.

A materialized view definition must specify a column alias for each item in the select list.[minimal]

• Failure to specify a column alias may cause the error ORA-00998 "must name this expression with a column alias".
• Example:

SELECT SUM (EMP.SALARY) AS SUM_EMP_SALARY,

       MIN (EMP.empno) AS MIN_EMP_NUM,

       'XX' || 'YY' AS XXYY

  FROM TEST_EMP EMP

 

 

Logical View Creation Example:

                CREATE OR REPLACE VIEW XXCUST.XXCUST_TEST_MV#

AS

   SELECT UPPER (oracle_username) USERNAME,

          DECODE (read_only_flag,'C','pub','E','applsys','U','apps') USERTYPE

     FROM fnd_oracle_userid

    WHERE read_only_flag IN ('C','E','U');

 

Test the materialized view definition for accuracy before generating the materialized view implementation

SELECT * FROM XXCUST.XXCUST_TEST_MV#;

 

 

3. Generate the Materialized View.

On an editioned database, materialized views are generated from their corresponding logical views using the AD_ZD_MVIEW.UPGRADE procedure.

• Do not attempt to directly create or replace the materialized view implementation. To recreate a materialized view implementation, call the AD_ZD_MVIEW.UPGRADE procedure.

BEGIN

      AD_ZD_MVIEW.UPGRADE('XXCUST', 'XXCUST_TEST_MV');

      COMMIT;

END;

 

 

In this example, the UPGRADE procedure detects that materialized view is missing and generates it from the Logical View. The Materialized View definition is generated by transforming the Logical View query into an equivalent implementation query that directly references the underlying tables and columns.

 

These 4 Rows get created in the process for

·         Logical View (Ending with '#') in Custom Schema

·         Materialized View in Custom Schema

·         Table in Custom Schema

·         Synonym in APPS

 

SELECT object_type, object_name

FROM dba_objects

WHERE object_name like 'XXCUST_TEST_MV%';

4.       Use the ALTER MATERIALIZED VIEW statement to modify an existing materialized view in one or more of the following ways:

·         To change its storage characteristics

·         To change its refresh method, mode, or time

·         To alter its structure so that it is a different type of materialized view

·         To enable or disable query rewrite

 

ALTER MATERIALIZED VIEW XXCUST.XXCUST_TEST_MV REFRESH FORCE ON DEMAND USING TRUSTED CONSTRAINTS DISABLE QUERY REWRITE;

/

 

5.       If the referenced objects are be placed in a non editioned schema that is different from the schema where the mview is to be created, then grant execute permission on the package to the mview owner.References:
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)

 

A materialized view should use 'REFRESH FORCE' instead of 'REFRESH FAST'. The 'FORCE' option allows the materialized view to fall back to using a complete refresh in situations where the fast refresh is not possible.

 

If the materialized view implementation content must be automatically refreshed after patching, then you must include the '/*AUTOREFRESH*/' comment tag in the materialized view definition query.

o    Do not specify the /*AUTOREFRESH*/ tag for large materialized views that will take a long time to refresh. For these cases use a concurrent program to refresh the materialized view after patching cutover.

• Example: create or replace view XXCUST_TEST_MV# as select /*AUTOREFRESH*/ ... ;

 

BEGIN 

      DBMS_MVIEW.REFRESH('XXCUST.XXCUST_TEST_MV','C',ATOMIC_REFRESH=>TRUE);

END;

/

 

Parameters in detail:

Refresh Method	Parameter	Description
COMPLETE	C	Refreshes by recalculating the defining query of the materialized view.
FAST	F	Refreshes 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_PCT	P	Refreshes 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.

 

 

6.       Create a comment to an oracle Materialized view

COMMENT ON MATERIALIZED VIEW XXCUST.XXCUST_TEST_MV IS 'snapshot table for snapshot XXCUST.XXCUST_TEST_MV ';