Andrew Fraser DBA

List databases using materialized view log

See which remote databases are using a ‘materialized view log’ on source database with:

SELECT mview_site , COUNT(*)
  FROM dba_registered_mviews
 GROUP BY mview_site
 ORDER BY 1 ;

The snaptime$$ column shows how old the data is in any materialized view log:

SELECT TRUNC(snaptime$$) , COUNT(*)
  FROM owner.mlog$_mytable
 GROUP BY TRUNC(snaptime$$)
 ORDER BY 1 ;

Oracle’s Interactive Quick Reference helped identify the dba_ views for this.