整体流程：1.生产环境A库对两个基表创建一个物化日志，物化视图2.测试环境B库，创建物化视图，通过DB_LINK远程查询3.模拟正常操作及刷新流程4.模拟异常情况，将测试环境连接主库db link,测试环境删除物化视图，模拟主库业务正常操作，最终导致生产环境A库物化视图日志无法被清理，Segments越来越大；1)使用上述之前的操作SQL> select * from my_mv_cd;        ID NAME                 ADDRESS---------- -------------------- --------------------         1 cc                   CC2)测试环境B库，创建物化视图，通过DB_LINK远程查询SQL> create public database link dblink1 connect to yz identified by yz using '(DESCRIPTION =(ADDRESS_LIST  =(ADDRESS =(PROTOCOL = TCP) (HOST = 192.168.20.66)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = tt)))';create materialized view scott.mv_cd_testbuild immediate refresh fast WITH ROWID on demandasselect p.rowid person_rowid,a.rowid address_rowid,p.pid as id, p.name, a.address from test_person@dblink1 p,test_address@dblink1 awhere p.pid = a.aid;生产环境A库可以查询到如下信息SQL> select OWNER,NAME,MVIEW_SITE,MVIEW_ID from DBA_REGISTERED_MVIEWS where name in('MY_MV_CD','MV_CD_TEST');OWNER      NAME                                MVIEW_SITE                       MVIEW_ID---------- ----------------------------------- ------------------------------ ----------YZ         MY_MV_CD                            TT11204                               104SCOTT      MV_CD_TEST                          TT                                    105物化视图的注册信息，以及物化视图对应的db nameSQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS;OWNER      MASTER                         MVIEW_LAS   MVIEW_ID---------- ------------------------------ --------- ----------YZ         TEST_PERSON                    20-AUG-19        105YZ         TEST_ADDRESS                   20-AUG-19        105YZ         TEST_PERSON                    20-AUG-19        104YZ         TEST_ADDRESS                   20-AUG-19        104根据物化视图ID号，可以得到物化视图对应的基表生产环境查询，物化视图信息，最后一次全量刷新，名称SQL> select owner,MVIEW_NAME,LAST_REFRESH_DATE,REFRESH_METHOD,FULLREFRESHTIM,INCREFRESHTIM,INC_REFRESHABLE,KNOWN_STALE,INVALID  from DBA_MVIEW_ANALYSIS;OWNER      MVIEW_NAME                     LAST_REFR REFRESH_ FULLREFRESHTIM INCREFRESHTIM I K I---------- ------------------------------ --------- -------- -------------- ------------- - - -YZ         MY_MV_CD                       20-AUG-19 COMPLETE              0             0 Y N N测试环境查询，物化视图信息，最后一次全量刷新，名称SQL> select owner,MVIEW_NAME,LAST_REFRESH_DATE,REFRESH_METHOD,FULLREFRESHTIM,INCREFRESHTIM,INC_REFRESHABLE,KNOWN_STALE,INVALID  from DBA_MVIEW_ANALYSIS;OWNER                          MVIEW_NAME                     LAST_REFRESH REFRESH_ FULLREFRESHTIM INCREFRESHTIM I K I------------------------------ ------------------------------ ------------ -------- -------------- ------------- - - -SCOTT                          MV_CD_TEST                     18-NOV-19    FAST                  0             0 Y N N3）模拟正常操作及刷新流程思路： 插入测试数据，commit提交；       查询物化视图日志；       手工刷新生产环境本地物化视图，查询物化视图以及日志情况；       插入测试数据，commit提交；       手工刷新测试环境远程物化视图，查询物化视图以及日志情况(此时对于远程物化视图来说，基表的两次测试数据都已应用完成， 但是对于本地来说物化视图存在1条记录并未应用的情况) 插入测试数据SQL> insert into test_person values(2,'dd');SQL> insert into test_address values(2,'dd',2048);SQL>commit;SQL> select SNAPTIME$$,DMLTYPE$$ from  MLOG$_test_person;SNAPTIME$ D--------- -01-JAN-00 ISQL> select SNAPTIME$$,DMLTYPE$$ from  MLOG$_test_address;SNAPTIME$ D--------- -01-JAN-00 I生产环境手工执行刷新存储过程SQL>exec my_mv_cd_procedure_job;查询日志表，SNAPTIME 字段无变化查询生产环境本地物化视图SQL> select * from my_mv_cd;        ID NAME                                ADDRESS---------- ----------------------------------- --------------------         2 dd                                  dd         1 cc                                  CC查询测试环境物化视图(改变未生效)SQL> select * from MV_CD_TEST;SQL> select * from MV_CD_TEST;PERSON_ROWID       ADDRESS_ROWID              ID NAME                                     ADDRESS---- ---------------------------------------- ----------------------------------------AAAV5nAAEAAAbm/AAA AAAV5pAAEAAAbnPAAA          1 cc                                       CC插入测试数据SQL> insert into test_person values(3,'EE');SQL> insert into test_address values(3,'EE',3048);SQL>commit;查询物化视图日志，虽然两个物化视图，一个已经刷新完毕，但是物化视图日志并无变化SQL> select SNAPTIME$$,DMLTYPE$$ from  MLOG$_test_person;SNAPTIME$ D--------- -20-AUG-19 I01-JAN-00 I第二次刷新，刷新远程测试环境物化视图SQL> exec  dbms_mview.refresh('MV_CD_TEST');查询远程，测试环境物化视图SQL>  select * from MV_CD_TEST;PERSON_ROWID       ADDRESS_ROWID              ID NAME       ADDRESS------------------ ------------------ ---------- ---------- ----------AAAV5nAAEAAAbm/AAA AAAV5pAAEAAAbnPAAA          1 cc         CCAAAV5nAAEAAAbm9AAB AAAV5pAAEAAAbnNAAB          3 EE         EEAAAV5nAAEAAAbm9AAA AAAV5pAAEAAAbnNAAA          2 dd         dd查询本地生产环境物化视图日志及本地物化视图SQL> select SNAPTIME$$,DMLTYPE$$ from  MLOG$_test_person;no rows selected！！！  正常情况下不会如此，说明再测试的过程中，本地环境自动刷新同步了，导致物化视图被刷新。删除本地物化刷新JOB，重复上述操作，调整为delete操作BEGIN DBMS_SCHEDULER.DROP_JOB( job_name => 'my_mv_cd_job' ); END; /本地，删除物化视图基表，一条记录，多次刷新本地物化视图，观察现象。SQL> delete test_person where pid=3;SQL> commit;SQL>  select SNAPTIME$$,DMLTYPE$$ from  MLOG$_test_person;SNAPTIME$ D--------- -01-JAN-00 D生产环境手工执行刷新存储过程SQL>exec my_mv_cd_procedure_job;SQL>exec my_mv_cd_procedure_job;查询发现，SQL> select * from my_mv_cd;        ID NAME                 ADDRESS---------- -------------------- --------------------         2 dd                   dd         1 cc                   CCSQL> delete test_person where pid=2;SQL> commit;远程测试环境刷新SQL> exec  dbms_mview.refresh('MV_CD_TEST');SQL> exec  dbms_mview.refresh('MV_CD_TEST');可以发现，删除2条记录，但是物化视图日志，只保留了一条记录，这是本地并未应用的物化视图日志SQL> select SNAPTIME$$,DMLTYPE$$ from  MLOG$_test_person;SNAPTIME$ D--------- -20-AUG-19 D4.模拟异常情况，将测试环境连接主库db link,测试环境删除物化视图，模拟主库业务正常操作，最终导致生产环境A库物化视图日志无法被清理，Segments越来越大；SQL>drop public database link dblink1;SQL> drop materialized view scott.MV_CD_TEST;虽然测试环境已经删除了物化视图，但是生产环境还是记录了物化视图信息，由于DB LINK失效导致，生产环境并未收到关于删除的操作更新。SQL>select OWNER,NAME,MVIEW_SITE,MVIEW_ID from DBA_REGISTERED_MVIEWS where name in('MY_MV_CD','MV_CD_TEST');OWNER      NAME       MVIEW_SITE                       MVIEW_ID---------- ---------- ------------------------------ ----------YZ         MY_MV_CD   TT11204                               104SCOTT      MV_CD_TEST TT                                    105SQL>  SELECT * FROM DBA_BASE_TABLE_MVIEWS;OWNER      MASTER                         MVIEW_LAS   MVIEW_ID---------- ------------------------------ --------- ----------YZ         TEST_PERSON                    20-AUG-19        104YZ         TEST_ADDRESS                   20-AUG-19        104YZ         TEST_PERSON                    20-AUG-19        105YZ         TEST_ADDRESS                   20-AUG-19        105插入测试数据SQL> insert into test_person values(3,'EE');SQL> commit;生产环境手工执行刷新存储过程SQL>exec my_mv_cd_procedure_job;SQL> select SNAPTIME$$,DMLTYPE$$ from  MLOG$_test_person;SNAPTIME$ D--------- -21-AUG-19 ISQL> insert into test_person values(2,'dd');SQL> commit;生产环境手工执行刷新存储过程SQL>exec my_mv_cd_procedure_job;SQL>  select SNAPTIME$$,DMLTYPE$$ from  MLOG$_test_person;SNAPTIME$ D--------- -20-AUG-19 I20-AUG-19 ISQL> select * from my_mv_cd;        ID NAME            ADDRESS---------- -------------------- --------------------         3 EE              EE         1 cc              CC         2 dd              dd可以发现，发生此问题时，物化视图日志将用不被删除，最终实际某生产环境，物化视图日志达到100G水平。https://blog.csdn.net/demonson/article/details/91518341How to purge MLOG$ Tables in the OLTP database (文档 ID 1922560.1)    由于测试环境关闭或者网络问题，导致主库物化视图日志一直保留，需要清除有问题的物化视图日志。SQL>EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(105);SQL> select SNAPTIME$$,DMLTYPE$$ from  MLOG$_test_person;no rows selectedSQL> EXEC DBMS_MVIEW.UNREGISTER_MVIEW('SCOTT', 'MV_CD_TEST', 'TT');SQL> select OWNER,NAME,MVIEW_SITE,MVIEW_ID from DBA_REGISTERED_MVIEWS where name in('MY_MV_CD','MV_CD_TEST');SQL>  SELECT * FROM DBA_BASE_TABLE_MVIEWS;关于注册信息均被删除How to REGISTER and UNREGISTER a Materialized View - Testcase (文档 ID 1393276.1)