What to do When Materialized View Log is not Cleared Automatically After a Fast Refresh?

(Doc ID 727632.1)

Last updated on SEPTEMBER 04, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

Goal

The materialized view log does not get cleared after the fast refresh.

Why is this  happening?

For example the mview log of the TRES_RAWDATA_LOT_MAT_VW does not get cleared.

Check the output of:

select owner,name ,snapshot_site, snapshot_id, refresh_method from dba_registered_snapshots order by owner, name; 


And it can be seen that there are many mviews with the same name but with different mview sites.

OWNER NAME SNAPSHOT_SITE SNAPSHOT_ID REFRESH_METHOD
-------- ------------------------------ ------------- ----------- --------------
ODS TRES_RAWDATA_LOT_MAT_VW ODSDEVT 96 PRIMARY KEY
ODS TRES_RAWDATA_LOT_MAT_VW ODSPROD 708 PRIMARY KEY
ODS TRES_RAWDATA_LOT_MAT_VW ODSUAT 64 PRIMARY KEY

 The one which is useful and still being actively fast refreshed is the one with snapshot id 708.

 Note: If there is another mview defined on master table as COMPLETE REFRESH type and not refreshed in awhile, the MLOG$_ will not be purged until Complete Refresh runs on that mview. This behavior is expected. Both MVs are "subscribers" of the mv log even if one of the MVs does only complete refresh. In many cases, a "complete refresh" MV is also fast-refreshable. Such MVs can be altered to be REFRESH FAST at any moment. Keeping them as a subscriber of the MV log is necessary to avoid losing changes in the log for next fast refresh. Therefore, it is a good idea to ensure that all mviews are kept fresh.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms