Create Materialized Views in Oracle EBS 12.2 version

Create Materialized Views in Oracle EBS 12.2 version

1, Create Logical View as per below standards

CREATE VIEW XXETC_SAMPLE_MV#

AS

   SELECT org_id,

          b.name AS organization,

          XXETC_TEST_PKG.GET_EMP EMP  --Call Package and Function

     FROM apps.xxetc_mobile_dept_orglist a,

              apps.hr_all_organization_units b

    WHERE a.org_id = b.organization_id

    

2, Upgrade Logical View to Materialized View

EXEC AD_ZD_MVIEW.UPGRADE('APPS', 'XXETC_SAMPLE_MV')

3, Verify with below query

   

SELECT owner,object_name,object_type FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE  'XXETC_SAMPLE_MV'%'

   

APPS    XXETC_SAMPLE_MV    TABLE

APPS    XXETC_SAMPLE_MV    MATERIALIZED VIEW

APPS    XXETC_SAMPLE_MV#    VIEW

4, Refresh Materialized Views:

   

EXEC DBMS_MVIEW.REFRESH('XXETC_SAMPLE_MV', METHOD => '?',  ATOMIC_REFRESH => FALSE, OUT_OF_PLACE => TRUE);

SELECT * FROM XXETC_MOBILE_CONNECTREPORT_MV

For the General Materialized view creation in Oracle DB, follow the steps mentioned in this URL: https://oracle-base.com/articles/misc/materialized-views