Listing Master Information For Materialized Views

Listing Information About Materialized Views

This section contains queries that you can run to display information about the materialized views at a replication site.

Listing Master Information For Materialized Views

The following query shows the master for each materialized view at a replication site and whether the materialized view can be fast refreshed:

COLUMN MVIEW_NAME HEADING 'Materialized|View Name' FORMAT A15
COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN MASTER_LINK HEADING 'Master Link' FORMAT A30
COLUMN Fast_Refresh HEADING 'Fast|Refreshable?' FORMAT A16

SELECT MVIEW_NAME, 
       OWNER, 
       MASTER_LINK,  
       DECODE(FAST_REFRESHABLE, 
              'NO', 'NO',
              'DML', 'YES',    
              'DIRLOAD', 'DIRECT LOAD ONLY',
              'DIRLOAD_DML', 'YES',
              'DIRLOAD_LIMITEDDML', 'LIMITED') Fast_Refresh
    FROM DBA_MVIEWS;

Your output looks similar to the following:

Materialized                                              Fast
View Name       Owner      Master Link                    Refreshable?
--------------- ---------- ------------------------------ ----------------
COUNTRIES_MV1   HR         @ORC1.WORLD                    YES
DEPARTMENTS_MV1 HR         @ORC1.WORLD                    YES
EMPLOYEES_MV1   HR         @ORC1.WORLD                    YES
JOBS_MV1        HR         @ORC1.WORLD                    YES
JOB_HISTORY_MV1 HR         @ORC1.WORLD                    YES
LOCATIONS_MV1   HR         @ORC1.WORLD                    YES
REGIONS_MV1     HR         @ORC1.WORLD                    YES