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® Database Advanced Replication Management API Reference

11g Release 1 (11.1)

Part Number B28327-03

"Truncating a Materialized View Log"

If the registered MViews that need to stay FAST REFRESHABLE are all located on a single instance, we can simplify the process (as we do not need to create a cleaned up copy of the MV LOG):

1.Take note of the row count in the MV LOG:

SELECT COUNT(1) FROM .MLOG$_

;

We know that at this point, no MV LOG rows can be deleted (because we have outdated MViews preventing purging of the MV log). Additions to the MV LOG are still possible (by performing DML against the master table)

2. Perform a REFRESH FAST of all MViews that are still operational and need to be kept up-to-date. While this may be slow (due to the size of the MV Log) it will prevent an even more expensive operation of full refreshes for these MViews.

3. Execute:

LOCK TABLE < table> IN EXCLUSIVE MODE;

This prevents any further updates to the master table and hence the MV log. This lock is only required for a short period of time.

4. Confirm that the row count has not changed for the MV Log:

SELECT COUNT(1) FROM .MLOG$_

;

If there is no change, continue with the next step, otherrwise unlock the master table and restart the process again from the beginning. There may still be automatic (scheduled) jobs that had caused DML against the master table while the refresh operations were going on.

At this point, we prevent updates to the master table (via the exclusive lock) and we know the usable MViews are up-to-date.

5. Using a different session, execute:

TRUNCATE TABLE .MLOG$_

;

This will clear the MV log of all remaining change vectors. Any MView that is not up-to-date will no longer be fast refreshable after this step.

6. In the original session, execute:

ROLLBACK

to release the lock on the master table. DML against the master table is again possible.

Repeat the steps above for other MV LOGs that are very large.

Note that the time that the LOCKs are held is comparably small as it is only needed to execute the TRUNCATE command. This effectively prevents that any changes occur while truncating the MView log (which would cause a race condition).Yet, any DML against the master table will fail during the time the lock is held.

7. We now have a small MV LOG and we can proceed to DROP unused / stale MViews (also in remote instances):

DROP MVIEW ;

8. If you located MViews using Note 236292.1 that are registered but no longer accessible, you need to un-register them manually as outlined below. Otherwise, the MV logs will start growing again:

How to Manually Register/Unregister Snapshots/Materialized Views (Doc ID 67371.1)

This completes the process

Source: Oracle