Script to Refresh Materialized Views

The following script can be used to refresh materialized views that are capable of FAST (incremental) refresh automatically. Can be used on EBS database as well if you un-comment the commented (REM) lines. The refresh criteria used is any fast refresh-able materialized view that has not been refreshed in the past 24 hours, but was refreshed in the last one month...

REM Uncomment below line if EBS database

REM alter session set current_schema=apps;

set serveroutput on size 9999

Declare

cursor mv2ref is select owner ||'.'||  mview_name mview

       from dba_mviews

       where last_refresh_date between sysdate -30 and sysdate -1

       and fast_refreshable = 'DML';

dummy pls_integer;

begin

REM Uncomment below line if EBS database

REM select MRP_AP_REFRESH_S.NEXTVAL into dummy from dual;

for rmv2ref in mv2ref loop

begin

    dbms_mview.refresh(rmv2ref.mview, 'F');

    dbms_output.put_line(rmv2ref.mview ||' Refreshed at '|| to_char(systimestamp));

exception

when others then

    dbms_output.put_line(rmv2ref.mview ||' Errored at '|| to_char(systimestamp));

    dbms_output.put_line('    Error: '|| sqlcode ||':'||sqlerrm);

end;

end loop;

end;

/

Enjoy!