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');