Preventing Materialized View Logs From Becoming Too Large

Materialized view logs keep track of changes to master tables at master sites. When a fast refresh is performed on a materialized view, the changes in the materialized view log are applied to the materialized view. After changes are applied to all of the materialized views that use the materialized view log, the changes are deleted from the materialized view log.

A materialized view log can become too large when one or more materialized views that use the materialized view log do not refresh for a relatively long period of time. This situation can slow refresh performance for other materialized views. The following are typical causes and solutions:

• A network problem is preventing one or more materialized views that use the materialized view log from refreshing. In this case, correct the network problem and refresh the relevant materialized views. See Configuring the Network Environment for information about configuring network connectivity between databases.

• A materialized view was dropped at a materialized view site when the materialized view site was not connected to the network. When a materialized view is dropped and a network connection is available, information about the materialized view is removed from the master site automatically. However, when a network connection is not available, information about the materialized view remains at the master site and must be cleaned up manually. See "Tutorial: Cleaning Up Materialized View Support at a Master Site".

• Materialized views are not being refreshed often enough. Materialized views can be refreshed manually, or they can be refreshed automatically when they are in a refresh group.

  If you refresh materialized views manually, then you might need to refresh them more often if materialized view logs are becoming too large. See "Refreshing a Materialized View".

  If materialized views are in a refresh group that is not refreshing often enough, then you can make the refresh interval shorter for the refresh group.

To modify the refresh interval for a refresh group: 

1. In Enterprise Manager, log in to the materialized view site as the materialized view administrator. The default user name for the materialized view administrator is mvadmin.

2. Go to the Database Home page.

3. Click Data Movement to open the Data Movement subpage.

4. Click Manage in the Advanced Replication section.

   The Advanced Replication: Administration page appears, showing the Overview subpage.

5. Click the number associated with Refresh Groups in the Materialized View Site section.

6. On the Refresh Groups page, use the search tool to list the refresh group that you want to modify.

7. Select the refresh group.

8. Click Edit.

   The Edit Refresh Group page appears, showing the General subpage.

9. In the Refresh section, click Change for the Interval field.

10. Shorten the interval using the Every field and list.

11. Click OK.

12. On the Edit Refresh Group page, click Apply to save your changes.