MV LOG

Determine why that log table is growing

First, determine why the MV log is growing.

This is most likely linked to a registered MView that no longer exists or has not been refreshed for a long time (for example, MViews set to refresh manually or a large time intervals, as well as MViews that do no longer exist)

To find out which MViews are not refreshing, see the following article:

"Scripts to Report Information about Materialized View Logs at the Master Site" (Doc ID 236292.1)

Once the MV logs become very large, normal purging of the MV Logs as outlined in Note 1031924.6will not be efficient.

Dropping a stale MView will in turn cause expensive DELETE DML on the MV Log and may therefore not be desiraable.

Dropping and re-creating the MV LOG itself does not work since we still have FAST REFRESHABLE MViews defined. This prevents the MV Log to be dropped.

Considering that it is probably more expensive / disruptive to drop all the MViews in order to drop the MV LOGs, a better option is to TRUNCATE the MV LOG, provided that we do not miss any updates to the MV logs in the meantime.

The general steps to TRUNCATE the MV LOG are documented here:

Oracle