Oracle Scheduler : Putting tasks on autopilotThe 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.
The Oracle Scheduler provides a number of benefits:
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;
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.
To create a simple, self-contained job where attributes are specified in the job itself, perform the following:
To create a saved schedule:
Creating a job that uses the program:
Free eBookSubscribe 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.Read more Testimonials
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."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
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
The name Oracle is a trademark of Oracle Corporation.