对物化视图的状态等信息进行查询，监控和管理时，需要对系统视图进行查询，以下列出了常用的物化视图状态、依赖关联，批量维护时能用到的查询语句，根据具体情况进行适当修改。
语句中使用到的系统表字段说明请查看物化视图相关元数据视图字段说明
基本信息查询123456789-- 物化视图基本信息 SELECT OWNER , --所有者账户 MVIEW_NAME , --名称 LAST_REFRESH_DATE, --上次更新时间 REFRESH_METHOD , --更新方式 INVALID , --是否失效 QUERY --查询语句 FROM USER_MVIEW_ANALYSIS ORDER BY LAST_REFRESH_DATE DESC;
物化视图输出列1234567 SELECT a.OWNER , --所有者账户 a.MVIEW_NAME , --名称 b.COLUMN_NAME , --列名称 b.COMMENTS --列注释 FROM USER_MVIEW_ANALYSIS aINNER JOIN USER_COL_COMMENTS b ON a.MVIEW_NAME = b.TABLE_NAME;
物化视图引用了哪些基表12345678910-- 物化视图关联表 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%';
物化视图-基表-存储过程引用关系1234567891011121314151617181920212223242526272829-- 物化视图或普通视图引用了什么普通表，视图被哪些存储过程引用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 ;
批量删除物化视图日志将结果复制出来执行。
123-- 批量删除物化视图日志SELECT MASTER,'DROP MATERIALIZED VIEW LOG ON ' || MASTER || ' ;' AS DSQL FROM USER_MVIEW_LOGS;
删除没有被引用的基表的物化视图日志将结果复制出来执行。
1234567-- 删除没有被物化视图引用的基表的物化视图日志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 );
批量修改物化视图刷新方式将结果复制出来执行。
123456789101112131415161718--修改物化视图为手动增量刷新SELECT 'ALTER MATERIALIZED VIEW ' || MVIEW_NAME || ' REFRESH FAST ON DEMAND;' AS SFROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%';--手动执行物化视图增量刷新SELECT 'DBMS_MVIEW.REFRESH(''' || MVIEW_NAME || ''',''F'');' AS SFROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%';--修改物化视图为自动增量刷新SELECT 'ALTER MATERIALIZED VIEW ' || MVIEW_NAME || ' REFRESH FAST ON COMMIT;' AS SFROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%';--修改引用了某个基表的所有物化视图为手动增量刷新SELECT 'ALTER MATERIALIZED VIEW ' || MVIEW_NAME || ' REFRESH FAST ON DEMAND;' AS SFROM USER_MVIEWSWHERE MVIEW_NAME IN ( SELECT DISTINCT NAME FROM USER_DEPENDENCIES WHERE TYPE = 'MATERIALIZED VIEW' AND REFERENCED_NAME LIKE '%table_name%' -- 基表名称);
物化视图刷新信息1234567891011--物化视图刷新信息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;
物化视图最近一次刷新信息12345678910111213141516171819--查询物化视图最近一次刷新信息 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;