Monday, February 09, 2015

Refresh Materialized View Daily at Specific Time

We can define a specific time of the day to refresh a materialized view. For example, the following query makes the existing materialized view to be refreshed immediately and then every day at 7pm.

SQL> alter materialized view MV_NAME refresh start with sysdate next trunc(sysdate)+19/24;
In the above SQL command, trunc(sysdate) makes the date start from the beginning of the day (midnight). The time is then added by 19/24 of the day. For Oracle date type, 1 unit is a day.