Oracle Scheduler : Putting tasks on autopilot

The Scheduler uses three main components:

A schedule specifies when and how many times a job is executed. Similar to programs, schedules are database entities and can be saved in the database. The same schedule can be used by multiple jobs.

A program is a collection of metadata about what will be run by the scheduler. This includes information such as the program name, the type of program, and information about arguments passed to the program.

A job specifies what needs to executed and when. For example, the "what" could be a PL/SQL procedure, an executable C program, a java application, a shell script, or client-side PL/SQL. You can specify the program (what) and schedule (when) as part of the job definition, or you can use an existing program or schedule instead.

The Oracle Scheduler provides complex enterprise scheduling functionality that enables an o rganization to easily and effectively manage database maintenance and other routine tasks.

The Scheduler enables limited computing resources to be allocated appropriately among competing jobs, thus aligning job processing with your business needs.

It leverages the reliability and scalability of the Oracle database to provide a robust environment for running jobs.

BENEFITS:

The Oracle Scheduler provides a number of benefits:

• Easy to use
• Minimum development time is required since jobs can be easily defined and scheduled using simple mouse operations.
• Scheduler objects are modular and can be shared with other users thus reducing the development time for new jobs.
• The graphical interface makes it easy for users to manipulate existing Oracle Scheduler objects. Object properties can be modified to create new objects.
• The same operation can be performed on multiple jobs. For example, multiple jobs can be stopped in one call.
• Easy to manage
• Jobs can be easily moved from one system to another, for example from a development environment to production, by using the EXPORT or IMPORT utility in the database.
• Exception based management enables administrators to quickly focus on jobs with errors without having to wade through all the jobs.
• Jobs can be managed as a group.
• All Oracle Scheduler activities can be logged, providing an audit trail of all scheduler activities.
• There is support for time zones, which makes it easy to manage jobs in any time zone.
• The Scheduler can be accessed and controlled from anywhere, providing the utmost flexibility.
• All Scheduler activity can be carried out from the same graphical interface.
• Jobs can be filtered and sorted for easy viewing.
• Existing database knowledge can be leveraged, therefore eliminating the need to learn a new system and syntax.
• Since the Oracle Scheduler is part of the database, it is platform independent, therefore jobs can be managed similarly on all platforms.
• There is no extra licensing or cost that is required for the Scheduler because it is a feature of the database.
• The Scheduler can immediately exploit new database features.
• The Scheduler inherits all the database features: high security, high availability, and high scalability.

The Oracle Scheduler uses the supplied PL/SQL package DBMS_SCHEDULER to handle almost all scheduling Here is an example of scheduling a task with DBMS_JOB:

VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT (
job => :jobno
,what => 'BEGIN DBMS_STATS.
GATHER_SCHEMA_STATS(''HR'');END;'
,next_date => '09/01/2004 21:00:00'
,interval => 'TRUNC(SYSDATE) + 1 + 21/
24');
COMMIT;
END;

Monitoring and managing is a key activity in a job system. Jobs can be managed at a group level making it easy to manage large number of jobs.

The GUI provides a central overview of all scheduler objects, enabling administrators to easily monitor the progress of jobs. It enables them to quickly identify and rectify the malfunctions in Scheduler activities.

Jobs can be filtered and sorted by any attribute of the job, making it easy to identify jobs that are in an error state. Jobs can be viewed, altered, stopped or killed without having to go to another system or screen, by simply clicking on the job name, making it easy to resolve problems.

Here is an example of scheduling that same task with DBMS_SCHEDULER. The parameters now make sense when compared to DBMS_JOB. And gone at last is that wacky INTERVAL parameter.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'HR_STATS_REFRESH'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''HR'');
END;'
,start_date => '09/01/2004 09:00 PM'
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'Refreshes the HR Schema every night at 9 PM'
);
END;

To create a simple, self-contained job where attributes are specified in the job itself, perform the following:

1. Select the Jobs link.
2. Click Create to create a new job
3. Enter the following information, then click OK.
   Name: ALTER_INDX001
   Owner: HR
   Enabled:Yes
   Description: This job will coalesce index HR.EMP_NAME_IX on the EMPLOYEES table.
   Logging Level: log job runs only (RUNS)
   Job Class:DEFAULT_JOB_CLASS
   Auto Drop:FALSE
   Restartable:TRUE
   Command: EXECUTE IMMEDIATE 'alter index HR.EMP_NAME_IX coalesce';

To create a saved schedule:

1. Creating a Saved Schedule.
2. Select the Schedules link.
3. Click Create to create a new schedule.
   Enter the following information, then click OK.
   Name: SCHED001
   Owner: HR
   Description: Run at 11:00PM every night for the next year
   Start: Later
   Date:Today's date
   Time:11:00PM
   Frequency:1 Days
   Repeat Until: Custom
   Date: One year from today's date
   Time 11:00PM

Creating a job that uses the program:

1. Click Jobs and then create.
2. Enter the following information, then click Change Command Type.
   Name: LOADDATA_JOB1
   Schema: HR
   Enabled: Yes
   Description: This job uses the program loaddata
   Logging Level: log job runs only (RUNS)
   Job Class:DEFAULT_JOB_CLASS
   Auto Drop:FALSE
   Restartable:TRUE
3. Select Program Name and click the search light.
4. Select LOADDATA from the list and click Select
5. Scroll down to the Arguments heading
6. Select User defined from the drop down list for the Option column. Enter /(wkdir path)/loaddata1.dat in the Value column and click OK.

Return to Oracle DBA