Oracle Built−in Packages- P121: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Oracle Built-in Packages SEARCH Chapter 13 Job Scheduling in the Database NEXT 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 . with an interval of SYSDATE 1 it will be scheduled to execute the next day at 12 00 . However if a user executes the job manually at 4 00 . using then it will be rescheduled for execution at 4 00 . the next day. Another possibility is that the database is down or the job queue so busy that the job cannot be executed exactly at the time scheduled. In this case the job will run as soon as it can but the execution time will have migrated away from the original submission time due to the later execution. This drift in next execution times is characteristic of jobs with simple interval expressions. .
