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.

��