Change refresh schedules for an Oracle Materialized View ( MV )

Here is an automated script to change the refresh schedule for all MV's for a particular schema(in our case the
schema name is GGAMADM).
Please run this as DBA user (or change dba_mviews to user_mviews).

The basic syntax is

ALTER MATERIALIZED VIEW abc

     REFRESH COMPLETE

     START WITH trunc(sysdate+1) +(10/24)

     NEXT case when to_char( sysdate,'hh24' ) between '10' and '11' then trunc(sysdate)+(14/24) else trunc(sysdate+1)+(10/24) end

/

The above command refreshes the MV at 10am and 2pm.

declare
--str1 varchar2(100);
str2 varchar2(4000);
cursor c1
is select mview_name from dba_mviews where owner='GGAMDADM';
begin
for c2 in c1
loop

str2:= 'ALTER MATERIALIZED VIEW ggamdadm.'|| c2.mview_name||
     ' REFRESH COMPLETE
     START WITH trunc(sysdate+1) +(10/24)
     NEXT case when to_char( sysdate,''hh24'' ) between ''10'' and ''11'' then trunc(sysdate)+(14/24) else trunc(sysdate+1)+(10/24) end';
dbms_output.put_line(str2);
execute immediate str2;
end loop;
end;
/