Oracle Job Scheduler

The Oracle Job Scheduler

by

Despite the fact that the Oracle job scheduler was introduced back in the 10g version of the database, it seems a lot of people are still using the old job scheduling mechanism – DBMS_JOBS. This is despite the many advantages the job scheduler has in terms of both functionality and ease of use. This article is going to take a look at the Oracle job scheduler, how it works, and why you should be using it rather than DBMS_JOBS.

The Oracle Job Scheduler

Configuring scheduled jobs under the Oracle job scheduler is effectively broken down into three distinct steps:

  1. Firstly, create a schedule which determines when a job should run.
  2. Then create a program. A program stores information about a task such as the name of the package or procedure to run, whether the task is enabled, and so on. It does not include schedule information.
  3. Finally create the job – this is basically the link between the schedule and the program.

Creating A Job Schedule

A schedule is created by calling the DBMS_SCHEDULER.CREATE_SCHEDULE procedure. This procedure takes five parameters:

  1. The schedule name.
  2. The start date.
  3. The repeat interval.
  4. The end date.
  5. A comment parameter.

An example of creating a job schedule is as follows:

BEGIN
  DBMS_SCHEDULER.create_schedule (
    schedule_name => 'my_schedule',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY', 
    end_date => NULL, 
    comments => 'Runs every day for ever.'); 
END; 
/

The REPEAT_INTERVAL parameter offers many different ways of defining the interval and makes creating complex job schedules much easier than when using DBMS_JOB. You can find a full explanation of all the possible values for this parameter here.

Creating A Program

A program is created by calling the DBMS_SCHEDULER.CREATE _PROGRAM procedure. This procedure takes a minimum of three, and up to six paramters:

  1. The program name. Required.
  2. The program action. Required.
  3. The program type. Required.
  4. Number of arguments. Defaults to 0.
  5. Whether the program is enabled or not. Defaults to NO.
  6. A comment parameter. Defaults to null.

An example of creating a program is as follows:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name => 'my_program',
   program_type => 'EXECUTABLE',
   program_action => '/usr/local/bin/date',
   enabled => 'YES',
   comments => 'Runs the O/S date command.');
END;
/

The PROGRAM_TYPE parameter can have three different values:

  1. PL/SQL BLOCK
  2. STORED_PROCEDURE
  3. EXECUTABLE

If the PROGRAM_TYPE is set to EXECUTABLE, the PROGRAM_ACTION specifies a program that is external from the database. This effectively means an operating system command. You can find a full explanation of what all these parameters mean in the Oracle documentation.

Creating A Job

A job is created by calling the DBMS_SCHEDULER.CREATE_JOB procedure. This procedure can take a lot of parameters but the parameters needed when you’ve already defined a schedule and a program as above, are as follows:

  1. The job name. Required.
  2. The program name. Required. This is the name of the program you created using the DBMS_SCHEDULER.CREATE_PROGRAM command.
  3. The schedule name. Required. This is the name of the schedule you created using the DBMS_SCHEDULER.CREATE_SCHEDULE command.
  4. Whether the job is enabled or not. Defaults to No.
  5. A comment parameter. Defaults to null.

An example of creating a job using the schedule and program examples from above is as follows:

DBMS_SCHEDULER.CREATE_JOB (

    job_name => 'my_job',
    program_name => 'my_program',
    schedule_name => 'my_schedule',
    enabled => TRUE,
    comments => 'Job defined by an existing program and schedule.');

Again, you can find a full explanation of all the possible parameters for the CREATE_JOB procedure in the Oracle documentation.

Advantages Of DBMS_SCHEDULER vs DBMS_JOB

The Oracle job scheduler has a number of features that DBMS_JOB doesn’t. For example:

  • It records a history of job runs.
  • The scheduling syntax is simpler. The schedules can also be saved and reused.
  • You can run jobs that execute operating system programs outside of the database.
  • It has a privilege-based security model.

One important thing to note is that DBMS_SCHEDULER performs a COMMIT. This is different to DBMS_JOB and you need to be aware of it if migrating existing jobs to the scheduler.

Leave a Comment