RE: DBMS_JOB next_date calculation
Date: Fri, 6 Jan 2012 13:48:25 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8C3406AF_at_JAXMSG01.crowley.com>
Not sure if this is what you are looking for, but to stop the start time slippage you can use the truncate command.
By Truncating, you can reset the start time to the same time each run without slipping.
Here is an example
select to_char(sysdate, 'YYYY-Mon-DD HH24:MI:SS') now FROM dual; prompt
prompt trunc(sysdate + 1/24,'HH') (top of next hour) prompt trunc(sysdate) + 11/24 (11 hours from now). prompt trunc(sysdate + 5) + 2/24 ( 5 days from today at 02:00am)prompt
execute dbms_job.interval(&jobnum, '&interval_string');
execute dbms_job.next_date(&job_num,&next_date);
Joel Patterson
Database Administrator
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Reimer, John J.
Sent: Wednesday, January 04, 2012 2:17 PM
To: Oracle-L
Subject: DBMS_JOB next_date calculation
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-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jan 06 2012 - 12:48:25 CST