Unregister Mview Snapshot Cleanly

Useful SQL to dynamically create unregister mview script for all mviews for a certain site or schema or both!

--Dynamic SQL - Unregister Snapshot info for a particular schema and database
select OWNER, NAME, SNAPSHOT_SITE, 
'execute dbms_snapshot.unregister_snapshot(''' || OWNER || ''',''' || NAME || ''',''' || SNAPSHOT_SITE || ''');' 
from dba_registered_snapshots rs
WHERE rs.OWNER = 'MY_SCHEMA'
and SNAPSHOT_SITE = 'MY_SNAPSHOT_SITE';

This links to my previous posts on this topic:

Find Site holding up mlog & Register Orphaned Mviews

Investigation SQL for Mview and Mlog growth

Also see metalink note “How to Manually Register/Unregister Snapshots” [ID 67371.1]

Investigation SQL for Mview and Mlog growth

--Find oldest snaptime for a mlog - i.e. time the mlog has been building up since 
select * from sys.slog$
where mowner = 'SCHEMA'
and Master = 'TABLE'
ORDER BY SNAPTIME DESC;
--row growth every time the mlog updates
select count(*)
from ATD_UK_FP.MLOG$_FP_INET_OPTIONS
group by SNAPTIME$$;
-- SNapshot sites for a particular mview
select owner, name ,snapshot_site, snapshot_id, refresh_method 
from dba_registered_snapshots rs
WHERE rs.OWNER = 'SCHEMA'
and rs.Name = 'TABLE'
order by rs.owner, rs.name;
--Find which snapshot_site is holding up the mlog
select sl.*, rs.snapshot_site, rs.owner, rs.name, rs.refresh_method 
from sys.slog$ sl
left join dba_registered_snapshots rs on rs.SNAPSHOT_ID = sl.SNAPID
where mowner = 'SCHEMA'
and Master = 'TABLE'
ORDER BY SNAPTIME DESC;
--Find unregistered snaps and snaps older than certain number of days 
--not being refreshed
select mowner, master, snapid,  nvl(r.snapsite, 'not registered') snapsite,snaptime
from   sys.slog$ s, sys.reg_snap$ r where  s.snapid=r.snapshot_id(+) and
s.snaptime < (sysdate-0)
and mowner = 'SCHEMA';

Useful Oracle Metalink Docs

236233.1 – Materialized View Refresh : Log Population and Purge

727632.1 – What to do when a Materialized View Log is not cleared automatically after a Fast Refresh

258634.1 – Materialized View registration at Master Site