【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;