1. ROWIDs do not persist through a reorganization of master tables, forcing all MViews to perform a complete refresh after a table reorganization
2. Fast refreshable materialized view logs get registered in SYS.SLOG$ at the master side.
3. Oracle does not delete rows from the log until all MViews have used them.
4. Purging Policy
Rows purged : MLOG$_<table_name>.SNAPTIME$$ [ i.e old entries ] <= MIN (SLOG$.SNAPTIME) [ i.e Last refresh ]
Rows Never purged : MLOG$_<table_name>.SNAPTIME$$ [ i.e New entries ] > MIN (SLOG$.SNAPTIME) [ i.e Last refresh ]
DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG
SLOG$.SNAPTIME --> Refresh time
MLOG$_<table_name>.SNAPTIME$$ --> New entries (refresh timestamps of MLog)
Note : An orphan entry in SYS.SLOG$ at the master site for a deleted snapshot, the SNAPTIME in SLOG$ will not be updated.
The MView's last refresh time must be later than the oldest entry in the MView log
5. Scripts
Entry in SLOG$ not registered and not updated
SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, snaptime
FROM sys.slog$ s, dba_registered_snapshots r
WHERE s.snapid=r.snapshot_id(+)
AND mowner LIKE UPPER('&owner')
AND MASTER LIKE UPPER('&table_name');
Size of the MV Logs
SELECT log_owner, master, log_table FROM dba_mview_logs;
select segment_name, bytes/1024/1024 MB
from dba_segments
where (segment_name, owner) in ( select log_table,log_owner from dba_mview_logs);
select master ,log, least(oldest,oldest_pk) oldest, youngest, mtime from sys.mlog$
where mowner like upper('&owner_name')
History of Refresh even for MV not registered on Master side and MV registered for Complete refresh
col LOG_TABLE for a30
col SNAPSHOT_SITE for a10
col LOG_OWNER for a15
col OWNER for a15
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select l.snapshot_id,to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots,
substr(snapshot_site,1,30) snapshot_site,
owner, nvl(name,'---SnapID : Not Registered---') name ,
LOG_OWNER , nvl(LOG_TABLE ,'Registered : Complete Refresh ') LOG_TABLE, PRIMARY_KEY
from dba_registered_snapshots r full outer join dba_snapshot_logs l
on r.snapshot_id = l.snapshot_id
order by LOG_TABLE;
select l.snapshot_id, owner, name, substr(snapshot_site,1,30) snapshot_site,
to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots
from dba_registered_snapshots r, dba_snapshot_logs l
where r.snapshot_id = l.snapshot_id (+)
and l.master='&table_name';
2. Fast refreshable materialized view logs get registered in SYS.SLOG$ at the master side.
3. Oracle does not delete rows from the log until all MViews have used them.
4. Purging Policy
Rows purged : MLOG$_<table_name>.SNAPTIME$$ [ i.e old entries ] <= MIN (SLOG$.SNAPTIME) [ i.e Last refresh ]
Rows Never purged : MLOG$_<table_name>.SNAPTIME$$ [ i.e New entries ] > MIN (SLOG$.SNAPTIME) [ i.e Last refresh ]
DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG
SLOG$.SNAPTIME --> Refresh time
MLOG$_<table_name>.SNAPTIME$$ --> New entries (refresh timestamps of MLog)
Note : An orphan entry in SYS.SLOG$ at the master site for a deleted snapshot, the SNAPTIME in SLOG$ will not be updated.
The MView's last refresh time must be later than the oldest entry in the MView log
5. Scripts
Entry in SLOG$ not registered and not updated
SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, snaptime
FROM sys.slog$ s, dba_registered_snapshots r
WHERE s.snapid=r.snapshot_id(+)
AND mowner LIKE UPPER('&owner')
AND MASTER LIKE UPPER('&table_name');
Size of the MV Logs
SELECT log_owner, master, log_table FROM dba_mview_logs;
select segment_name, bytes/1024/1024 MB
from dba_segments
where (segment_name, owner) in ( select log_table,log_owner from dba_mview_logs);
select master ,log, least(oldest,oldest_pk) oldest, youngest, mtime from sys.mlog$
where mowner like upper('&owner_name')
History of Refresh even for MV not registered on Master side and MV registered for Complete refresh
col LOG_TABLE for a30
col SNAPSHOT_SITE for a10
col LOG_OWNER for a15
col OWNER for a15
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
select l.snapshot_id,to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots,
substr(snapshot_site,1,30) snapshot_site,
owner, nvl(name,'---SnapID : Not Registered---') name ,
LOG_OWNER , nvl(LOG_TABLE ,'Registered : Complete Refresh ') LOG_TABLE, PRIMARY_KEY
from dba_registered_snapshots r full outer join dba_snapshot_logs l
on r.snapshot_id = l.snapshot_id
order by LOG_TABLE;
select l.snapshot_id, owner, name, substr(snapshot_site,1,30) snapshot_site,
to_char(current_snapshots, 'mm/dd/yyyy hh24:mi:ss') current_snapshots
from dba_registered_snapshots r, dba_snapshot_logs l
where r.snapshot_id = l.snapshot_id (+)
and l.master='&table_name';
