When querying the status of materialized view and other information, monitoring and management, it is necessary to query the system view. The following lists the commonly used query statements for materialized view status, dependency Association, and batch maintenance, which can be modified according to the specific situation.

The system table fields used in the statement are described in Materialized view related metadata view field description

Basic information query

-- Basic information of materialized view
    SELECT OWNER            , --Owner account
           MVIEW_NAME       , --Name
           LAST_REFRESH_DATE, --Last updated
           REFRESH_METHOD   , --Update method
           INVALID          , --Failure or not
           QUERY              --Query statement
      FROM USER_MVIEW_ANALYSIS 
  ORDER BY LAST_REFRESH_DATE DESC;

Materialized view output column

     SELECT a.OWNER        , --Owner account
            a.MVIEW_NAME   , --Name
            b.COLUMN_NAME  , --Column name
            b.COMMENTS       --Column comment
       FROM USER_MVIEW_ANALYSIS a
 INNER JOIN USER_COL_COMMENTS b
         ON a.MVIEW_NAME = b.TABLE_NAME;

Which base tables are referenced by materialized views

-- Materialized view association table
        SELECT DISTINCT 
               A.OWNER,          --Owner account
               A.MVIEW_NAME     ,--Materialized view name
               B.COMMENTS       ,--View annotation
               A.DETAILOBJ_OWNER,--Base table owner account
               A.DETAILOBJ_NAME  --Base table name
          FROM USER_MVIEW_DETAIL_RELATIONS A
    INNER JOIN USER_MVIEW_COMMENTS B ON A.MVIEW_NAME = B.MVIEW_NAME
         WHERE DETAILOBJ_NAME NOT LIKE 'BIN%';

Materialized view base table stored procedure reference relationship

-- What common tables are referenced by materialized views or common views, and which stored procedures are referenced by views
WITH CTE AS (
    SELECT DISTINCT 
           A.OWNER,               -- Owner account
           A.MVIEW_NAME         , -- Materialized view name
           A.DETAILOBJ_OWNER    , -- Account number of the referenced table
           A.DETAILOBJ_NAME       -- Referenced table name
      FROM USER_MVIEW_DETAIL_RELATIONS A
     WHERE A.DETAILOBJ_NAME NOT LIKE 'BIN%'
 UNION ALL
    SELECT '' AS OWNER,
           NAME AS MVIEW_NAME,
           REFERENCED_OWNER AS DETAILOBJ_OWNER,
           REFERENCED_NAME AS DETAILOBJ_NAME
      FROM USER_DEPENDENCIES 
     WHERE TYPE ='VIEW'
)
    SELECT A.OWNER            ,-- View owner account
           A.MVIEW_NAME AS MV ,-- View name
           A.DETAILOBJ_OWNER  ,-- Base table owner account
           A.DETAILOBJ_NAME   ,-- Base table/view
           NVL(B.NAME,'-') AS PROC_NAME -- Stored procedure name
      FROM CTE A
 LEFT JOIN (SELECT DISTINCT NAME,REFERENCED_NAME 
              FROM USER_DEPENDENCIES B 
             WHERE B.TYPE = 'PROCEDURE' 
            ) B ON A.MVIEW_NAME = B.REFERENCED_NAME
  ORDER BY A.MVIEW_NAME,A.DETAILOBJ_NAME,B.NAME
    ;

Batch delete materialized view log

Copy out the results for execution.

-- Batch delete materialized view log
SELECT MASTER,'DROP MATERIALIZED VIEW LOG ON ' || MASTER || ' ;' AS DSQL 
  FROM USER_MVIEW_LOGS;

Delete materialized view logs of unreferenced base tables

Copy out the results for execution.

-- Delete materialized view log of base table not referenced by materialized view
SELECT MASTER,'DROP MATERIALIZED VIEW LOG ON ' || MASTER || ';'  AS A
  FROM ALL_MVIEW_LOGS 
 WHERE MASTER NOT IN (
           SELECT DETAILOBJ_NAME 
             FROM USER_MVIEW_DETAIL_RELATIONS 
        ); 

Batch modify materialized view refresh method

Copy out the results for execution.

--Change materialized view to manual incremental refresh
SELECT 'ALTER MATERIALIZED VIEW ' || MVIEW_NAME || ' REFRESH FAST ON DEMAND;' AS S
FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%';
--Manual materialized view incremental refresh
SELECT 'DBMS_MVIEW.REFRESH(''' || MVIEW_NAME || ''',''F'');' AS S
FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%';
--Change materialized view to auto incremental refresh
SELECT 'ALTER MATERIALIZED VIEW ' || MVIEW_NAME || ' REFRESH FAST ON COMMIT;' AS S
FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%';
--Change the materialized view that references a base table to manual incremental refresh
SELECT 'ALTER MATERIALIZED VIEW ' || MVIEW_NAME || ' REFRESH FAST ON DEMAND;' AS S
FROM USER_MVIEWS
WHERE MVIEW_NAME IN (
      SELECT DISTINCT NAME 
      FROM USER_DEPENDENCIES 
      WHERE TYPE = 'MATERIALIZED VIEW' 
      AND REFERENCED_NAME LIKE '%table_name%' -- Base table name
);

Materialized view refresh information

--Materialized view refresh information
SELECT MVIEW_NAME,--Materialized view name
       REFRESH_MODE,--refurbish mode 
       REFRESH_METHOD,--refresh type 
       FAST_REFRESHABLE,--Refresh or not
       LAST_REFRESH_TYPE,--Last refresh type
       LAST_REFRESH_DATE,--Last refresh time
       STALENESS --Whether the data is out of date
  FROM USER_MVIEWS 
 --WHERE MVIEW_NAME = 'MV1'
 ORDER BY LAST_REFRESH_DATE DESC;

Latest refresh information of materialized view

--Query the latest refresh information of materialized view
 SELECT MVIEW_NAME,  --Materialized view name
        LAST_REFRESH_DATE "START_TIME", --Refresh start time
        CASE WHEN FULLREFRESHTIM <> 0 
             THEN LAST_REFRESH_DATE + FULLREFRESHTIM/60/60/24
             WHEN INCREFRESHTIM <> 0 
             THEN LAST_REFRESH_DATE + INCREFRESHTIM/60/60/24
             ELSE LAST_REFRESH_DATE
        END "END_TIME", --Refresh end time
        FULLREFRESHTIM, --Full refresh time
        INCREFRESHTIM , --Incremental refresh time
        REFRESH_METHOD, --Last refresh method
        SUMMARY       , --Include aggregate query or not
        INC_REFRESHABLE,--Incremental refresh supported
        INVALID       , --Failure or not
        REWRITE_ENABLED --Whether query rewrite is supported
   FROM ALL_MVIEW_ANALYSIS
  WHERE OWNER='OWNER' --Materialized view owner account
ORDER BY LAST_REFRESH_DATE DESC;

More