Skip Headers
Oracle® Database Administrator's Guide
11g Release 1 (11.1)

Part Number B28310-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

Advanced Scheduler Concepts

Many Scheduler capabilities enable database administrators to control more advanced aspects of scheduling. Typically, these topics are not as important for application developers.

This section discusses the following advanced topics:

Job Classes

Job classes provide a way to:

  • Assign the same set of attribute values to member jobs

    Each job class specifies a set of attributes, such as logging level. When you assign a job to a job class, the job inherits those attributes. For example, you can specify the same policy for purging log entries for all payroll jobs.

  • Set service affinity for member jobs

    You can set the service attribute of a job class to a desired database service name. This determines the instances in a Real Application Clusters environment that run the member jobs, and optionally the system resources that are assigned to member jobs. See "Service Affinity when Using the Scheduler" for more information.

  • Set resource allocation for member jobs

    Job classes provide the link between the Database Resource Manager and the Scheduler, because each job class can specify a resource consumer group as an attribute. Member jobs then belong to the specified consumer group, and are assigned resources according to settings in the current resource plan.

    Alternatively, you can leave the resource_consumer_group attribute NULL and set the service attribute of a job class to a desired database service name. That service can in turn be mapped to a resource consumer group. If both the resource_consumer_group and service attributes are set, and the designated service maps to a resource consumer group, the resource consumer group named in the resource_consumer_group attribute takes precedence.

    See Chapter 25, "Managing Resource Allocation with Oracle Database Resource Manager" for more information on mapping services to consumer groups.

  • Group jobs for prioritization

    Within the same job class, you can assign priority values of 1-5 to individual jobs so that if two jobs in the class are scheduled to start at the same time, the one with the higher priority takes precedence. This ensures that you do not have a less important job preventing the timely completion of a more important one.

    If two jobs have the same assigned priority value, the job with the earlier start date takes precedence. If no priority is assigned to a job, its priority defaults to 3.


    Job priorities are used only to prioritize among jobs in the same class.

    There is no guarantee that a high priority job in class A will be started before a low priority job in class B, even if they share the same schedule. Prioritizing among jobs of different classes depends on the current resource plan and on the designated resource consumer group or service name of each job class.

When defining job classes, you should try to classify jobs by functionality. Consider dividing jobs into groups that access similar data, such as marketing, production, sales, finance, and human resources.

Some of the restrictions to keep in mind are:

  • A job must be part of exactly one class. When you create a job, you can specify which class the job is part of. If you do not specify a class, the job automatically becomes part of the class DEFAULT_JOB_CLASS.

  • Dropping a class while there are still jobs in that class results in an error. You can force a class to be dropped even if there are still jobs that are members of that class, but all jobs referring to that class are then automatically disabled and assigned to the class DEFAULT_JOB_CLASS. Jobs belonging to the dropped class that are already running continue to run under class settings determined at the start of the job.


You create windows to automatically start jobs or to change resource allocation among jobs during various time periods of the day, week, and so on. A window is represented by an interval of time with a well-defined beginning and end, such as "from 12am-6am".

Windows work with job classes to control resource allocation. Each window specifies the resource plan to activate when the window opens (becomes active), and each job class specifies a resource consumer group or specifies a database service, which can map to a consumer group. A job that runs within a window therefore has resources allocated to it according to the consumer group of its job class and the resource plan of the window.

Figure 26-2 shows a workday that includes two windows. In this configuration, jobs that belong to the job class that links to Consumer Group 1 get more resources in the morning than in the afternoon. The opposite is true for jobs in the job class that links to Consumer Group 2.

Figure 26-2 Windows help define the resources that are allocated to jobs

Description of Figure 26-2 follows
Description of "Figure 26-2 Windows help define the resources that are allocated to jobs"

See Chapter 25, "Managing Resource Allocation with Oracle Database Resource Manager" for more information on resource plans and consumer groups.

You can assign a priority to each window. If windows overlap, the window with the highest priority is chosen over other windows with lower priorities. The Scheduler automatically opens and closes windows as window start times and end times come and go.

A job can name a window in its schedule_name attribute. The Scheduler then starts the job when the window opens. If a window is already open, and a new job is created that points to that window, the job is not started until the next time the window opens.

See "Creating Windows" for examples of creating and using windows.


If necessary, you can temporarily block windows from switching the current resource plan. For more information, see "Enabling Oracle Database Resource Manager and Switching Plans", or the discussion of the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure in Oracle Database PL/SQL Packages and Types Reference.

Window Groups

You can group windows for ease of use in scheduling jobs. If a job must run during multiple time periods throughout the day, week, and so on, you can create a window for each time period, and then add the windows to a window group. You can then set the schedule_name attribute of the job to the name of this window group, and the job executes during all the time periods specified in the window group.

For example, if you had a window called "Weekends" and a window called "Weeknights," you could add these two windows to a window group called "Downtime." The data warehousing staff could then create a job to run queries according to this Downtime window group—on weeknights and weekends—when the queries could be assigned a high percentage of available resources.

If a window in a window group is already open, and a new job is created that points to that window group, the job is not started until the next window in the window group opens.

See "Creating Window Groups" for examples of creating window groups.

External Jobs

This section discusses the Scheduler's support for external jobs.

An external job is an operating system executable that runs outside the database. For an external job, job_type is specified as EXECUTABLE. (If using named programs, the corresponding program_type would be EXECUTABLE.) The job_action (or corresponding program_action if using named programs) is the full operating system–dependent path of the desired external executable, excluding any command line arguments. An example might be /usr/local/bin/perl or C:\perl\bin\perl. The program or job arguments for type EXECUTABLE must be a string type such as CHAR or VARCHAR2. They are set with the DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE procedure.

Most, but not all, platforms support external jobs. For platforms that do not support external jobs, creating or setting the attribute of a job or a program to type EXECUTABLE returns an error. See your operating system-specific documentation for more information. Note that a Windows batch file is not directly executable and must be run with cmd.exe.

Like any Scheduler job, you can assign a schema when you create the job. That schema then becomes the job owner. Note that although it is possible to create an external job in the SYS schema, Oracle recommends against this practice.

The CREATE JOB and CREATE EXTERNAL JOB privileges are both required for any schema that runs external jobs.

External jobs must run on the host computer as some operating system user. Thus, you must be able to assign operating system credentials to any external job that you create. You do so with a database object introduced in Oracle Database 11g Release 1 called a credential.

There are two types of external jobs: local external jobs and remote external jobs. Local external jobs run on the same computer as the database that schedules them. Remote external jobs run on a remote host—that is, on a host computer other than the computer running the database that schedules them.

Credentials, local external jobs, and remote external jobs are discussed in detail in the following sections:

About Credentials

A credential is a username and password pair stored in a dedicated database object. You set the credential_name attribute of an external job to designate a credential for that job. The job then runs under the username and password specified by that credential.

You use the DBMS_SCHEDULER.CREATE_CREDENTIAL procedure to create a credential. A credential can be used only by a job whose owner has EXECUTE privileges on the credential or whose owner is also the owner of the credential. Because a credential belongs to a schema like any other schema object, you use the GRANT SQL statement to grant privileges on a credential.



You can query the *_SCHEDULER_CREDENTIALS views to see a list of credentials in the database. Credential passwords are stored obfuscated, and are not displayed in the *_SCHEDULER_CREDENTIALS views.

About Local External Jobs

A local external job runs on the same computer as the Oracle database that schedules it. For such a job, the destination job attribute is null or contains a value of localhost. You do not have to assign a credential to a local external job, although Oracle recommends that you do so. If you do not assign a credential by setting the credential_name job attribute, the job runs under default credentials. Table 26-1 defines the default credentials for different platforms and different job owners.

Table 26-1 Default Credentials for Remote External Jobs

Job in SYS Schema? Platform Default Credentials



User who installed Oracle Database


UNIX and Linux

Values of the run-user and run-group attributes specified in the ORACLE_HOME/rdbms/admin/externaljob.ora file



User that the OracleJobScheduler Windows service runs as


Default credentials may be deprecated in a future release. It is therefore best to assign a credential to every local external job.

To disable the running of local external jobs that were not assigned credentials, remove the run_user attribute from the ORACLE_HOME/rdbms/admin/externaljob.ora file (UNIX and Linux) or stop the OracleJobScheduler service (Windows). Note that these steps do not disable the running of local external jobs in the SYS schema.

Some additional post-installation steps might be required to ensure that local external jobs are enabled. See your operating system-specific documentation for any post-installation configuration steps. For example, on Windows, you must set the username and password for the user account under which the OracleJobScheduler service is to run, and then enable the service.

About Remote External Jobs

A remote external job runs on a host computer other than the computer running the Oracle database that schedules it. For purposes of this discussion, the database host is the computer containing the database used to schedule a remote external job, and the remote host is the computer that the remote external job is to run on. The remote host may or may not have Oracle Database installed. However, in all cases, the remote host has a Scheduler agent that the database communicates with to start external jobs on the remote host. The agent is also involved in returning execution results to the database. The agent is an executable on a remote host that is installed separately. It listens on a network port for incoming job requests and executes them.

When setting up to run a remote external job, you specify a remote host and port as the destination attribute of the job. In addition, you must specify a credential for a remote external job.

Scheduler Support for Oracle Data Guard

Beginning with Oracle Database 11g Release 1, the Scheduler can run jobs based on whether a database is a primary database or a logical standby in an Oracle Data Guard environment.

For a physical standby database, any changes made to Scheduler objects or any database changes made by Scheduler jobs on the primary database are applied to the physical standby like any other database changes. For the primary database and logical standby databases, there is additional functionality that enables you to specify that a job can run only when the database is in the role of the primary database, or can run only when the database is in a logical standby role.

You do this using the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to set the database_role job attribute to one of two values: PRIMARY or LOGICAL STANDBY. (To run a job in both roles, you can make a copy of the job and set database_role to PRIMARY for one job and to LOGICAL STANDBY for the other).

On switchover or failover, the Scheduler automatically switches to running jobs specific to the new role. DML is replicated to the job event log so that on failover, a record of what had run successfully on the primary database until it failed is available.