Managing Materialized View Log Space

Oracle automatically tracks which rows in a materialized view log have been used during the refreshes of materialized views, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple materialized views can use the same materialized view log, rows already used to refresh one materialized view may still be needed to refresh another materialized view. Oracle does not delete rows from the log until all materialized views have used them.

For example, suppose two materialized views were created against the CUSTOMERS table in a master site. Oracle refreshes the CUSTOMERS materialized view at the SPDB1 database. However, the server that manages the master table and associated materialized view log does not purge the materialized view log rows used during the refresh of this materialized view until the CUSTOMERS materialized view at the SPDB2 database also refreshes using these rows.

Because Oracle must wait for all dependent materialized views to refresh before purging rows from a materialized view log, unwanted situations can occur that cause a materialized view log to grow indefinitely when multiple materialized views are based on the same master table. For example, such situations can occur when more than one materialized view is based on a master table and one of the following conditions is true:

• One materialized view is not configured for automatic refreshes and has not been manually refreshed for a long time.

• One materialized view has an infrequent refresh interval, such as every year (365 days).

• A network failure has prevented an automatic refresh of one or more of the materialized views based on the master table.

• A network or site failure has prevented a master from becoming aware that a materialized view has been dropped.

Because of situations such as these, you may need to manually purge rows from your materialized view log using the DBMS_SNAPSHOT.PURGE_LOG procedure, or you may need to TRUNCATE your materialized view log.

If you purge or TRUNCATE the materialized view log before a materialized view has refreshed the changes that were deleted, the materialized view must perform a complete refresh.