【ORACLE】常用物化檢視相關後設資料查詢語句

對物化檢視的狀態等資訊進行查詢，監控和管理時，需要對系統檢視進行查詢，以下列出了常用的物化檢視狀態、依賴關聯，批量維護時能用到的查詢語句，根據具體情況進行適當修改。

語句中使用到的系統表欄位說明請檢視物化檢視相關後設資料檢視欄位說明

基本資訊查詢

-- 物化檢視基本資訊
    SELECT OWNER            , --所有者賬戶
           MVIEW_NAME       , --名稱
           LAST_REFRESH_DATE, --上次更新時間
           REFRESH_METHOD   , --更新方式
           INVALID          , --是否失效
           QUERY              --查詢語句
      FROM USER_MVIEW_ANALYSIS 
  ORDER BY LAST_REFRESH_DATE DESC;

物化檢視輸出列

     SELECT a.OWNER        , --所有者賬戶
            a.MVIEW_NAME   , --名稱
            b.COLUMN_NAME  , --列名稱
            b.COMMENTS       --列註釋
       FROM USER_MVIEW_ANALYSIS a
 INNER JOIN USER_COL_COMMENTS b
         ON a.MVIEW_NAME = b.TABLE_NAME;

物化檢視引用了哪些基表

-- 物化檢視關聯表
        SELECT DISTINCT 
               A.OWNER,          --所有者賬戶
               A.MVIEW_NAME     ,--物化檢視名稱
               B.COMMENTS       ,--檢視註釋
               A.DETAILOBJ_OWNER,--基表所有者賬號
               A.DETAILOBJ_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%';

物化檢視-基表-儲存過程引用關係

-- 物化檢視或普通檢視引用了什麼普通表，檢視被哪些儲存過程引用
WITH CTE AS (
    SELECT DISTINCT 
           A.OWNER,               -- 所有者賬戶
           A.MVIEW_NAME         , -- 物化檢視名稱
           A.DETAILOBJ_OWNER    , -- 被引用表賬號
           A.DETAILOBJ_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            ,-- 檢視所有者賬戶
           A.MVIEW_NAME AS MV ,-- 檢視名稱
           A.DETAILOBJ_OWNER  ,-- 基表所有者賬戶
           A.DETAILOBJ_NAME   ,-- 基表/檢視
           NVL(B.NAME,'-') AS PROC_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
    ;

批量刪除物化檢視日誌

將結果複製出來執行。

-- 批量刪除物化檢視日誌
SELECT MASTER,'DROP MATERIALIZED VIEW LOG ON ' || MASTER || ' ;' AS DSQL 
  FROM USER_MVIEW_LOGS;

刪除沒有被引用的基表的物化檢視日誌

將結果複製出來執行。

-- 刪除沒有被物化檢視引用的基表的物化檢視日誌
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 
        ); 

批量修改物化檢視重新整理方式

將結果複製出來執行。

--修改物化檢視為手動增量重新整理
SELECT 'ALTER MATERIALIZED VIEW ' || MVIEW_NAME || ' REFRESH FAST ON DEMAND;' AS S
FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%';
--手動執行物化檢視增量重新整理
SELECT 'DBMS_MVIEW.REFRESH(''' || MVIEW_NAME || ''',''F'');' AS S
FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%';
--修改物化檢視為自動增量重新整理
SELECT 'ALTER MATERIALIZED VIEW ' || MVIEW_NAME || ' REFRESH FAST ON COMMIT;' AS S
FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%';
--修改引用了某個基表的所有物化檢視為手動增量重新整理
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%' -- 基表名稱
);

物化檢視重新整理資訊

--物化檢視重新整理資訊
SELECT MVIEW_NAME,--物化檢視名稱
       REFRESH_MODE,--重新整理方式
       REFRESH_METHOD,--重新整理型別
       FAST_REFRESHABLE,--是否可重新整理
       LAST_REFRESH_TYPE,--最近一次重新整理型別
       LAST_REFRESH_DATE,--最近一次重新整理時間
       STALENESS --資料是否過時
  FROM USER_MVIEWS 
 --WHERE MVIEW_NAME = 'MV1'
 ORDER BY LAST_REFRESH_DATE DESC;

物化檢視最近一次重新整理資訊

--查詢物化檢視最近一次重新整理資訊
 SELECT MVIEW_NAME,  --物化檢視名稱
        LAST_REFRESH_DATE "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", --重新整理結束時間
        FULLREFRESHTIM, --全量重新整理耗時
        INCREFRESHTIM , --增量重新整理耗時
        REFRESH_METHOD, --最近一次重新整理方式
        SUMMARY       , --是否含有聚合查詢
        INC_REFRESHABLE,--是否支援增量重新整理
        INVALID       , --是否失效
        REWRITE_ENABLED --是否支援查詢重寫
   FROM ALL_MVIEW_ANALYSIS
  WHERE OWNER='OWNER' --物化檢視所有者賬戶
ORDER BY LAST_REFRESH_DATE DESC;

更多