||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
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
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
||Need an Oracle Health Check?
Does your boss blame you for an Oracle performance problem?
Need to prove that your database is properly optimized?
BC Oracle performance guru's can quickly verify every aspect of your
Oracle database and provide a complete certification that your database
is fully optimized.