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