其中一個mview失敗，一個命令來剔除失敗mview的所需的log

原創
場景:
1. 分散式資料庫透過mview同步, 一個主表被多個mview來刷取資料。
2. 當其中一個含 mview的資料庫失敗，其上的那個mview停止重新整理。
2. 壞了一個 mview , 主表的mlog$xxxx表的資料因為有一個沒刷, 而不刪除，主表的mlog$xxxx表不斷變大。
3. mlog$xxxx 不斷變大 , 影響其它正常的mview的重新整理非常速度, 且iowait嚴重。

解決:

1. 在建主表庫上使用DBMS_MVIEW.PURGE_LOG() 可以 刪除mlog$中log (最近最少重新整理的mview所需log), 那個失敗的mview一般情況下就是

最近最少重新整理(least recently refreshed) 的(根據實際情況定) 。

2. DBMS_MVIEW.PURGE_LOG() 後, mlog$xxxx表 的 仍熱很大, 因為HWM(高水位線) 過高, 可用alter table xxxx move來降低。

回顧： mview 重新整理的原理: 不再陳述。

使用方法如下:
=====================================PURGE_LOG Procedure=================================
This procedure purges rows from the materialized view log.

Syntax
DBMS_MVIEW.PURGE_LOG (
master IN VARCHAR2,
num IN BINARY_INTEGER := 1,
flag IN VARCHAR2 := 'NOP');

Parameters
Table 54-8 PURGE_LOG Procedure Parameters
Parameter Description
master
Name of the master table or master materialized view.

num
Number of least recently refreshed materialized views whose rows you want to remove from materialized view log. For

example, the following statement deletes rows needed to refresh the two least recently refreshed materialized views:

DBMS_MVIEW.PURGE_LOG('master_table', 2);

To delete all rows in the materialized view log, indicate a high number of materialized views to disregard, as in this

example:

DBMS_MVIEW.PURGE_LOG('master_table',9999);

This statement completely purges the materialized view log that corresponds to master_table if fewer than 9999 materialized

views are based on master_table. A simple materialized view whose rows have been purged from the materialized view log must

be completely refreshed the next time it is refreshed.

flag
Specify delete to guarantee that rows are deleted from the materialized view log for at least one materialized view. This

parameter can override the setting for the parameter num. For example, the following statement deletes rows from the

materialized view log that has dependency rows in the least recently refreshed materialized view:

DBMS_MVIEW.PURGE_LOG('master_table',1,'delete');

原創

[@more@]