Blog Archives

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

Posted in Materialized views

Move MV from one refresh group into another

select 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;

Posted in Materialized views

Materialized Views with dbms_metadata.get_ddl

See 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

Posted in Materialized views