DBMS_JOB next_date calculation
Date: Wed, 4 Jan 2012 14:16:45 -0500
Message-ID: <192E07895A25D94B9ECDA303F8CED9A1498E2A_at_FA0014>
Several years (not sure how many) and Oracle versions ago, I seem to recall testing how DBMS_JOB sets the next_date to run a job. It seemed to use the INTERVAL setting for the job with SYSDATE at the time the job started to calculate next_date. However, if the job ran past what that calculation computed, it actually used the INTERVAL setting using the SYSDATE of the time the job ended to calculate next_date. So, if a job was set to run every hour, and a run lasted 1.5 hours, the next run would begin one hour after the last one finished. If it ran for less than one hour, the next run would begin one hour from the start of the previous run.
That is not the behavior we saw with a long running job recently, so I tested this again. On both a 9i and 10g database, if a DBMS_JOB job runs past the time the INTERVAL is set for, Oracle seems to place the SYSDATE at job completion into next_date. So, if a job runs longer than the INTERVAL is set for, it will start back up again as soon as it finishes.
I have looked at Oracle documentation, and it says that the INTERVAL is evaluated before the job is executed. It says it must be a time in the future. This date becomes the new NEXT_DATE after the job executes successfully. I can't find anything that says what Oracle does if the calculated date is not in the future at that point in time.
Is there a parameter setting that could affect how NEXT_DATE is calculated? I know scheduler has more flexibility, but we have many DBMS_JOB jobs, and I want to be able to say with certainty what will happen if a job runs long.
Thanks,
John Reimer
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 04 2012 - 13:16:45 CST