Scheduler and Interval Questions

[jmodic]

Originally posted by slimdave
Having said that, the NEXT_DATE is calculated at the end of the job execution. If you wanted the job to start on the next hour after it last finished, your next_date expression would be ...

Trunc(sysdate,'HH')+(1/24)

Not quite so. The NEXT_DATE is calculated *at the very beginning of the job execution*, not at the end. However, the catch is, this newly calculated time is not updated (in fact, this update is merely not commited) in the job queue untill the currently running process finished. However once the current job finished (implicit commit performed regardles whether the job finished successfuly or with error), the vaalue of the next_date that was calculated when the job was started becomes wisible to the outsside world.

Let's take an example. The interval for the job is set to "Trunc(sysdate,'HH')+(1/24)", meaning that it should run at every full hour. Lets say it is executed at 8:00. As soon as the job starts to execute, the NEXT_DATE is calculated to 9:00. The job queue tables is updated immediately with this value, however if you query DBA_JOBS while it is running, you won't be able to see this, because the update is not yet commited. Let's say that job runs for 55 minutes and finishes at 8:55. As soon as it finishes, the NEXT_DATE=9:00 becomes visible. So at approximately 9:00 the job is started again and the NEXT_DATE is recalculated to 10:00. Now let's say that this time job runs for 65 minutes. At 10:00 the process that checks job que table can't see that the next_date for this job is allready set to this new value. But at 10:05, when it finishes, you are able to see that the NEXT_DATE of this job is set to 10:00,and so does the process that checks the job queue table. The first time it checks the queue after job has finished it finds that this job was due to start 5 minutes ago and will run it immediately.

If you realy wanted to schedule the job to restart an hour after it has finished, you should reset the NEXT_DATE from within the job's body code (by finding the job_number of the current job and then using DBM_JOB.NEXT_DATE() procedure). Again, you can't set something like this from the outside when you define a job, it must be done dinamicaly from within the job itself.