Oracle 物化视图和物化视图日志

一、相关概念 物化视图是将查询预先定义在结构中，并手动或者定期刷新将结果存储在物化视图段中，也就是说跟普通视图不同，它是需要存储空间的，从而不需要重新或者反复的执行sql语句，支持增量刷新，快速获取结果，提高数据获取的效率。 物化视图类型根据刷新模式，可分为on demand、on commit 。on demand 是需要刷新时才进行刷新，可以通过job或者手动进行刷新；on commit 是DML型的刷新，一旦事务commit立即刷新。 物化视图的刷新方式有四种：fast、complete、force、never。 --fast 刷新采用增量刷新，只刷新上次刷新以来的修改。 --complete 刷新针对整个物化视图刷新。 --force 在刷新时oracle自动选择刷新方式，满足fast就增量刷新，不满足则选择complete。 --never 不进行任何刷新 二、管理视图 在源数据库端的相关视图    DBA_BASE_TABLE_MVIEWS    DBA_REGISTERED_MVIEWS    DBA_MVIEW_LOGS 在MView数据库端的相关视图     DBA_MVIEWS     DBA_MVIEW_REFRESH_TIMES    DBA_REFRESH和DBA_REFRESH_CHILDREN 源端可以理解为基表所在的库，数据库端是视图存放的位置，基表和视图可以在同一个库中，也可以通过dblink创建分布式的远程的物化视图。 三、问题处理 客户环境Goldengate目标库发现有大表持续增长，表空间占用紧张。 首先根据段空间管理查看近期增长频繁的段，发现是MLOG$命名的表增长很快，MLOG$是物化视图基表上的日志表，根据上面的物化视图原理可以确定是有相关的物化视图没有进行快速刷新，因此这些日志表中的数据会一直增长下去。使用easydb可以轻松的获取资源增长情况，Easydb是袋鼠云研发，目前支持了众多云上云下的客户，有关Easydb的详情参考：https://easydb.dtstack.com。 处理步骤： 1、首先查看有多少物化视图注册到了刷新机制中 SQL select OWNER,NAME,MVIEW_SITE,MVIEW_ID from DBA_REGISTERED_MVIEWS; OWNER        NAME                   MVIEW_SITE            MVIEW_ID --------------- ------------------------------ ------------------------------ ---------- SYSMAN        MGMT_ECM_MD_ALL_TBL_COLUMNS    SEEDDATA                    0 USER2   CIPMV_T_REPORT_2           USER1                      44 USER2    CIPMV_T_REPORT_1          USER1                      43 USER2    CIPMV_T_ORDER_2            USER1                     42 USER2    CIPMV_T_ORDER_1            USER1                      41 查看基表上的物化视图刷新依赖 SQL SELECT * FROM DBA_BASE_TABLE_MVIEWS; OWNER        MASTER                                               MVIEW_LAST_REFRESH_     MVIEW_ID --------------- ------------------------------------------------------------------------------------------ ------------------- ---------- USER1        T_PATIENT                                           2017-09-20 20:00:12        1 USER1        T_REPORT                                           2017-09-20 20:00:47           46 USER1        T_ORDER                                            2017-09-20 20:03:02           45 USER1        T_ORDERREPORTLINK                                       2017-09-20 20:03:02           61 USER1        T_ORDER                                            2017-09-26 14:03:21           42 USER1        T_REPORT                                           2017-09-26 14:03:21           44 USER1        T_ORDER                                            2017-09-26 14:03:22           41 USER1        T_REPORT                                           2017-09-26 14:03:22           43 发现mvid是1、45、46、61四个视图没有注册到刷新中。 2、查找近期进行刷新的物化视图，确定哪些物化视图没有进行刷新，发现有几个MVID对应的物化视图是不存在的，有可能这些物化视图是远程数据库上的。 SQL SELECT * FROM DBA_BASE_TABLE_MVIEWS; OWNER        MASTER                                               MVIEW_LAST_REFRESH_     MVIEW_ID --------------- -------------------------------------------------------------------------------- USER1        T_ORDER                                            2017-09-26 14:03:21           42 USER1        T_REPORT                                           2017-09-26 14:03:21           44 USER1        T_ORDER                                            2017-09-26 14:03:22           41 USER1        T_REPORT                                           2017-09-26 14:03:22           43 1、45、46、61四个视图没有刷新 3、跟客户确认环境，这个库是个灾备库，不需要这些视图刷新，此时我们把这些无效的且注册的物化视图信息去掉. begin DBMS_MVIEW.UNREGISTER_MVIEW(DBLINK,RIS_T_PATIENT_1, ORCL); 4、根据刷新情况清空物化视图日志 EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(46); EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(61); EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(1); 5、此时本地物化视图刷新后，MLOG$中的数据就会随着刷新而清空了。 6、存在的物化视图进行自动刷新 --快速刷新 begin      dbms_mview.refresh(USER2.CIPMV_T_REPORT_2,F); begin      dbms_mview.refresh(USER2.CIPMV_T_REPORT_1,F); begin      dbms_mview.refresh(USER2.CIPMV_T_ORDER_2,F); begin      dbms_mview.refresh(USER2.CIPMV_T_ORDER_1,F); --定时刷新 alter materialized view USER2.CIPMV_T_REPORT_2 refresh fast on demand start with sysdate next to_date(concat(to_char(sysdate+1,dd-mm-yyyy), 22:00:00),dd-mm-yyyy hh24:mi:ss); alter materialized view USER2.CIPMV_T_REPORT_1 refresh fast on demand start with sysdate next to_date(concat(to_char(sysdate+1,dd-mm-yyyy), 22:01:00),dd-mm-yyyy hh24:mi:ss); alter materialized view USER2.CIPMV_T_ORDER_2 refresh fast on demand start with sysdate next to_date(concat(to_char(sysdate+1,dd-mm-yyyy), 22:02:00),dd-mm-yyyy hh24:mi:ss); alter materialized view USER2.CIPMV_T_ORDER_1 refresh fast on demand start with sysdate next to_date(concat(to_char(sysdate+1,dd-mm-yyyy), 22:03:00),dd-mm-yyyy hh24:mi:ss); 总结：本次问题的原因是表被多个物化视图使用，且包含远程物化视图，MLOG物化视图日志如果不被所有已注册的物化视图刷新是不会清空的，保留正常的物化视图即可。
andrew征程 从事数据库服务多年，熟悉Oracle、MySQL数据库架构、优化、容灾等，近三年来关注数据库上云相关服务。