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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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.

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

  COMMIT;
END;
/
PRINT l_job

-- New scheduler.

BEGIN
  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 */');
END;
/

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