Migrating from dbms_job to dbms_scheduler

http://www.dba-oracle.com/job_scheduling/dbms_job_scheduler.htm

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.