物化视图日志没有清除
最近在研究物化视图日志的结果，查询了一下系统中物化视图日志的情况，没想到却发现了一个问题：
调用自己写的一个脚本，结果发现个别物化视图日志的数量已经达到了几十万：
SQL> declare
2 v_output number;
3 begin
4 for c_cursor in (select tname from tab where tname like 'MLOG%') LOOP
5 execute immediate 'select count(*) from ' || c_cursor.tname into v_output;
6 dbms_output.put_line(substr(c_cursor.tname, 7) || ' count(*) is ' || v_output);
7 end loop;
8 end;
9 /
CAT_ADMIN count(*) is 0
CAT_AGENT count(*) is 0
CAT_AREA_MEDICARE count(*) is 115416
CAT_AREA_PRICE count(*) is 789700
……
CAT_PRODUCT count(*) is 507262
CAT_PRODUCT_CENTER count(*) is 90945
……
PLT_ORG_PLAT count(*) is 207
PLT_PLAT count(*) is 52
T_DIRECT_SEEDING count(*) is 0
T_INVITE count(*) is 0
T_MARK count(*) is 0
Z_INVITE_COMM_PUB count(*) is 0
PL/SQL procedure successfully completed.
当前查询的站点是主站点，建立了一个主体组。系统中还建立了三个物化视图站点，其中两个刷新全部主体组，一个刷新主体组的一部分。其中两个物化视图刷新组的刷新频率是30分钟，另外一个是1天。因此，物化视图日志中存在少量数据是正常的，但是超过十万条就不正常了。
查询数据量大的一个物化视图日志表，发现物化视图日志中包含着很多以前的数据，时间从2004年8月一直到现在，而物化视图站点最大刷新间隔是1天。
通过查询DBA_BASE_TABLE_VIEWS视图，发现问题：
SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS WHERE MVIEW_LAST_REFRESH_TIME < TRUNC(SYSDATE);
OWNER MASTER MVIEW_LAST_REFRESH_ MVIEW_ID
------- ------------------------------ ------------------- ----------
NDMAIN CAT_AREA_MEDICARE 2004-08-08 00:57:26 448
NDMAIN CAT_AREA_PRICE 2004-08-08 00:57:26 449
NDMAIN CAT_AUTH_MEDICARE 2004-08-08 00:57:26 450
NDMAIN CAT_AUTH_MEDICARE_RELATE_PRODU 2004-08-08 00:57:26 451
NDMAIN CAT_AUTH_PRICE 2004-08-08 00:57:26 469
NDMAIN CAT_BUYER 2004-08-08 00:57:26 509
NDMAIN CAT_CATEGORY 2004-08-08 00:57:26 529
NDMAIN CAT_DEALER 2004-08-08 00:57:26 473
NDMAIN CAT_DOSEAGE_FORM 2004-08-08 00:57:26 454
NDMAIN CAT_DRUG 2004-08-08 00:57:26 455
NDMAIN CAT_ENTERPRISE 2004-08-08 00:57:26 456
NDMAIN CAT_ENTERPRISE_PLATCODE 2004-08-08 00:57:26 457
NDMAIN CAT_FILE_SALER 2004-08-08 00:57:26 474
NDMAIN CAT_INVITE_COMM 2004-08-08 00:57:26 489
NDMAIN CAT_MANUFACTURE 2004-08-08 00:57:26 471
NDMAIN CAT_METRIC 2004-08-08 00:57:26 458
NDMAIN CAT_PRODUCT 2004-08-08 00:57:26 534
NDMAIN CAT_PRODUCT_CENTER 2004-08-08 00:57:26 475
NDMAIN CAT_QUALITY_DEFINE 2004-08-08 00:57:26 461
NDMAIN CAT_REGION 2004-08-08 00:57:26 462
NDMAIN CAT_WRAP 2004-08-08 00:57:26 472
NDMAIN MBR_FOOTING_RECORD 2004-08-08 00:57:26 463
NDMAIN MBR_GRADE 2004-08-08 00:57:26 464
NDMAIN MBR_MEMBER 2004-08-08 00:57:26 465
NDMAIN MBR_MEMBER_ITEM 2004-08-08 00:57:26 466
NDMAIN PLT_ORG_PLAT 2004-08-08 00:57:26 467
NDMAIN PLT_PLAT 2004-08-08 00:57:26 468
27 rows selected.
这些物化视图的查询上次刷新时间是04年8月，而且也正是这些物化视图的物化视图日志数据量异常。
继续查询，又发现问题：
SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS WHERE MASTER = 'CAT_PRODUCT';
OWNER MASTER MVIEW_LAST_REFRESH_ MVIEW_ID
------- ------------------------------ ------------------- ----------
NDMAIN CAT_PRODUCT 2004-08-08 00:57:26 534
NDMAIN CAT_PRODUCT 2005-02-28 01:05:59 560
NDMAIN CAT_PRODUCT 2005-02-28 13:34:42 676
NDMAIN CAT_PRODUCT 2005-02-28 14:08:46 530
居然有四个物化视图定义在物化视图组上，看来是其中一个一直没有刷新，难怪造成了这个问题。
SQL> SELECT OWNER, NAME, MVIEW_SITE, MVIEW_ID FROM DBA_REGISTERED_MVIEWS
2 WHERE NAME = 'CAT_PRODUCT';
OWNER NAME MVIEW_SITE MVIEW_ID
------- --------------- ------------------------- ----------
NDMAIN CAT_PRODUCT REPDB01.US.ORACLE.COM 560
NDMAIN CAT_PRODUCT BJDB01.US.ORACLE.COM 530
NDMAIN CAT_PRODUCT ZJTRADE.US.ORACLE.COM 676
可是查询已注册的物化视图却只发现三个，看来原因就出在多出的那个物化视图身上。
其中一个物化视图站点在去年8月的时候进行过升级，整个数据库EXP，导入到一个新的服务器上，并重建了物化视图站点。看来现在的问题是由于上次没有将物化视图环境清除干净。
物化视图以前已经进行了UNREGISTERED_MVIEW_GROUP的操作，只需要执行PURGE_MVIEW_FROM_LOG过程就行了。
PURGE_MVIEW_FROM_LOG过程进行了重载，可以输入MVIEW_ID或输入MVIEWOWNER、MVIEWNAME、MVIEWSITE。由于MVIEWSITE现在已经被重复使用，因此只能通过MVIEW_ID的方式。
SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS WHERE MASTER = 'CAT_PRODUCT';
OWNER MASTER MVIEW_LAST_REFRESH_ MVIEW_ID
------- ------------------------------ ------------------- ----------
NDMAIN CAT_PRODUCT 2004-08-08 00:57:26 534
NDMAIN CAT_PRODUCT 2005-02-28 01:05:59 560
NDMAIN CAT_PRODUCT 2005-02-28 14:08:46 530
NDMAIN CAT_PRODUCT 2005-02-28 14:34:39 676
SQL> SELECT COUNT(*) FROM NDMAIN.MLOG$_CAT_PRODUCT;
COUNT(*)
----------
507347
SQL> EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(534)
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM DBA_BASE_TABLE_MVIEWS WHERE MASTER = 'CAT_PRODUCT';
OWNER MASTER MVIEW_LAST_REFRESH_ MVIEW_ID
------- ------------------------------ ------------------- ----------
NDMAIN CAT_PRODUCT 2005-02-28 01:05:59 560
NDMAIN CAT_PRODUCT 2005-02-28 14:08:46 530
NDMAIN CAT_PRODUCT 2005-02-28 14:34:39 676
SQL> SELECT COUNT(*) FROM NDMAIN.MLOG$_CAT_PRODUCT;
COUNT(*)
----------
236
通过执行PURGE_MVIEW_FROM_LOG过程，DBA_BASE_TABLE_MVIEWS视图中的结果和MLOG中的数量都恢复正常。
SQL> begin
2 for i in (select mview_id from dba_base_table_mviews where mview_last_refresh_time < trunc(sysdate)) loop
3 dbms_mview.purge_mview_from_log(i.mview_id);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
http://yangtingkun.itpub.net/post/468/19939
