4

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?

Improve this question

1 Answer 1

Reset to default
13

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

In case I use the below command, will it just refresh till the current date and time stamp? ALTER MATERIALIZED VIEW EMPLOYEE_INFO REFRESH COMPLETE START WITH SYSDATE
Refreshes once at current time.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.