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 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)

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@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

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.


John Reimer



Search Discussions

Discussion Posts


Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 2 | next ›
Discussion Overview
grouporacle-l @
postedJan 4, '12 at 7:17p
activeJan 6, '12 at 6:49p

2 users in discussion

Reimer, John J.: 1 post Joel Patterson: 1 post



site design / logo © 2022 Grokbase