Refreshing an existing materialized View

Question

I created a materialized view with the following information.

CREATE MATERIALIZED VIEW EMPLOYEE_INFO
AS
SELECT * FROM EMPLOYEE_TABLE WHERE LOCATION = 'Brazil'

I did not add any refresh interval to this MV initially. Now, I need to refresh this MV everyday at 0000HRS. Will the following command help me to alter it for everyday at 0000HRS?

ALTER MATERIALIZED VIEW EMPLOYEE_INFO
REFRESH COMPLETE 
START WITH SYSDATE

In case, I need to refresh it for every 6 hours, how do I perform it? Is it possible?

Answer 1

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

ALTER MATERIALIZED VIEW EMPLOYEE_INFO
 REFRESH COMPLETE
 NEXT TRUNC(SYSDATE) + 1

To refresh every 6 hours:

ALTER MATERIALIZED VIEW EMPLOYEE_INFO
 REFRESH COMPLETE
 NEXT SYSDATE + 6/24

From 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.