Getting Info about Materialized View Logs
Submitted by dave on Mon, 08/22/2011 - 11:52
SELECT * FROM DBA_REGISTERED_MVIEWS;
SELECT AMVS.OWNER,
AMVS.MASTER,
AMVS.MVIEW_LAST_REFRESH_TIME,
RMVS.OWNER,
RMVS.NAME,
RMVS.MVIEW_SITE
FROM ALL_BASE_TABLE_MVIEWS AMVS,
ALL_REGISTERED_MVIEWS RMVS
WHERE RMVS.MVIEW_ID=AMVS.MVIEW_ID;
SELECT MOWNER,
MASTER,
SNAPSHOT,
SNAPID,
SNAPTIME,
SSCN,
USER#
FROM SYS.SLOG$
WHERE MASTER='&TABLE_NAME';
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');