Use DBMS_SCHEDULER to replace DBMS_JOB


DBMS_JOB has been around forever, and now it is deprecated. Although DBMS_JOB still exists in 10g and 11g, but only for backward compatibility. No new features are being added to dbms_job and you will likely quickly run into its limitations. Oracle recommends the use of DBMS_SCHEDULER in releases 10g and up. DBMS_SCHEDULER is a much more robust package and fully-featured than DBMS_JOB. To use the DBMS_SCHEDULER package a user must be granted the CREATE JOB privilege.

DBMS_SCHEDULERincludes the following features that DBMS_JOB does not have :

  • logging of job runs (job history)
  • simple but powerful scheduling syntax (similar to but more powerful than cron syntax)
  • running of jobs outside of the database on the operating system
  • resource management between different classes of jobs
  • use of job arguments including passing of objects into stored procedures
  • privilege-based security model for jobs
  • naming of jobs and comments in jobs
  • stored, reusable schedules

Features in releases after 10g Release 1 include :

  • dependencies between job units (10gR2 and up)
  • scheduling based on financial calendars and fiscal quarters (10gR2 and up)
  • event based jobs which run when an event is received (10gR2 and up)
  • running of jobs on remote machines (11gR1 and up)
  • e-mail notifications on job events of interest (10gR2 and up)
  • starting a job based on arrival of a file (10gR2 and up)

Here simple comparison for the codes :

  • Old using DBMS_JOB scheduler.VARIABLE l_job NUMBER;
    BEGIN
    DBMS_JOB.submit (
    job => :l_job,
    what => ‘BEGIN NULL; /* code to execute*/ END;’,
    next_date => SYSDATE,
    interval => ‘SYSDATE + 1 /* 1 Day */’);COMMIT;
    END;
    /
    PRINT l_job
  • New with DBMS_SCHEDULER scheduler.BEGIN
    DBMS_SCHEDULER.create_job (
    job_name => ‘dummy_job’,
    job_type => ‘PLSQL_BLOCK’,
    job_action => ‘BEGIN NULL; /* code to execute */ END;’,
    start_date => SYSTIMESTAMP,
    repeat_interval => ‘SYSTIMESTAMP + 1 /* 1 Day */’);
    END;
    /

After replace DBMS_JOB with DBMS_SCHEDULER for all jobs successful, the job_queue_processes parameter can now be set to zero.

alter system set job_queue_processes=0;

There is also a forum dedicated to questions about dbms_scheduler here : http://forums.oracle.com/forums/forum.jspa?forumID=193

Advertisements

One Response

  1. […] Use DBMS_SCHEDULER to replace DBMS_JOB […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: