To help you simplify the scheduling of hundreds or even thousands of tasks, Oracle Database includes Oracle Scheduler, an enterprise job scheduler. Oracle Scheduler (the Scheduler) is implemented by the procedures and functions in the
DBMS_SCHEDULER PL/SQL package.
The Scheduler enables you to control when and where various computing tasks take place in the enterprise environment.The Scheduler helps you effectively manage and plan these tasks. By ensuring that many routine computing tasks occur without manual intervention, you can lower operating costs, implement more reliable routines, minimize human error, and shorten the time windows needed.
The section contains:
The Scheduler provides sophisticated, flexible enterprise scheduling functionality, which you can use to:
Run PL/SQL anonymous blocks, PL/SQL stored procedures, and Java stored procedures.
Run executables that are external to the database (external executables), such as applications, shell scripts, and batch files. You can run external executables on the local system or on remote systems. Remote systems do not require an Oracle Database installation; they require only a Scheduler agent. Scheduler agents are available for all platforms supported by Oracle Database and some additional platforms.
Schedule job execution using the following methods:
You can schedule a job to run at a particular date and time, either once or on a repeating basis. You can define complex repeat intervals, such as "every Monday and Thursday at 3:00a.m except on public holidays" or "the last Wednesday of each business quarter."
The Scheduler enables you to start jobs in response to system or business events. Your applications can detect events and then signal the Scheduler. Depending on the type of signal sent, the Scheduler starts a specific job. Examples of event-based scheduling include starting jobs when a file arrives on a system, when inventory falls below predetermined levels, or when a transaction fails.
The Scheduler can run tasks based on the outcome of one or more previous tasks. You can define complex dependency chains that include branching and nested chains.
Prioritize jobs based on business requirements.
The Scheduler enables control over resource allocation among competing jobs, thus aligning job processing with your business needs. This is accomplished in the following ways:
Jobs that share common characteristics and behavior can be grouped into larger entities called job classes. You can prioritize among the classes by controlling the resources allocated to each class. This enables you to ensure that your critical jobs have priority and have enough resources to complete. For example, if you have a critical project to load a data warehouse, then you can combine all the data warehousing jobs into one class and give priority to it over other jobs by allocating it a high percentage of the available resources.
The Scheduler takes prioritization of jobs one step further, by providing you the ability to change the prioritization based on a schedule. Because your definition of a critical job can change over time, the Scheduler enables you to also change the priority among your jobs over that time frame. For example, you may consider the extract, transfer, and load (ETL) jobs used to load a data warehouse to be critical jobs during non-peak hours but not during peak hours. However, jobs that must run during the close of a business quarter may need to take priority over the ETL jobs. In these cases, you can change the priority among the job classes by changing the resource allocated to each class. See "Creating Job Classes" and "Creating Windows" for more information.
Manage and monitor jobs
There are multiple states that a job undergoes from its creation to its completion. Scheduler activity is logged and information such as the status of the job and the last run time of the job can be easily tracked. This information is stored in views and can be easily queried using Enterprise Manager or SQL. These views provide valuable information about jobs and their execution that can help you schedule and better manage your jobs. For example, a DBA can easily track all jobs that failed for a particular user. See "Monitoring and Managing the Scheduler".
Execute and manage jobs in a clustered environment
A cluster is a set of database instances that cooperates to perform the same task. Oracle Real Application Clusters (RAC) provides scalability and reliability without any change to your applications. The Scheduler fully supports execution of jobs in such a clustered environment. To balance the load on your system and for better performance, you can also specify the database service where you want a job to run. See "Using the Scheduler in Real Application Clusters Environments" for more information.
To use the Scheduler, you create Scheduler objects. These are schema objects that define the what, when, and how for job scheduling. Scheduler objects enable a modular approach to managing tasks. One advantage of the modular approach is that objects can be reused when creating new tasks that are similar to existing tasks.
All Scheduler objects have attributes. You assign values to these attributes when you create or modify the objects.
The Scheduler objects include:
Each of these objects is described in detail in other sections of this documentation. The principal Scheduler object is the job. The job defines the action to perform and the schedule by which to perform it. It does so either in a stand-alone manner or by referencing other Scheduler objects.
Because Scheduler objects belong to schemas, you can grant object privileges on them. Some Scheduler objects, including job classes, windows, and window groups, are always created in the
SYS schema, even if the creating user is not user
SYS. All other objects are created in the schema of the creating user or in the designated schema.