Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Job Scheduler Time Slide

Oracle Database Tips by Donald Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

It would appear that all of the above expressions give exactly the same run schedule regardless of which syntax is used.  In practice this not true because PL/SQL expressions can allow the run schedules of jobs to slide.


The scheduler attempts to execute all jobs on time, but in practice there is often a small delay.  When a job is executed, the first thing that happens is the next run date is calculated using the specified repeat interval.  Since most PL/SQL expressions use either the sysdate or systimestamp functions, the actual start date may be slightly later than the job's original next run date.  Over several iterations of the job, this could add up to a noticeable difference between the times the job is expected to run and when it actually does run.


Table 11.9 gives an example of the sort of slide that might be seen if the scheduler is consistently ten seconds late in executing a job that was originally intended to run a 09:00:00 each day.






01-JAN-2004 09:00:10

02-JAN-2004 09:00:10


02-JAN-2004 09:00:20

03-JAN-2004 09:00:20


03-JAN-2004 09:00:30

04-JAN-2004 09:00:30


04-JAN-2004 09:00:40

05-JAN-2004 09:00:40


05-JAN-2004 09:00:50

06-JAN-2004 09:00:50


06-JAN-2004 09:01:00

07-JAN-2004 09:01:00

Table 11.9 - The Time Slide Phenomenon

This issue becomes even more noticeable on shorter repeat intervals such as hourly runs. This behavior can be prevented by always defining PL/SQL expressions that result in a specific time rather than one relative to the current time.  This is typically done using the trunc and round functions to remove the variable components. For example, 'trunc(sysdate) + 1 + 6/24' is always 06:00 tomorrow morning no matter what time it is evaluated because the time component has been truncated.  The earlier examples regularly make use of the trunc function for the same reason.


The calendar syntax does not suffer from the problem of sliding schedules as the repeat intervals it defines are always time specific.  If a component of the calendar string is not defined explicitly, it is defaulted using values from the start date specified when the job or schedule was defined.  For example, a schedule with a start date of 01-JAN-2004 09:45:31 and a calendar string with no byminute clause would actually be assigned byminute=45.  As a result, every next_run_date evaluated using this schedule would have a value of 45 minutes past the hour.


Sometimes it is either not possible or very difficult to define a repeat interval using the calendar syntax or a PL/SQL expression.  In these situations, it might be easier to use a database function which returns a date or timestamp as required.  The my_schedule_function.sql script creates a function which returns a different time interval depending on the contents of the database.




-- Requires the following grant:

--   grant select on v_$database to job_user;

-- **********************************************************


CREATE OR REPLACE FUNCTION my_schedule_function (

  p_timestamp  IN  TIMESTAMP)



  l_db_name    v$;

  l_timestamp  TIMESTAMP;


  SELECT name

  INTO   l_db_name

  FROM   v$database;


  CASE l_db_name

    WHEN 'PROD' THEN l_timestamp := p_timestamp + INTERVAL '10' MINUTE;

    WHEN 'TEST' THEN l_timestamp := p_timestamp + INTERVAL '1' HOUR;

    ELSE l_timestamp := p_timestamp + INTERVAL '1' DAY;



  RETURN l_timestamp;




When this script is run against the development environment with a database name of DB10G, the following run schedule is produced:


SQL1> exec test_timestamp_string('my_schedule_function(systimestamp)');

Next Run Date: 02-JAN-2004 03:04:32

Next Run Date: 03-JAN-2004 03:04:32

Next Run Date: 04-JAN-2004 03:04:32

Next Run Date: 05-JAN-2004 03:04:32

Next Run Date: 06-JAN-2004 03:04:32


PL/SQL procedure successfully completed.


The same result could be achieved by running a different schedule in each environment, but it serves to illustrate the point.


It should now be obvious that there is an almost limitless combination of possible calendar string and PL/SQL expression variations.  The only way to become really confident with repeat intervals is to try as many variations as possible.  The test procedures presented in this section will allow this to be done without having to actually schedule jobs and this, in turn, will save lots of time.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.