Listing the Properties of Materialized Views

Listing the Properties of Materialized Views

You can use the query in this section to list the following information about the materialized views at the current replication site:

• The name of each materialized view
• The owner of each materialized view
• The refresh method used by each materialized view: COMPLETE, FORCE, FAST, or NEVER
• Whether a materialized view is updatable
• The last date on which each materialized view was refreshed

Run the following query to list this information:

COLUMN MVIEW_NAME HEADING 'Materialized|View Name' FORMAT A15
COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN REFRESH_METHOD HEADING 'Refresh|Method' FORMAT A10
COLUMN UPDATABLE HEADING 'Updatable?' FORMAT A10
COLUMN LAST_REFRESH_DATE HEADING 'Last|Refresh|Date'
COLUMN LAST_REFRESH_TYPE HEADING 'Last|Refresh|Type' FORMAT A15

SELECT MVIEW_NAME, 
       OWNER,
       REFRESH_METHOD, 
       UPDATABLE, 
       LAST_REFRESH_DATE, 
       LAST_REFRESH_TYPE
    FROM DBA_MVIEWS; 

Your output looks similar to the following:

                                                 Last      Last
Materialized               Refresh               Refresh   Refresh
View Name       Owner      Method     Updatable? Date      Type
--------------- ---------- ---------- ---------- --------- ---------------
COUNTRIES_MV1   HR         FAST       Y          21-OCT-03 FAST
DEPARTMENTS_MV1 HR         FAST       Y          21-OCT-03 FAST
EMPLOYEES_MV1   HR         FAST       Y          21-OCT-03 FAST
JOBS_MV1        HR         FAST       Y          21-OCT-03 FAST
JOB_HISTORY_MV1 HR         FAST       Y          21-OCT-03 FAST
LOCATIONS_MV1   HR         FAST       Y          21-OCT-03 FAST
REGIONS_MV1     HR         FAST       Y          21-OCT-03 FAST