We found huge MV log (~14GB) on table which has 1G. The reason was that few months ago, after some maintenance we had to recreate materialized view (REFRESH FAST).
After recreating MV, log had two registered snapshots and leave data for first MV which was dropped. MV has been created to replicate data beatween two databases and inMV definition we using database links. After dropping MV, information about registred MV had not been refreshed.
To purge log table from database which waiting for not existed MV I used dbms_mview.purge_mview_from_log procedure, but in first step I have to check which snapshot not exist (thanks Remek: http://remigium.blogspot.com/2013/08/the-orphaned-mview-registration-on.html)
SELECT s.mowner,
s.master,
s.snaptime,
'exec dbms_mview.purge_mview_from_log('
||s.snapid
||');' fix1
FROM sys.slog$ s
WHERE NOT EXISTS
(SELECT 1 FROM DBA_REGISTERED_SNAPSHOTS R WHERE S.SNAPID=R.SNAPSHOT_ID
)
ORDER BY MOWNER,
master;
s.master,
s.snaptime,
'exec dbms_mview.purge_mview_from_log('
||s.snapid
||');' fix1
FROM sys.slog$ s
WHERE NOT EXISTS
(SELECT 1 FROM DBA_REGISTERED_SNAPSHOTS R WHERE S.SNAPID=R.SNAPSHOT_ID
)
ORDER BY MOWNER,
master;
and run purge_mview_from_log for not exited snapid.
After that all data for not existed MV will be remove, but only for this snap id, so we don't need run complete refresh for exited MV.
MV log table is a normall segment and it's no problem with shrinking segment by alter table MLOG$_EX_TABLE shrink space;
On our db after purging and shrinking, log table segment has 80MB.
