Materialized views - Revision history

Stuart at 16:55, 3 February 2017

2017-02-03T16:55:34Z

Stuart at 16:42, 3 February 2017

2017-02-03T16:42:52Z

Stuart: /* Rebuild a broken View */

2016-06-21T11:09:38Z

‎Rebuild a broken View

Stuart at 11:09, 21 June 2016

2016-06-21T11:09:13Z

Stuart: Created page with "From [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
2013-01-23T22:49:10Z

Created page with "From [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 <pr..."

New page
From [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

<pre>
create materialized view log on <table>
/

or...

create materialized view log on <table>
tablespace <tablespace_name>
/
</pre>

List all materialized view logs
<pre>
select log_owner
, log_table
from dba_mview_logs
/
</pre>

Create a simple materialized view
<pre>
create materialized view andy_mview
refresh [fast | complete | force]
start with sysdate
next sysdate + 1/24
with primary key
as select * from test_table
/

Fast = update changes only
Complete = wipe and repopulate the mview
Force = fast if possible, complete if not.
</pre>

Show all materialized and resfresh times
<pre>
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
/
</pre>

Show materialized view tables and masters
<pre>
set lines 100
col mview format a40
col master format a40
select owner || '.' || name mview
, master_owner || '.' || master master
from dba_mview_refresh_times
/
</pre>

Show refresh jobs in dba_jobs
<pre>
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%'
/
</pre>

Manually start a refresh
<pre>
execute dbms_mview.refresh ('<owner.mv_table>');
</pre>

Force a complete refresh
<pre>
execute dbms_mview.refresh ('<owner.mv_table>','C');
</pre>

@@ Line 103: / Line 103: @@
 * On QA
 <pre>
-sqlplus snap_ltyprd/snap
+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;
 </pre>

@@ Line 2: / Line 2: @@
 * [http://www.shutdownabort.com/dbaqueries/Structure_Materialized_view.php shutdownabort.com]
-Create a view log for the master table
+===Create a view log for the master table===
 This is required for fast refresh
@@ Line 16: / Line 16: @@
 </pre>
-List all materialized view logs
+====List all materialized view logs====
 <pre>
 select 	log_owner
@@ Line 24: / Line 24: @@
 </pre>
-Create a simple materialized view
+===Create a simple materialized view===
 <pre>
 create materialized view emp_mv
@@ Line 39: / Line 39: @@
 Force = fast if possible, complete if not.<br />
 <br />
-Show all materialized views and their refresh times
+====Show all materialized views and their refresh times====
 <pre>
 set lines 100 pages 999
@@ Line 52: / Line 52: @@
 </pre>
-Show materialized view tables and masters
+====Show materialized view tables and masters====
 <pre>
 set lines 100
@@ Line 63: / Line 63: @@
 </pre>
-Show refresh jobs in dba_jobs
+===Show refresh jobs in dba_jobs===
 <pre>
 This is useful for spotting failures
@@ Line 85: / Line 85: @@
 </pre>
-Manually start a refresh
+===Force a refresh===
 <pre>
-execute dbms_mview.refresh ('<owner.mv_table>');
+execute dbms_mview.refresh ('<owner.mv_table>','F');
 </pre>
-Force a complete refresh
+Complete - truncates or deletes the data (depending on other options) and rebuilds the view from scratch (remember to gather view stats afterwards).
 <pre>
 execute dbms_mview.refresh ('<owner.mv_table>','C');
 </pre>

@@ Line 1: / Line 1: @@
-From [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
@@ Line 25: / Line 26: @@
 Create a simple materialized view
 <pre>
-create materialized view andy_mview
+create materialized view emp_mv
 refresh [fast | complete | force]
 start with sysdate
 next sysdate + 1/24
 with primary key
-as select * from test_table
+as select * from emp@dblink
+/
 </pre>
+where<br />
-Show all materialized and resfresh times
+Fast = update changes only<br />
 <pre>
 set lines 100 pages 999
 col last_refresh format a20
-select	owner
+select owner
-,	mview_name
+,      mview_name
-,	to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
+,      to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
-from	dba_mviews
+from   dba_mviews
-order by owner, last_refresh
+order  by owner
+/
 </pre>
@@ Line 55: / Line 57: @@
 col mview format a40
 col master format a40
-select	owner || '.' || name mview
+select owner || '.' || name mview
-,	master_owner || '.' || master master
+,      master_owner || '.' || master master
-from	dba_mview_refresh_times
+from   dba_mview_refresh_times
+/
 </pre>
@@ Line 72: / Line 74: @@
 col fail format 9999
 col what format a20
-select	job
+select job
-,	log_user
+,      log_user
-,	to_char(last_date, 'dd/mm/yy hh24:mi') last
+,      to_char(last_date, 'dd/mm/yy hh24:mi') last
-,	to_char(next_date, 'dd/mm/yy hh24:mi') next
+,      to_char(next_date, 'dd/mm/yy hh24:mi') next
-,	failures fail
+,      failures fail
-,	replace(what, '"') what
+,      replace(what, '"') what
-from	dba_jobs
+from   dba_jobs
-where	what like '%dbms_refresh.refresh%'
+where  what like '%dbms_refresh.refresh%'
+/
 </pre>
@@ Line 98: / Line 100: @@
 <pre>
 drop materialized view log on loyalty.account
-create materialized view log on loyalty.account tablespace loyalty_dl_01 nocache with primary key,sequence including new values;
+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;
 </pre>
@@ Line 107: / Line 109: @@
 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;
 </pre>