Oracle Replication Tips by Burleson

Monitoring the Oracle Materialized View Log

The Materialized View log can sometimes grow unconstrained. This usually occurs when a remote database with a refreshing Materialized View is removed without first dropping the Materialized View. Thus, the master site will continue to collect data in an effort to update the remote Materialized View.

If you find that your Materialized View logs are growing rapidly, you may need to purge rows from the Materialized View log. This is done via the dbms_Materialized View.purge_log procedure. Use this procedure carefully, or you may be forced to perform full refreshes of your remote Materialized Views.

You can keep track of the growth of the Materialized View log via the dba_extents view, with a little help from the dba_Materialized View_logs view. The following script can be used to keep track of the growth of Materialized View logs:

SELECT a.log_owner, a.log_table, sum(b.bytes), count(*)
FROM dba_Materialized View_logs a, dba_extents b
SEE CODE DEPOT FOR FULL SCRIPT
GROUP by a.log_owner, a.log_table;

And here is some sample output:

LOG_OWNER  LOG_TABLE              SUM(B.BYTES)  COUNT(*)
---------- ---------------------- ------------ ---------
PUBS       MLOG$_BOOK             65536        1

This is an excerpt from Oracle Replication By Rampant TechPress (only $19.95).  You can click here to order a copy and get instant access to the code depot:

http://www.rampant-books.com/book_2003_2_replication.htm