物化檢視日誌無法正常清除的解決方法

今天在檢查時，發現某個物化檢視日誌佔用的空間超過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;