ALTER Materialized View Refresh Schedule

March 08, 2020  

For periodic refresh you must use NEXT clause. To refresh everyday at 00:00:

ALTER MATERIALIZED VIEW RAMESH.KUMAR_MVW REFRESH COMPLETE NEXT TRUNC(SYSDATE) + 1
/

To refresh every 6 hours:

ALTER MATERIALIZED VIEW RAMESH.KUMAR_MVW REFRESH COMPLETE NEXT SYSDATE + 6/24
/


Here is the automated script for MView Refresh Schedule:

The basic syntax is:

ALTER MATERIALIZED VIEW RAMESH.KUMAR_MVW

REFRESH COMPLETE

START WITH TRUNC(SYSDATE+1) +(13/24)

NEXT CASE WHEN TO_CHAR(SYSDATE,'hh24' ) BETWEEN '13' AND '14' 

THEN TRUNC(SYSDATE)+(14/24) 

ELSE TRUNC(SYSDATE+1)+(13/24) 

END

/

The above command refreshes the mview at 1 PM and 2 PM

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

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

From oracle documentation (ALTER MATERIALIZED VIEW):

START WITH Clause

Specify START WITH date to indicate a date for the first automatic refresh time.

NEXT Clause

Specify NEXT to indicate a date expression for calculating the interval between automatic refreshes.

Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, then Oracle Database determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, then Oracle Database refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the alter_mv_refresh entirely, then Oracle Database does not automatically refresh the materialized view.

At the time of the next automatic refresh, Oracle Database refreshes the materialized view, evaluates the NEXT expression to determine the next automatic refresh time, and continues to refresh automatically.


Source: Oracle