Category Archives: Materialized views

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 … Continue reading →

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;

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 … Continue reading →