清理物化视图日志

物化视图日志表中的记录由于是用delete 清理 所以使用久了会占用空间，下文摘自官方文档

 

Oracle9i Replication Management API Reference
Release 2 (9.2)
Part Number A96568-01

 

Purging Rows from a Materialized View Log

Always try to keep a materialized view log as small as possible to minimize the database space that it uses. To remove rows from a materialized view log and make space for newer log records, you can perform one of the following actions:

• Refresh the materialized views associated with the log so that Oracle can purge rows from the materialized view log.
• Manually purge records in the log by deleting rows required only by the nth least recently refreshed materialized views.

To manually purge rows from a materialized view log, execute the PURGE_LOG procedure of the DBMS_MVIEW package at the database that contains the log. For example, to purge entries from the materialized view log of the customers table that are necessary only for the least recently refreshed materialized view, execute the following procedure:

BEGIN

   DBMS_MVIEW.PURGE_LOG (

      master => 'hr.employees',

      num    => 1,

      flag   => 'DELETE');

END;

/

Only the owner of a materialized view log or a user with the EXECUTE privilege for the DBMS_MVIEW package can purge rows from the materialized view log by executing the PURGE_LOG procedure.

 

Truncating a Materialized View Log

If a materialized view log grows and allocates many extents, then purging the log of rows does not reduce the amount of space allocated for the log. In such cases, you should truncate the materialized view log. Only the owner of a materialized view log or a user with the DELETE ANY TABLE system privilege can truncate a materialized view log.

To reduce the space allocated for a materialized view log by truncating it, complete the following steps:

 

Step 1 Acquire an exclusive lock on the master table or master materialized view to prevent updates from occurring during the following process. For example, issue a statement similar to the following:

LOCK TABLE hr.employees IN EXCLUSIVE MODE;

 

Step 2 Using a second database session, copy the rows in the materialized view log (in other words, the MLOG$ base table) to a temporary table. For example, issue a statement similar to the following:

CREATE TABLE hr.templog AS SELECT * FROM hr.MLOG$_employees;

 

Step 3 Using the second session, truncate the log using the SQL statement TRUNCATE. For example, issue a statement similar to the following:

TRUNCATE hr.MLOG$_employees;

 

Step 4 Using the second session, reinsert the old rows so that you do not have to perform a complete refresh of the dependent materialized views. For example, issue statements similar to the following:

INSERT INTO hr.MLOG$_employees SELECT * FROM hr.templog;

 

DROP TABLE hr.templog;

 

Step 5 Using the first session, release the exclusive lock on the master table or master materialized view by performing a rollback:

ROLLBACK;

 

---

Note:

Any changes made to the master table or master materialized view between the time you copy the rows to a new location and when you truncate the log do not appear until after you perform a complete refresh.

---

 

我的示例：

 select '数据量   ' || count(0)  from t3 union

select '需更新量 ' || count(0) from mlog$_t3;

 

LOCK TABLE t3 IN EXCLUSIVE MODE;

CREATE TABLE templog AS  SELECT * FROM mlog$_t3;

TRUNCATE table mlog$_t3;

INSERT INTO mlog$_t3  SELECT * FROM templog;

DROP TABLE templog;

ROLLBACK;