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 Scheduling Migrating from dbms_job to dbms_scheduler

Oracle Tips by Burleson Consulting

Migrating from dbms_job to dbms_scheduler

This section will introduce options available for migrating jobs from the dbms_job package to the dbms_scheduler package.  Depending on how simple or complex the process is, it is a matter of personal preference how far through this migration process to go.

Since Oracle 10g and beyond supports both the dbms_job and dbms_scheduler packages, it may be wise to continue running existing jobs until familiar with all the features of the new scheduler.  This ?do nothing? approach is only a temporary option as the original scheduler is retained for backwards compatibility only.  There is no guarantee that it will be present in future versions of the database, so take steps now to future-proof the system.

When deciding to start converting jobs, the easiest option is to use the new scheduler the same way the old one was used.  In previous sections, a simple example of each scheduling mechanism was presented.

-- Old scheduler.

  DBMS_JOB.submit (
    job       => :l_job,
    what      => 'BEGIN NULL; /* Do Nothing */ END;',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1 /* 1 Day */');   

PRINT l_job

-- New scheduler.

  DBMS_SCHEDULER.create_job (
    job_name        => 'dummy_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN NULL; /* Do Nothing */ END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'SYSTIMESTAMP + 1 /* 1 Day */');

By comparing these examples, it is noted that conversion of basic jobs is quite simple, involving the following steps:

* Define a meaningful job_name for the new job.

* Assign a job_action of PLSQL_BLOCK.

* Use the what value from the old job as the job_action value in the new job.

* Use SYSTIMESTAMP for the start_date value.

* Use the interval value from the old job as the repeat_interval value in the new job, making sure the result of the expression is a TIMESTAMP not a DATE.

Once this conversion has been completed for all jobs, there is freedom from using the old scheduler, so the job_queue_processes parameter can now be set to zero.

alter system set job_queue_processes=0;

Next, the use of the calendar syntax to replace the PL/SQL expressions used in the repeat_interval should be investigated.  The calendar syntax is easier to read than a PL/SQL expression and always results in a specific run time, rather than a drifting interval.  The previous repeat_interval value could be altered as shown below, scheduling the job to run every day at 6:00 a.m.

repeat_interval => ?freq=daily; byhour=6; byminute=0; bysecond=0?

Once the basic jobs are converted, the next step might be to identify common job_action and repeat_interval values that can be used to create programs and schedules, respectively.  The job definitions can then be revised to use these sharable components, allowing a single point for management of job definitions.

If control of the resources allocated to jobs is desired, related jobs can be grouped into job classes which are linked to specific resource consumer groups.  In addition, window definitions permit automatic switching of the server?s active resource plan, which allows the automatic process of altering resource usage over time.


This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.


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

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational