I have a job that refreshes a materialised view refresh group on Oracle 19c. I wanted to refresh the group daily at midnight –
trunc(sysdate)? The job was made using using the following PL/SQL:
BEGIN DBMS_REFRESH.MAKE ( name => 'REFG_1', list => '', next_date => trunc(sysdate), interval => 'SYSDATE + 1', implicit_destroy => FALSE, rollback_seg => '', push_deferred_rpc => TRUE, refresh_after_errors => FALSE); END; /
The MVs are getting refreshed daily. However, they are refreshing at 11:26AM (this coincides with the time I originally created the job).
How do I create this job to ensure it refreshes at midnight each night?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Interval should be
trunc(sysdate)+1 to mean midnight tomorrow.
next_date will be evaluated to midnight today (in the past) so the first refresh will happen ASAP. You probably want this to also be tomorrow midnight
The next execution time (
SYSDATE+1 in your case) of your job is evaluated when the previous job has been finished. Thus it will be shifted more and more.
TRUNC(SYSDATE)+1 or change to modern Scheduler Jobs were you have more freedom.