garden1.jpg

Recreate Matenrialized View


idbasolutions.com Apr 2008

Sometime a MV (Materialized View) becomes UNUSABLE, STALE or INVALID and even using
alter materialized view compile does not fix the problem. The only way is to re-create it.
The following stored procedure will recreate a given MV for a given owner.

Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.

CREATE OR REPLACE procedure recreate_mv(a_owner in dba_mviews.owner%type,
p_mv in dba_mviews.mview_name%type)
AUTHID CURRENT_USER
is
cursor cur_mv is
select owner, mview_name, query
from dba_mviews
where owner=a_owner
and mview_name = p_mv;
typ_mv cur_mv%rowtype;
v_sql varchar2(1000);
begin
open cur_mv;
fetch cur_mv into typ_mv;
if cur_mv%found then
v_sql := ‘DROP MATERIALIZED VIEW ‘ || typ_mv.owner || ‘.’ || typ_mv.mview_name;
dbms_output.put_line(v_sql);
execute immediate (v_sql);
v_sql := ‘CREATE MATERIALIZED VIEW ‘ || typ_mv.owner || ‘.’ ||
typ_mv.mview_name || ‘ Enable query rewrite as ‘ || typ_mv.query;
dbms_output.put_line(v_sql);
execute immediate (v_sql);
end if;
close cur_mv;
exception
when others then
dbms_output.put_line(‘Error number : ‘ || sqlcode || ‘ Description ‘ || sqlerrm);
if cur_mv%ISOPEN then
close cur_mv;
end if;
return;
end recreate_mv;
/

Use this procedure in conjuction with the following dynamic SQL to
recreate all invalid query enable MV’s for all schemas:

set heading off
set feedback off
set echo off
set pause off
spool /u05/oltp/export/d_recreate_unusable_mv_sql.sql
select ‘exec sys.recreate_mv(‘|| ””||owner||””||’,'||”” ||MVIEW_NAME ||””||’)'||’;’
from dba_mview_analysis
where UNUSABLE=’Y’
and REWRITE_ENABLED=’Y’
and owner not in
(‘SYS’,'SYSTEM’,'PERFSTAT’,'SPACE_M’,'WIRELESS’,'CTXSYS’,
‘MOWEN’,'WKSYS’,'HR8′,’SCOTT’,'MDSYS’,
‘OE8′,’IORG’,'WMSYS’,'ORACACHE’,'OUTLN’,'ORDSYS’,'OCITEST’)
/
select ‘exec sys.recreate_mv(‘|| ””||owner||””||’,'||”” ||MVIEW_NAME ||””||’)'||’;’
from dba_mviews
where STALENESS=’UNUSABLE’
and REWRITE_ENABLED=’Y’
/
spool off

Use this procedure in conjuction with the following dynamic SQL to
recreate all query enable MV’s with a STALENESS=’IMPORT’ for all schemas.
This is most usefull after a complete refresh of a schema from an import dump
where after the import completes all MV have a STALENESS=’IMPORT’ and will not be
used my the optimizer:

Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.

Set heading off
set feedback off
set echo off
set pause off
spool d_recreate_unusable_mv_sql.sql
select ‘exec sys.recreate_mv(‘|| ””||owner||””||’,'||”” ||MVIEW_NAME ||””||’)'||’;’
from dba_mviews
where STALENESS=’IMPORT’
and REWRITE_ENABLED=’Y’
and owner not in
(‘SYS’,'SYSTEM’,'PERFSTAT’,'SPACE_M’,'WIRELESS’,'CTXSYS’,
‘MOWEN’,'WKSYS’,'HR8′,’SCOTT’,'MDSYS’,
‘OE8′,’IORG’,'WMSYS’,'ORACACHE’,'OUTLN’,'ORDSYS’,'OCITEST’)

Latest Blog Items

email

Thank you for your interest in idbasolutions.

Contact us using this email: contact.us@idbasolutions.com