dba_jobs and interval

Few examples of some scenarios of setting intervals for dba_jobs. Note: Updating the interval will not update the NEXT_DATE run of the job until the job runs.

Setting a job to run every monday at 10:00 am
SQL> exec dbms_job.interval(5, 'TRUNC(NEXT_DAY(SYSDATE, ''MONDAY''))+(10/24)');
SQL> commit;

— assume job was run and this SQL was run after it was run
SQL> SELECT job, interval, next_date FROM dba_jobs where job = 5;
JOB NEXT_DATE
———- ——————–
5 19-MAR-2012 10:00:00

Setting a job to run every hour on the 1/2 hour
SQL> exec dbms_job.interval(5, 'TRUNC(SYSDATE, ''HH'')+(1.5/24)');
SQL> commit;

Setting a job to run every hour after the last time the job was started
SQL> exec dbms_job.interval(5, 'SYSDATE+(1/24)');
SQL commit;
— assume job was run and this SQL was run after it was run, the time NEXT_DATE would be a hour after the job was last started.
SQL> SELECT job, interval, next_date FROM dba_jobs where job = 5;
JOB NEXT_DATE
———- ——————–
5 15-MAR-2012 22:45:00

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s