物化視圖重新整理失敗導緻日志表異常增大

整理自：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

--傳回源庫查詢物化視圖對應日志條目個數，發現日志并沒有被清除

--在源庫查詢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');

--删除的MVIEW_ID應該是不需要的MVIEW對應的ID

SQL> EXEC DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(54);

/*

--注意：千萬不能把MVIEW_ID=86的MVIEW LOG删除了;如果删除的是MVIEW_ID=86的物化視圖注冊資訊的話，在物化視圖端重新整理會報錯，此時隻能重建物化視圖

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

--此時在可重新整理端重新整理物化視圖

--此時源庫上ITEM_TAG對應的物化視圖日志被清除

SQL> SELECT COUNT(1) FROM MLOG$_ITEM_TAG;

/* 

如果廢棄的物化視圖端的資料庫仍然可用，且有相關的資料庫連結，則更簡單的辦法是在廢棄物化視圖的資料庫中把物化視圖删除，此時如果資料庫連結可用，oracle會把源資料庫端的物化視圖注冊資訊一并删除，如：

--首先在源資料庫中查詢名稱為BRAND的物化視圖注冊資訊

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

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';

FIREDRAKE BRAND 2006-07-17 上午 08:31:2 1

--然後在物化視圖端執行：

SQL> DROP MATERIALIZED VIEW BRAND;

Materialized view dropped

--此時源資料庫端BRAND對應的物化視圖注冊資訊已經被删除了

當然，這種做法要符合三個前提條件

1、廢棄的物化視圖端資料庫仍然可用

2、網絡正常，在物化視圖端能用dblink能通路源資料庫

3、在業務上物化視圖可以被删除

最後，在資料庫空閑的時候對物化視圖日志表執行move操作，降低HWM

alter table mlog$_item_tag move;