Oracle Built-in Packages

Tips on Using DBMS_JOB

This section discusses several useful tips for using DBMS_JOB.

Job Intervals and Date Arithmetic

Job execution intervals are determined by the date expression set by the interval parameter. Getting jobs to run at the desired times can be one of the more confusing aspects of using DBMS_JOB and the job queue. One key to setting the interval correctly is determining which of the following applies to the job:

• Each execution of the job should follow the last by a specific time interval.

• The job should execute on specific dates and times.

Jobs of type 1 usually have relatively simple date arithmetic expressions of the type SYSDATE+N, where N represents the time interval expressed in days. The following table provides examples of these types of intervals.

Action	Interval Value
Execute daily	‘SYSDATE + 1’
Execute hourly	‘SYSDATE + 1/24’
Execute every 10 minutes	‘SYSDATE + 10/1440’
Execute every 30 seconds	‘SYSDATE + 30/86400’
Execute every 7 days	‘SYSDATE + 7’
Do not re-execute and remove job	NULL

Remember that job intervals expressed as shown in the previous table do not guarantee that the next execution will happen at a specific day or time, only that the spacing between executions will be at least that specified. For instance, if a job is first executed at 12:00 p.m. with an interval of SYSDATE + 1, it will be scheduled to execute the next day at 12:00 p.m. However, if a user executes the job manually at 4:00 p.m. using DBMS_JOB.RUN, then it will be rescheduled ...