Materialized views

From shutdownabort.com

Create a view log for the master table This is required for fast refresh

create materialized view log on <table>
/

or...

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

List all materialized view logs

select 	log_owner
,	log_table
from	dba_mview_logs
/

Create a simple materialized view

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.

Show all materialized and resfresh times

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
/

Show materialized view tables and masters

set lines 100
col mview format a40
col master format a40
select	owner || '.' || name mview
,	master_owner || '.' || master master
from	dba_mview_refresh_times
/

Show refresh jobs in dba_jobs

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%'
/

Manually start a refresh

execute dbms_mview.refresh ('<owner.mv_table>');

Force a complete refresh

execute dbms_mview.refresh ('<owner.mv_table>','C');

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

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 synonym snap_ltyprd.account for loyalty.account;

• On QA

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;