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 […]
Read More List databases using materialized view logselect name, refgroup,rname from dba_refresh_children where name in (‘MV1′,’MV2’); exec dbms_refresh.subtract(‘RGOWNER.RG1’, ‘MVOWNER.MV1’) exec dbms_refresh.add(‘RGOWNER.RG2’, ‘MVOWNER.MV1’) List refresh groups with: select rowner, rname, count(*) from dba_refresh_children group by rname, rowner order by 1,2;
Read More Move MV from one refresh group into anotherSee the SQL create definition of a materialized view by running this SQL: set long 200000 pages 0 lines 131 doc off column txt format a121 word_wrapped spool recreate_sql select dbms_metadata.get_ddl(‘MATERIALIZED_VIEW’,’my_mv_name’,’my_owner’) txt from dual; spool off set pages 9999 Look especially for the refresh option, e.g.: REFRESH FORCE ON DEMAND NEXT null There are three […]
Read More Materialized Views with dbms_metadata.get_ddl