其中一个mview失败，一个命令来剔除失败mview的所需的log

xjwyq28@hotmail.com 原创
场景:
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');

xjwyq28@hotmail.com 原创

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8494287/viewspace-885822/，如需转载，请注明出处，否则将追究法律责任。