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;
/
