dbawiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
  *  [[https://oracle-base.com/articles/misc/materialized-views|oracle-base.com]]
  *  [[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>
create materialized view log on <table>
/

or...

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

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

==== Create a simple materialized view ====
<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>

Fast - uses the changes in the view log on the source side.
<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>
execute dbms_mview.refresh ('<owner.mv_table>','C');
</code>

Force - tries a fast. If this does not work, a complete will be done.
<code>
execute dbms_mview.refresh ('<owner.mv_table>','?');
</code>

==== 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
<code>
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;
</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>
exec dbms_stats.gather_table_stats(snap_ltyprd, 'account');
</code>