The Database Design Resource Center



Oracle Scheduler : Putting tasks on autopilot

The Oracle Scheduler enables database administrators and application developers to control when and where various tasks take place.

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;

Oracle Scheduler

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';
Oracle Scheduler

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


Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant


Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:


What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials



Database Normalization eBook:


Database Normalization eBook




Copyright © www.databasedesign-resource.com /
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.