Differences

This shows you the differences between two versions of the page.

--- materialized_views [2018/12/06 21:05] – created 91.177.234.129
+++ materialized_views [2020/05/26 12:42] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Materialized_views ======
+  *  [[https://oracle-base.com/articles/misc/materialized-views|oracle-base.com]]
+  *  [[http://www.shutdownabort.com/dbaqueries/Structure_Materialized_view.php|shutdownabort.com]]
-  * [[https://oracle-base.com/articles/misc/materialized-views|oracle-base.com]]
+==== Create a view log for the master table ====
-  * [[http://www.shutdownabort.com/dbaqueries/Structure_Materialized_view.php|shutdownabort.com]]
-=====Create a view log for the master table=====
 This is required for fast refresh
-<code>0@@</code>
+<code>
+create materialized view log on <table>
+/
-====List all materialized view logs====
+or...
-<code>1@@</code>
-=====Create a simple materialized view=====
+create materialized view log on <table>
-<code>2@@</code>
+tablespace <tablespace_name>
-where<br />
+/
-Fast = update changes only<br />
+</code>
-Complete = wipe and repopulate the mview<br />
-Force = fast if possible, complete if not.<br />
-<br />
-====Show all materialized views and their refresh times====
-<code>3@@</code>
-====Show materialized view tables and masters====
+=== List all materialized view logs ===
-<code>4@@</code>
+<code>
+select log_owner
+,      log_table
+from   dba_mview_logs
+/
+</code>
-=====Show refresh jobs in dba_jobs=====
+==== Create a simple materialized view ====
-<code>5@@</code>
+<code>
+create materialized view emp_mv
+refresh [[fast | complete | force]]
+start with sysdate
+next sysdate + 1/24
+with primary key
+as select * from emp@dblink
+/
+</code>
+where\\
+Fast = update changes only\\
+Complete = wipe and repopulate the mview\\
+Force = fast if possible, complete if not.\\
+=== Show all materialized views and their refresh times ===
+<code>
+set lines 100 pages 999
+col last_refresh format a20
+select owner
+,      mview_name
+,      to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
+from   dba_mviews
+order  by owner
+,      last_refresh
+/
+</code>
+=== Show materialized view tables and masters ===
+<code>
+set lines 100
+col mview format a40
+col master format a40
+select owner || '.' || name mview
+,      master_owner || '.' || master master
+from   dba_mview_refresh_times
+/
+</code>
+==== Show refresh jobs in dba_jobs ====
+<code>
+This is useful for spotting failures
+set lines 100
+col job format 9999
+col log_user format a15
+col last format a15
+col next format a15
+col fail format 9999
+col what format a20
+select job
+,      log_user
+,      to_char(last_date, 'dd/mm/yy hh24:mi') last
+,      to_char(next_date, 'dd/mm/yy hh24:mi') next
+,      failures fail
+,      replace(what, '"') what
+from   dba_jobs
+where  what like '%dbms_refresh.refresh%'
+/
+</code>
+==== Force a refresh ====
+All views
+<code>
+set serveroutput on
+declare
+    l_retval number := 0;
+begin
+    dbms_output.enable(null);
+    dbms_mview.refresh_all_mviews (l_retval,'C','', true, false);
+    dbms_output.put_line ('returned: '||l_retval);
+end;
+/
+</code>
-=====Force a refresh=====
 Fast - uses the changes in the view log on the source side.
-<code>6@@</code>
+<code>
+execute dbms_mview.refresh ('<owner.mv_table>','F');
+</code>
 Complete - truncates or deletes the data (depending on other options) and rebuilds the view from scratch (remember to gather view stats afterwards).
-<code>7@@</code>
+<code>
+execute dbms_mview.refresh ('<owner.mv_table>','C');
+</code>
 Force - tries a fast. If this does not work, a complete will be done.
-<code>8@@</code>
+<code>
+execute dbms_mview.refresh ('<owner.mv_table>','?');
+</code>
-=====Rebuild a broken View=====
+==== Rebuild a broken View ====
 Sometimes the materialised views stop working. If the original table was modified for example (maybe index was changed or a column removed), the view will fail.
-  * On PROD
+  *  On PROD
-<code>9@@</code>
+<code>
-  * On QA
+drop materialized view log on loyalty.account
-<code>10@@</code>
+create materialized view log on loyalty.account tablespace loyalty_dl_01 nocache with primary key, sequence including new values;
+create synonym snap_ltyprd.account for loyalty.account;
+</code>
+  *  On QA
+<code>
+sqlplus snap_ltyprd/****
+drop materialized view account;
+create materialized view snap_ltyprd.account tablespace snap_ts using index tablespace snap_ts refresh fast on demand enable query rewrite as select * from account@link_loyalty_ltyprd;
+select count('e') from account;
+</code>
 Remember to refresh the view stats!
-<code>11@@</code>
+<code>
+exec dbms_stats.gather_table_stats(snap_ltyprd, 'account');
+</code>