物化视图刷新失败导致日志表异常增大

整理自：http://blog.itpub.net/231499/viewspace-63714/

今天在检查时，发现某个物化视图日志占用的空间超过150M，再检查看，该物化视图日志表的记录数有150W，由于其对应的物化视图没有会刷新一次，结合业务量分析可知：物化视图日志不能正常清除。

下面的解决步骤

--在源库查询物化视图对应日志条目个数
SQL> select count(1) from MLOG$_ITEM_TAG;

COUNT(1)
----------
532515

--在物化视图端刷新物化视图 
SQL> exec dbms_snapshot.refresh('item_tag');

PL/SQL procedure successfully completed

--返回源库查询物化视图对应日志条目个数，发现日志并没有被清除
SQL> select count(1) from MLOG$_ITEM_TAG;

COUNT(1)
----------
532515

--在源库查询ITEM_TAG对应的注册信息，发现有两个库的物化视图是基于ITEM_TAG建立的 
SQL> select * from USER_REGISTERED_MVIEWS where name='ITEM_TAG';

OWNER NAME MVIEW_SITE CAN_USE_LOG UPDATABLE REFRESH_METHOD MVIEW_ID VERSION QUERY_TXT
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------- --------- -------------- --------------------------------------- -------------------------- --------------------------------------------------------------------------------
TEST ITEM_TAG SC1.SOUCHANG.COM YES YES PRIMARY KEY 54 ORACLE 8 MATERIALIZED VIEW SELECT "ITEM_TAG"."ITEM_TAG_ID" "ITEM_TAG_ID","ITEM_TAG"."ITEM_TAG_SEQ_NUMBER" "
TEST ITEM_TAG SC2TEST.SOUCHANG.COM YES YES PRIMARY KEY 86 ORACLE 8 MATERIALIZED VIEW SELECT "ITEM_TAG"."ITEM_TAG_ID" "ITEM_TAG_ID","ITEM_TAG"."ITEM_TAG_SEQ_NUMBER" "

SQL> select * from DBA_BASE_TABLE_MVIEWS where master='ITEM_TAG';

OWNER MASTER MVIEW_LAST_REFRESH_TIME MVIEW_ID
------------------------------ ------------------------------ ----------------------- ----------
SOUCHANG2 ITEM_TAG 2006-06-22 上午 08:54:0 54
SOUCHANG2 ITEM_TAG 2006-07-17 上午 10:47:5 86

/*原因找出来了，是因为其中一个库的物化视图没有刷新，所以导致物化视图日志没有被删除（物化视图日志必须在所有基于该表的物化视图都刷新后才会被删除）
遇到这种情况可以有两种解决方法：删除无法刷新的物化视图 或 删除无法刷新的物化视图注册信息
在本案例中，由于无法刷新物化视图的库是一个老库，已经被移除了，所以只能通过在源库删除这些物化视图的注册信息
*/
SQL> exec DBMS_MVIEW.unregister_mview('TEST','ITEM_TAG','SC1.SOUCHANG.COM');

PL/SQL procedure successfully completed

--删除的MVIEW_ID应该是不需要的MVIEW对应的ID
SQL> EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(54);

PL/SQL procedure successfully completed

/*
--注意：千万不能把MVIEW_ID=86的MVIEW LOG删除了;如果删除的是MVIEW_ID=86的物化视图注册信息的话，在物化视图端刷新会报错，此时只能重建物化视图
SQL> exec dbms_snapshot.refresh('item_tag');

begin dbms_snapshot.refresh('item_tag'); end;

ORA-12034: materialized view log on "SOUCHANG2"."ITEM_TAG" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1
*/

--此时在可刷新端刷新物化视图
SQL> exec dbms_snapshot.refresh('item_tag');

PL/SQL procedure successfully completed

--此时源库上ITEM_TAG对应的物化视图日志被清除
SQL> SELECT COUNT(1) FROM MLOG$_ITEM_TAG;

COUNT(1)
----------
0

/* 
如果废弃的物化视图端的数据库仍然可用，且有相关的数据库链接，则更简单的办法是在废弃物化视图的数据库中把物化视图删除，此时如果数据库链接可用，oracle会把源数据库端的物化视图注册信息一并删除，如：
*/
--首先在源数据库中查询名称为BRAND的物化视图注册信息
SQL> select * from DBA_REGISTERED_MVIEWS where name='BRAND';

OWNER NAME MVIEW_SITE CAN_USE_LOG UPDATABLE REFRESH_METHOD MVIEW_ID VERSION QUERY_TXT
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------- --------- -------------- --------------------------------------- -------------------------- --------------------------------------------------------------------------------
FIREDRAKE BRAND NEI.SOUCHANG.COM YES YES PRIMARY KEY 1 ORACLE 8 MATERIALIZED VIEW SELECT "BRAND"."BRAND_ID" "BRAND_ID","BRAND"."ORGANIZATION_ID" "ORGANIZATION_ID"

SQL> select * from DBA_BASE_TABLE_MVIEWS where master='BRAND';

OWNER MASTER MVIEW_LAST_REFRESH_TIME MVIEW_ID
------------------------------ ------------------------------ ----------------------- ----------
FIREDRAKE BRAND 2006-07-17 上午 08:31:2 1

--然后在物化视图端执行：
SQL> DROP MATERIALIZED VIEW BRAND;

Materialized view dropped

--此时源数据库端BRAND对应的物化视图注册信息已经被删除了
SQL> select * from DBA_BASE_TABLE_MVIEWS where master='BRAND';

OWNER MASTER MVIEW_LAST_REFRESH_TIME MVIEW_ID
------------------------------ ------------------------------ ----------------------- ----------

SQL> select * from DBA_REGISTERED_MVIEWS where name='BRAND';

OWNER NAME MVIEW_SITE CAN_USE_LOG UPDATABLE REFRESH_METHOD MVIEW_ID VERSION QUERY_TXT
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------- --------- -------------- --------------------------------------- -------------------------- --------------------------------------------------------------------------------

/*
当然，这种做法要符合三个前提条件
1、废弃的物化视图端数据库仍然可用
2、网络正常，在物化视图端能用dblink能访问源数据库
3、在业务上物化视图可以被删除
*/

最后，在数据库空闲的时候对物化视图日志表执行move操作，降低HWM

alter table mlog$_item_tag move;