Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Oracle Job Scheduling

Chapter 3 - Time-Based Job Scheduling

Complex Date Rules for Job Execution

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.

The above text is an excerpt from:

Oracle Job Scheduling
Creating Robust Task Management with dbms_job and Oracle10g dbms_scheduler

ISBN 0-9744486-6-4

by Dr. Timothy S. Hall

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.