Oracle materialized view refresh schedule


Since I see that it is possible to specify an expression like sysdate + 1, here are my questions:

  • Is it possible to specify the exact time of the refresh for materialized views in oracle?

  • Is it possible that it all starts at 2 a.m. while there is nobody using the db from my application?

Best Answer

Your expression can be as complicated as you'd like so you can certainly specify that the refresh should happen every day at 2 AM, i.e.

NEXT trunc(sysdate+1) + interval '2' hour

will specify that the refresh happens every day at 2 AM. The expression is evaluated at the conclusion of each refresh so you just need to ensure that the expression evaluates to whatever time you want at that particular instant in time.