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:
- Firstly, create a schedule which determines when a job should run.
- 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.
- 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:
- The schedule name.
- The start date.
- The repeat interval.
- The end date.
- 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:
- The program name. Required.
- The program action. Required.
- The program type. Required.
- Number of arguments. Defaults to 0.
- Whether the program is enabled or not. Defaults to NO.
- 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:
- PL/SQL BLOCK
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:
- The job name. Required.
- The program name. Required. This is the name of the program you created using the DBMS_SCHEDULER.CREATE_PROGRAM command.
- The schedule name. Required. This is the name of the schedule you created using the DBMS_SCHEDULER.CREATE_SCHEDULE command.
- Whether the job is enabled or not. Defaults to No.
- A comment parameter. Defaults to null.
An example of creating a job using the schedule and program examples from above is as follows:
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.