Materialized views log are not getting purged after the completion of related fast re

[srikanth raju]

Hi ,

Currently we are having 12 different MV's that corresponds to 5 different fact tables.
out of these five tables , we have fast refresh of MV's defined for them.

One of our logs for the fact table(fact_order_backlog) is not getting cleared after the completion of the fast refresh related to the fact table.
but the remaining fact tables logs are getting cleared.

we have created logs for dimension tables , even the logs for dimension tables rae not getting cleared.

can you please let us know what could be the problem.

thanks
Srikanth

[Vinod Sadanandan]

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

But if you are really interested in purging the snapshot log ,there is a mannual mehod as well.

BEGIN
DBMS_SNAPSHOT.PURGE_LOG (
master => 'fact_order_backlog',
num => 1,
flag => 'DELETE');
END;
/

The procedure DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG is called on the master site to delete the rows in snapshot refresh related data dictionary tables maintained at the master site for the specified snapshot identified by its snapshot_id or the combination of the snapowner, snapname, and the snapsite.

Thanks & Regards,
Vinod Sadanandan
Oracle DBA