Basic Scheduler Concepts - Oracle 10g

The Scheduler offers a modularized approach for managing tasks within the Oracle environment. By breaking down a task into its components such as time, location, and database object, the Scheduler offers you an easier way to manage your database environment. Another advantage of this modularization is that different users can perform similar tasks with only small modifications. In the Scheduler, all the components are database objects like a table, which enables you to use normal Oracle privileges. Oracle provides transient jobs, which are deleted once executed. This means that all the metadata about the job is deleted from the database. Of course, the job log will contain an entry for the transient job so it can be audited. In addition to transient jobs, the Scheduler also enables you to create persistent jobs.

General Rules for all Database Objects

The following rules apply for all objects when using the Scheduler:

  • If you try to drop an object that does not exist, a PL/SQL exception is raised saying that the object does not exist.
  • If you try to enable or disable an object that is already enabled or disabled, no error is generated.
  • If you try to alter an object that does not exist, an exception is raised saying that the object does not exist.

The basic elements of the Scheduler are:

  • Programs
  • Schedules
  • Jobs


A program is a collection of metadata about what will be run by the Scheduler. It includes information such as the name of the program, program action (for example, a procedure or executable name), program type (for example, PL/SQL and Java stored procedures or PL/SQL anonymous blocks) and the number of arguments required for the program.
A program is a separate entity from a job. Jobs can be created using existing programs, which means that different jobs can use the same program. Given the right privileges, different users can use the same program without having to redefine it. This enables the creation of program libraries, where users can select from a list of existing programs.


A schedule specifies when and how many times a job is executed. Jobs can be scheduled for processing at a later time or immediately. For jobs to be executed at a later time, the user can specify a date and time when the job should start. For jobs that repeat over a period of time, an end date and time can be specified, which indicates when the schedule expires. A schedule also has a limit associated with it, which is expressed as a time duration. The schedule limit indicates that a job should not be run if the duration has elapsed after the scheduled start time and the job has not yet been started. If the job was a repeating job, it is rescheduled. Similar to programs, schedules are database entities and can be saved in the database. Users with the right privileges can share saved schedules. For example, the end of quarter may be a common schedule for many jobs. Instead of each user having to redefine the schedule each time, they can all point to a saved schedule. Some examples of schedules you might use to control time -based jobs are:

  • Run on Wednesday, December 26th, 2001 at 2pm
  • Run every Monday, at 8am, starting on December 26th, 2001, and ending on January 31st, 2002
  • Run on every working day


A job is a user -defined task that is scheduled to run one or more times. It is a combination of what (the action) needs to be executed and when (the schedule). Users with the right privileges can create jobs either by simply specifying the action and schedule at the time of job creation, or by using existing programs and schedules. To customize an existing program, users can specify values for the program arguments during job creation, which will override the defaults that were specified when the program was created. A common example of a job is backing up data on a personal computer. This is a task that most users perform on a nightly basis. It would therefore be best to create a program for this task that can be shared among different users. The program action would be the backup script and the program would have one argument, the path where the files that need to be backed up reside. Each user would create a job that points to this program and specify the path that is unique to them during the job creation, thus customizing the program for their own needs.

Job Instances

A job instance represents a specific run of a job. Jobs that are scheduled to run only once will have only one instance, however, jobs that have a repeating schedule will have multiple instances, with each run of the job representing an instance. For example, a job that is scheduled to run on Tuesday, Oct. 8th 2002 will have one instance. A job that runs daily at noon for a week will have seven instances, one for each time the job ran. So, in this case, the run on Tuesday at noon represents one instance of the job. When a job is created, only one entry is added to the job table to represent the job, however, each time the job runs, an entry will be added to the job log. Therefore, if you create a job that has a repeating schedule, you should find one entry in the job table and multiple entries in the job log. Each run of the job is assigned an instance ID that can be used to monitor job progress and manage future runs. They provide specific information about a particular run, for example, the job status and the start and end time.

How Programs, Jobs, and Schedules are Related

To determine when, where, and what will be executed in the database, you need to assign relationships among programs, jobs, and schedules. You can, however, leave a program, job, or schedule unassigned if you wish. You could, for example, create a job that calculates a year end inventory and leave it unassigned. That could be J4. Similarly, schedules and programs could exist for year end tasks and be left unassigned. To understand , consider a situation where tables are being analyzed.

In this example, P1 would be a program to analyze a table using the DBMS_STATS package. The program takes in a variable for the table name. Two jobs, J1 and J2, both point to the same program, which specifies a different table name. Finally, schedule S1 could have a value of 2AM for when the jobs should be performed. The end result is that the two tables named in J1 and J2 would be analyzed at 2AM. Note that P2, P9, J4, and S2 are entities that have nothing pointed to them. so, for example, J4 could be self-contained with all relevant information inside the job itself. Also note that more than one job can map to a program or schedule.

Relationships Among Programs, Jobs, and Schedules

Relationships Among Programs, Jobs, and Schedules

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 10g Topics