DBMS_JOB next_date calculation
- From: "Reimer, John J." <JJReimer@xxxxxxxxxxxxxx>
- To: "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Jan 2012 14:16:45 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
