Skip Headers

Oracle® Database Administrator's Guide
10g Release 1 (10.1)

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

Go to previous page
Previous
Go to next page
Next
View PDF

28 Administering the Scheduler

Oracle Database provides database scheduling capabilities through the Scheduler. This chapter describes managing a Scheduler environment, including step-by-step instructions for configuring, administering, and monitoring. This chapter contains the following sections:

Configuring the Scheduler

The following tasks are necessary when configuring the Scheduler:


Task 1: Setting Scheduler Privileges

You should have the SCHEDULER_ADMIN role to administer the Scheduler. Typically, database administrators will already have this role with the ADMIN option as part of the DBA (or equivalent) role. You can grant this role to another administrator by issuing the following statement:

GRANT SCHEDULER_ADMIN TO username;

Because the SCHEDULER_ADMIN role is a powerful role allowing a grantee to execute code as any user, you should consider granting individual Scheduler system privileges instead. Object and system privileges are granted using regular SQL grant syntax. An example is if the database administrator issues the following statement:

GRANT CREATE JOB TO scott;

After this statement is executed, scott can create jobs, schedules, or programs in his schema. Another example is if the database administrator issues the following statement:

GRANT MANAGE SCHEDULER TO adam;

After this statement is executed, adam can create, alter, or drop windows, job classes, or window groups. He will also be able to set and retrieve Scheduler attributes and purge Scheduler logs.

See "How to Manage Scheduler Privileges" for more information regarding privileges.


Task 2: Configuring the Scheduler Environment

This section discusses the following tasks:


Task 2A: Creating Job Classes

To create job classes, use the CREATE_JOB_CLASS procedure. The following statement illustrates an example of creating a job class:

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name              => 'my_jobclass1',
   resource_consumer_group     => 'my_res_group1', 
   comments                    => 'This is my first job class.');
END;
/

This statement creates a job class called my_jobclass1 with attributes such as a resource consumer group of my_res_group1. To verify the job class contents, issue the following statement:

SELECT * FROM DBA_SCHEDULER_JOB_CLASSES;

JOB_CLASS_NAME        RESOURCE_CONSU   SERVICE   LOGGING_LEV  LOG_HISTORY    COMMENTS
-----------------     --------------   -------   -----------  -----------    --------
DEFAULT_JOB_CLASS                                       RUNS                 The default
AUTO_TASKS_JOB_CLASS  AUTO_TASK_CON                     RUNS                 System maintenance
FINANCE_JOBS          FINANCE_GROUP                     RUNS    
MY_JOBCLASS1          MY_RES_GROUP1                     RUNS                 My first job class
MY_CLASS1                              my_service1      RUNS                 My second job class
5 rows selected.

Note that job classes are created in the SYS schema.


See Also:

PL/SQL Packages and Types Reference for CREATE_JOB_CLASS syntax, "Creating Job Classes" for further information on job classes, and "Examples of Creating Job Classes" for more examples of creating job classes


Task 2B: Creating Windows

To create windows, use the CREATE_WINDOW procedure. The following statement illustrates an example of creating a window:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
   window_name     =>  'my_window1',
   resource_plan   =>  'my_resourceplan1',
   start_date      =>  '15-APR-03 01.00.00 AM Europe/Lisbon',
   repeat_interval =>  'FREQ=DAILY',
   end_date        =>  '15-SEP-04 01.00.00 AM Europe/Lisbon',
   duration        =>  interval '50' minute,
   window_priority =>  'HIGH',
   comments        =>  'This is my first window.');
END;
/

This statement creates a window called my_window1 with attributes such as a resource plan of my_resourceplan1. To verify the window contents, query the view DBA_SCHEDULER_WINDOWS. As an example, issue the following statement:

SELECT WINDOW_NAME, RESOURCE_PLAN, DURATION, REPEAT_INTERVAL
FROM DBA_SCHEDULER_WINDOWS;

WINDOW_NAME    RESOURCE_PLAN     DURATION         REPEAT_INTERVAL
-----------    -------------     -------------    ---------------
MY_WINDOW1     MY_RESOURCEPLAN1  +000 00:50:00    FREQ=DAILY


See Also:

PL/SQL Packages and Types Reference for CREATE_WINDOW syntax, "Creating Windows" for further information on windows, and "Examples of Creating Windows" for more examples of creating job classes


Task 2C: Creating Resource Plans

To create resource plans, use the CREATE_SIMPLE_PLAN procedure. This procedure enables you to create consumer groups and allocate resources to them by executing a single statement. If you do not create a resource plan, the Scheduler uses a default resource plan called INTERNAL_PLAN.

The following statement illustrates an example of using this procedure to create a resource plan called my_simple_plan1:

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (
   simple_plan       => 'my_simple_plan1',
   consumer_group1   => 'my_group1',
   group1_cpu        => 80,
   consumer_group2   => 'my_group2',
   group2_cpu        => 20);
END;
/

This statement creates a resource plan called my_simple_plan1. To verify the resource plan contents, query the view DBA_RSRC_PLANS. An example is the following statement:

SELECT PLAN, STATUS FROM DBA_RSRC_PLANS;

PLAN                           STATUS
------------------------------ --------------------------
SYSTEM_PLAN                    ACTIVE
INTERNAL_QUIESCE               ACTIVE
INTERNAL_PLAN                  ACTIVE
MY_SIMPLE_PLAN1                ACTIVE


See Also:

"Allocating Resources Among Jobs" for further information on resource plans


Task 2D: Creating Window Groups

To create window groups, use the CREATE_WINDOW_GROUP and ADD_WINDOW_GROUP_MEMBER procedures. The following statements illustrate an example of using these procedures:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP (
   group_name        =>  'my_window_group1',
   comments          =>  'This is my first window group.');

DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name        =>  'my_window_group1',
   window_list       =>  'my_window1, my_window2');

DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name        =>  'my_window_group1',
   window_list       =>  'my_window3');
END;
/

These statements assume that you have already created my_window2 and my_window3. You can do this with the CREATE_WINDOW procedure.

These statements create a window group called my_window_group1 and then add my_window1, my_window2, and my_window3 to it. To verify the window group contents, issue the following statements:

SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;

WINDOW_GROUP_NAME   ENABLED  NUMBER_OF_WINDOWS   COMMENTS
-----------------   -------  -----------------   --------------------
MY_WINDOW_GROUP1    TRUE                     3   This is my first window group.

SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;

WINDOW_GROUP_NAME              WINDOW_NAME
------------------------------ ---------------
MY_WINDOW_GROUP1               MY_WINDOW1
MY_WINDOW_GROUP1               MY_WINDOW2
MY_WINDOW_GROUP1               MY_WINDOW3


See Also:

PL/SQL Packages and Types Reference for CREATE_WINDOW_GROUP syntax, "Administering Window Groups" for further information on window groups, and "Example of Creating Window Groups" for more detailed examples of creating window groups


Task 2E: Setting Scheduler Attributes

There are several Scheduler attributes that control the behavior of the Scheduler. They are default_timezone, log_history, and max_job_slave_processes. It is crucial that you set the default_timezone attribute because it impacts the behavior of repeating jobs and windows. The other two have defaults, but you may want to change the default settings. The values of these attributes can be set by using the SET_SCHEDULER_ATTRIBUTE procedure. Setting these attributes requires the MANAGE SCHEDULER privilege. Attributes that can be set are:

Monitoring and Managing the Scheduler

The following sections discuss how to monitor and manage the Scheduler:

How to View Scheduler Information

You can check Scheduler information by using many views. An example is the following, which shows information for completed instances of my_job1:

SELECT JOB_NAME, STATUS, ERROR#
FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'MY_JOB1';

JOB_NAME     STATUS           ERROR#
--------     --------------   ------
MY_JOB1      FAILURE           20000

Table 28-1 contains views associated with the Scheduler. The *_SCHEDULER_JOBS, *_SCHEDULER_SCHEDULES, *_SCHEDULER_PROGRAMS, *_SCHEDULER_RUNNING_JOBS, *_SCHEDULER_JOB_LOG, *_SCHEDULER_JOB_RUN_DETAILS views are particularly useful for managing jobs. See Oracle Database Reference for details regarding Scheduler views.

Table 28-1 Scheduler Views

View Description
*_SCHEDULER_SCHEDULES
These views show all schedules.
*_SCHEDULER_PROGRAMS
These views show all programs.
*_SCHEDULER_PROGRAM_ARGUMENTS
These views show all arguments registered with all programs as well as the default values if they exist.
*_SCHEDULER_JOBS
These views show all jobs, enabled as well as disabled.
*_SCHEDULER_GLOBAL_ATTRIBUTE
These views show the current values of Scheduler attributes.
*_SCHEDULER_JOB_ARGUMENTS
These views show all arguments for all jobs, assigned and unassigned.
*_SCHEDULER_JOB_CLASSES
These views show all job classes.
*_SCHEDULER_WINDOWS
These views show all windows.
*_SCHEDULER_JOB_RUN_DETAILS
These views show all completed (failed or successful) job runs.
*_SCHEDULER_WINDOW_GROUPS
These views show all window groups.
*_SCHEDULER_WINGROUP_MEMBERS
These views show the members of all window groups, one row for each group member.
*_SCHEDULER_RUNNING_JOBS
These views show state information on all jobs that are currently being run.

How to View the Currently Active Window and Resource Plan

You can view the currently active window and the plan associated with it by issuing the following statement:

SELECT WINDOW_NAME, RESOURCE_PLAN FROM DBA_SCHEDULER_WINDOWS
WHERE ACTIVE='TRUE';

WINDOW_NAME                    RESOURCE_PLAN
------------------------------ --------------------------
MY_WINDOW10                    MY_RESOURCEPLAN1

If there is no window active, you can view the active resource plan by issuing the following statement:

SELECT * FROM V$RSRC_PLAN;

How to View Scheduler Privileges

You must have the MANAGE SCHEDULER privilege to administer the Scheduler. Typically, database administrators have this privilege with the ADMIN option as part of the DBA (or equivalent) role. You can check your current system privileges by issuing the following statement:

SELECT * FROM SESSION_PRIVS;

If you do not have sufficient privileges, see "Task 1: Setting Scheduler Privileges", "How to Manage Scheduler Privileges", and Oracle Database Security Guide.

How to Find Information About Currently Running Jobs

You can check job state by issuing the following statement:

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'MY_EMP_JOB1';

JOB_NAME                       STATE
------------------------------ ---------
MY_EMP_JOB1                    DISABLED

In this case, you could enable the job using the ENABLE procedure. Table 28-2 shows the valid values for job state.

Table 28-2 Job States

Job State Description
disabled The job is disabled.
scheduled The job is scheduled to be executed.
running The job is currently running.
completed The job has completed, and is not scheduled to run again.
broken The job is broken.
failed The job was scheduled to run once and failed.
retry scheduled The job has failed at least once and a retry has been scheduled to be executed.
succeeded The job was scheduled to run once and completed successfully.

You can check whether the progress of currently running jobs by issuing the following statement:

SELECT * FROM DBA_SCHEDULER_RUNNING_JOBS;

Note that, for the column CPU_USED, the initialization parameter RESOURCE_LIMIT must be set to true.

You can check whether the job coordinator is running by searching for a process of the form cjqNNN.


See Also:

Oracle Database Reference for details regarding the *_SCHEDULER_RUNNING_JOBS and DBA_SCHEDULER_JOBS views

How the Job Coordinator Works

The job coordinator background process is automatically started and stopped on an as-needed basis. By default, the coordinator will not be up and running, but the database does monitor whether there are any jobs to be executed, or windows to be opened in the near future. If so it will start the coordinator.

As long as there are jobs or windows running, the coordinator continues to be up. Once there has been a certain period of Scheduler inactivity and there are no jobs or windows scheduled in the near future, the coordinator will automatically be stopped.

Job Coordinator and Real Application Clusters

Each RAC instance has its own job coordinator. The database monitoring checks that determine whether or not to start the job coordinator do take the service affinity of jobs into account. For example, if there is only one job scheduled in the near future and the job class to which this job belongs has service affinity for only two out of the four RAC instances, only the job coordinators for those two instances will be started.

Using DBMS_SCHEDULER and DBMS_JOB at the Same Time

Even though Oracle recommends you switch from DBMS_JOB to DBMS_SCHEDULER, DBMS_JOB is still supported for backward compatibility. Both Scheduler packages share the same job coordinator, but DBMS_JOB does not have the auto start and stop functionality. Instead, the job coordinator is controlled by the JOB_QUEUE_PROCESSES initialization parameter. When JOB_QUEUE_PROCESSES is set to 0, the coordinator is turned off and when it has a non-zero value it is turned on.The JOB_QUEUE_PROCESSES initialization parameter is only used for DBMS_JOB. When this parameter is set to a non-zero value, auto start and stop no longer apply because the coordinator will always be up and running. In this case, the coordinator will take care of execution of both DBMS_SCHEDULER and DBMS_JOB jobs.

If the initialization parameter is set to 0, or if it is not set at all, no DBMS_JOB jobs will be run, however, the auto start and stop feature will be used for all DBMS_SCHEDULER jobs and windows. If there is a DBMS_SCHEDULER job to be executed, the coordinator will be started and the job will be executed. However, DBMS_JOB jobs still will not be run.

Scheduler Attribute max_job_slave_processes

The initialization parameter JOB_QUEUE_PROCESSES only applies to DBMS_JOB. When DBMS_SCHEDULER is used, the coordinator will automatically determine how many job slaves to start based on CPU load and the number of outstanding jobs. In special scenarios a dba can still limit the maximum number of slaves to be started by the coordinator by setting the MAX_JOB_SLAVE_PROCESSES with the DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE procedure.

How to Monitor and Manage Window and Job Logs

Logs have a new entry for each event that occurs so you can track historical information. This is different from a queue, where you only want to track the latest status for an item. There are logs for jobs, job runs, and windows.

Job activity is logged in the *_SCHEDULER_JOB_LOG views. Altering a job is logged with a status of UPDATE. Dropping a job is logged in these views with a status of DROP.

Job Logs

A job log has an entry for each time you create or drop a job. To see the contents of the job log, query the DBA_SCHEDULER_JOB_LOG view. An example is the following statement, which shows what happened for past job runs:

SELECT JOB_NAME, OPERATION, OWNER FROM DBA_SCHEDULER_JOB_LOG;

JOB_NAME        OPERATION     OWNER
--------        ---------     -----
MY_JOB13        CREATE        SYS
MY_JOB14        CREATE        OE
MY_NEW_JOB3     CREATE        SYS
MY_JOB1         CREATE        SYS
MY_TEST_JOB1    CREATE        SYS
MY_TEST_JOB2    CREATE        SYS
MY_TEST_JOB2    CREATE        OE
MY_JOB11        CREATE        OE
MY_TEST_JOB4    CREATE        OE
MY_TEST_JOB5    CREATE        OE
MY_JOB12        CREATE        OE
MY_NEW_JOB3     ENABLE        SYS
MY_EMP_JOB1     UPDATE        SYS
MY_JOB1         CREATE        SCOTT
MY_EMP_JOB1     UPDATE        SYS
MY_EMP_JOB      CREATE        SYS
MY_EMP_JOB1     CREATE        SYS
MY_JOB14        RUN           OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        RETRY_RUN     OE
MY_JOB14        BROKEN        OE
MY_NEW_JOB1     CREATE        SYS
MY_JOB14        DROP          OE
MY_NEW_JOB2     CREATE        SYS

Job Run Details

To further analyze each job run, for example, why it failed, or what the actual start time was, or how long the job ran, query the DBA_SCHEDULER_JOB_RUN_DETAILS view. As an example, the following statement illustrates the status for my_job14:

SELECT JOB_NAME, STATUS FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE JOB_NAME = 'MY_JOB14';

JOB_NAME                       STATUS
------------------------------ ------------------------------
MY_JOB14                       FAILURE
MY_JOB14                       FAILURE
MY_JOB14                       FAILURE
MY_JOB14                       FAILURE
MY_JOB14                       FAILURE

For every row in SCHEDULER_JOB_LOG that is of operation RUN or RETRY_RUN, there will be a corresponding row in *_JOB_RUN_DETAILS view with the same LOG_ID. LOG_DATE contains the timestamp of the entry, so sorting by LOG_DATE should give you a chronological picture of the life of a job.

Controlling Job Logging

You can control the amount of logging the Scheduler performs on jobs at either a class or job level. Normally, you will want to control jobs at a class level as this offers a full audit trail. To do this, use the logging_level attribute in the CREATE_JOB_CLASS procedure.

For each new class, the creator of the class must specify what the logging level is for all jobs in that class. The three possible options are:

  • DBMS_SCHEDULER.LOGGING_OFF

    No logging will be performed for any jobs in this class.

  • DBMS_SCHEDULER.LOGGING_RUNS

    The Scheduler will write detailed information to the job log for all runs of each job in this class.

  • DBMS_SCHEDULER.LOGGING_FULL

    In addition to recording every run of a job, the Scheduler will record all operations performed on all jobs in this class. In other words, every time a job is created, enabled, disabled, altered, and so on will be recorded in the log.

By default, only job runs are recorded. For job classes that have very short and highly frequent jobs, the overhead of recording every single run might be too much and you might choose to turn the logging off. You might, however, prefer to have a complete audit trail of everything that happened to the jobs in a specific class, in which case you need to turn on full logging for that class.

The second way of controlling the logging level is on an individual job basis. You should keep in mind, however, that the log in many cases is used as an audit trail, thus if you want a certain level of logging, the individual job creator must not be able to turn logging off. The class-specific level is, therefore, the minimum level at which job information will be logged. A job creator can only turn on more logging for an individual job, not less.

This functionality is provided for debugging purposes. For example, if the class-specific level is set to record job runs and the job-specific logging is turned off, the Scheduler will still log the runs. If, on the other hand, the job creator turns on full logging and the class-specific level is set to record runs only, all operations on this individual job will be logged. This way, an end user can test his job by turning on full logging.

To set the logging level of an individual job, you must use the SET_ATTRIBUTE procedure on that job. For example, to turn on full logging for a job called mytestjob, issue the following statement:

DBMS_SCHEDULER.SET_ATTRIBUTE (
   'mytestjob', 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);


See Also:

PL/SQL Packages and Types Reference for detailed information about the CREATE_JOB_CLASS and SET_ATTRIBUTE procedures and "Task 2E: Setting Scheduler Attributes"

Window Logs

A window log has an entry for each time you do the following:

  • create a window

  • drop a window

  • open a window

  • close a window

  • overlap windows

  • disable a window

  • enable a window

There are no logging levels for window logging, but every window operation will automatically be logged by the Scheduler.

For every row in a window log that is of a close operation, there will be a corresponding row in the WINDOW_DETAILS view, with the same log_id. To see the contents of the window log, query the DBA_SCHEDULER_WINDOW_LOG view. As an example, issue the following statement:

SELECT WINDOW_NAME, USER_NAME FROM DBA_SCHEDULER_WINDOW_LOG;

WINDOW_NAME                    USER_NAME
------------------------------ ------------------------------
MY_WINDOW10                    SYS
MY_WINDOW100                   SYS
MY_WINDOW100                   SYS
MY_WINDOW10                    SYS
MY_WINDOW100                   SYS
MY_WINDOW100                   SYS
MY_WINDOW1                     SYS
MY_WINDOW2                     SYS
MY_WINDOW3                     SYS
MY_WINDOW10                    SYS
MY_WINDOW100                   SYS

There is also a window details view that gives information about every window that was active and completed.

Purging Logs

To prevent job and window logs from growing indiscriminately, use the SET_SCHEDULER_ATTRIBUTE attribute to specify how much history (in days) to keep. The Scheduler automatically purges all log entries from both the job log and the window log that are older than the history you specify. It does this once a day, and the default is 30 days, which can be changed by using the SET_SCHEDULER_ATTRIBUTE procedure. For example, to change the number of days to 90, issue the following statement:

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');

Some job classes are more important than others. Because of this, you can override this global history setting by using a class-specific setting. An example is if there are three classes (class1, class2, and class3) and you want to keep 10 days of history for the window log and class1 and class3, but 30 days for class2. To achieve this, issue the following statements:

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','10');
DBMS_SCHEDULER.SET_ATTRIBUTE('class2','log_history','30');

You can also set the class-specific history when creating the job class.

Besides the Scheduler automatically purging the log once a day based on the log history specified, you might also want to manually purge the log. To do this, use the PURGE_LOG procedure. As an example, the following statement purges all entries from both the job and window logs:

DBMS_SCHEDULER.PURGE_LOG();

Another example is the following, which purges all entries from the jog log that are older than three days. The window log will stay as is.

DBMS_SCHEDULER.PURGE_LOG(log_history => 3, which_log => 'JOB_LOG');

The following statement purges all window log entries older than 10 days, all job log entries relating to job1, and all jobs in class2 that are older than 10 days:

DBMS_SCHEDULER.PURGE_LOG(log_history => 10, job_name => 'job1, sys.class2');

How to Manage Scheduler Privileges

You should have the SCHEDULER_ADMIN role to administer the Scheduler. Typically, database administrators will already have this role with the ADMIN option as part of the DBA (or equivalent) role. You can grant this role to another administrator by issuing the following statement:

GRANT SCHEDULER_ADMIN TO username;

Because the SCHEDULER_ADMIN role is a powerful role allowing a grantee to execute code as any user, you should consider granting individual Scheduler system privileges instead. Both system and object privileges are granted using regular SQL grant syntax. An example is for the database administrator to issue the following statement:

GRANT CREATE JOB TO scott;

After this statement is executed, scott can create jobs, schedules, or programs in his schema. Another example is to grant an object privilege, as in the following statement:

GRANT ALTER myjob1 TO scott;

After this statement is executed, scott can execute, alter, or copy myjob1. See Oracle Database SQL Reference for system and object privilege details and Oracle Database Security Guide for general information.

An alternative to the SCHEDULER_ADMIN role for administering the Scheduler is to use the MANAGE SCHEDULER privilege, which is recommended for managing resources. As an example of granting this privilege to adam, issue the following statement:

GRANT MANAGE SCHEDULER TO adam;

After this statement is executed, adam can create, alter, or drop windows, job classes, or window groups. He will also be able to set and retrieve Scheduler attributes and purge Scheduler logs.

Types of Privileges and Their Descriptions

The following privileges are important when using the Scheduler.

Table 28-3 Scheduler Privileges

Privilege Name Operations Authorized
System Privileges:
CREATE JOB This privilege enables you to create jobs, schedules, and programs in your own schema. You will always be able to alter and drop jobs, schedules and programs in your own schema, even if you do not have the CREATE JOB privilege. In this case, the job must have been created in your schema by another user with the CREATE ANY JOB privilege.
CREATE ANY JOB This privilege enables you to create, alter, and drop jobs, schedules, and programs in any schema. This privilege is very powerful and should be used with care because it allows the grantee to execute code as any other user.
EXECUTE ANY PROGRAM This privilege enables your jobs to use programs from any schema.
EXECUTE ANY CLASS This privilege enables your jobs to run under any job class.
MANAGE SCHEDULER This is the most important privilege for administering the Scheduler. It enables you to create, alter, and drop job classes, windows, and window groups. It also enables you to set and retrieve Scheduler attributes and purge Scheduler logs.
Object Privilege:
EXECUTE This privilege enables you to create a job which runs with the program or job class. It also enables you to view object attributes. It can only be granted for programs and job classes.
ALTER This privilege enables you to alter or drop the object it is granted on. Altering includes such operations as enabling, disabling, defining or dropping program arguments, setting or resetting job argument values and running a job. For programs and jobs, this privilege enables you to view object attributes. This privilege can only be granted on jobs, programs and schedules. For other types of Scheduler objects, you can grant the MANAGE SCHEDULER system privilege. This privilege can be granted for:

jobs (DROP_JOB, RUN_JOB, SET_JOB_ARGUMENT_VALUE, RESET_JOB_ARGUMENT_VALUE, SET_JOB_ANYDATA_VALUE) and (STOP_JOB without the force option)

programs (DROP_PROGRAM, DEFINE_PROGRAM_ARGUMENT, DEFINE_ANYDATA_ARGUMENT, DEFINE_METADATA_ARGUMENT, DROP_PROGRAM_ARGUMENT, GET_ATTRIBUTE, SET_ATTRIBUTE, SET_ATTRIBUTE_NULL)

schedules (DROP_SCHEDULE)

ALL This privilege authorizes operations allowed by all other object attributes possible for a given object. It can be granted on jobs, programs, schedules and job classes.
SCHEDULER_ADMIN:
All Pre-Defined Roles The SCHEDULER_ADMIN role is created with all of the preceding system privileges (with the ADMIN option). The SCHEDULER_ADMIN role is granted to dba (with the ADMIN option).

How to Drop a Job

You can remove a job from the database by issuing a DROP_JOB statement, as in the following:

BEGIN
DBMS_SCHEDULER.DROP_JOB (
   job_name    =>  'my_job1');
END;
/

See Also:

PL/SQL Packages and Types Reference for DROP_JOB procedure syntax

How to Drop a Running Job

You can delete a running job by issuing the DROP_JOB procedure with the force option. For example, the following statement forces the deletion of my_job1:

BEGIN
DBMS_SCHEDULER.DROP_JOB (
   job_name   =>  'my_job1',
   force      =>  TRUE);
END;
/

Note that this statement will fail if my_job1 is running and you do not use the force option.

If the force option is specified, it will try to stop the job by using an interrupt mechanism. (which would be equivalent to calling STOP_JOB without force first). Alternatively, you can call STOP_JOB to first stop the job and then call DROP_JOB to drop it. If you have the MANAGE SCHEDULER privilege, you can call STOP_JOB with force, if the regular STOP_JOB call failed to stop the job, and then call DROP_JOB.

Why Does a Job Not Run?

A job may fail to run for several reasons. First, you should check that the job is not running by issuing the following statement:

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS;

Typical output will resemble the following:

JOB_NAME                       STATE
------------------------------ ---------
MY_EMP_JOB                     DISABLED
MY_EMP_JOB1                    DISABLED
MY_NEW_JOB1                    DISABLED
MY_NEW_JOB2                    DISABLED
MY_NEW_JOB3                    DISABLED

There are four types of jobs that are not running:

Failed Jobs

If a job has the status of failed in the job table, it was scheduled to run once but the execution has failed. If the job was specified as restartable, all retries have failed.

If a job fails in the middle of execution, only the last transaction of that job is rolled back. If your job executes multiple transactions, you need to be careful about setting restartable to TRUE. You can query failed jobs by querying the *_SCHEDULER_JOB_RUN_DETAILS views.

Broken Jobs

A broken job is one that has exceeded a certain number of failures. This number is set in max_failures, and can be altered. In the case of a broken job, the entire job is broken, and it will not be run until it has been fixed. For debugging and testing, you can use the RUN_JOB procedure.

You can query broken jobs by querying the *_SCHEDULER_JOBS and *_SCHEDULER_JOB_LOG views.

Disabled Jobs

A job can become disabled for the following reasons:

  • The job was manually disabled

  • The job class it belongs to was dropped

  • The program or schedule that it points to was dropped

  • A window or window group is its schedule and it is dropped

Completed Jobs

A job will be completed if end_date or max_runs is reached.

How to Change Job Priorities

You can change job priorities by using the SET_ATTRIBUTE procedure. Job priorities must be in the range 1-5 with 1 being the highest priority. For example, the following statement changes the job priority for my_job1 to a setting of 1:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_emp_job1',
   attribute      =>   'job_priority',
   value          =>   1);
END;
/

You can verify that the attribute was changed by issuing the following statement:

SELECT JOB_NAME, JOB_PRIORITY FROM DBA_SCHEDULER_JOBS;

JOB_NAME                       JOB_PRIORITY
------------------------------ ------------
MY_EMP_JOB                                3
MY_EMP_JOB1                               1
MY_NEW_JOB1                               3
MY_NEW_JOB2                               3
MY_NEW_JOB3                               3


See Also:

PL/SQL Packages and Types Reference for detailed information about the SET_ATTRIBUTE procedure

How the Scheduler Guarantees Availability

You do not need to perform any special operations for the Scheduler in the event of a system or slave process failure.

How to Handle Scheduler Security

You should grant the CREATE JOB system privilege to regular users who need to be able to use the Scheduler to schedule and run jobs. You should grant MANAGE SCHEDULER to any database administrator who needs to be able to manage system resources. Granting any other Scheduler system privilege or role should not be done without great caution. In particular, the CREATE ANY JOB system privilege and the SCHEDULER_ADMIN role, which includes it, are very powerful because they allow execution of code as any user. They should only be granted to very powerful roles or users.

A particularly important issue from a security point of view is handling external jobs. See "Running External Jobs" for further information. Security for the Scheduler has no other special requirements. See Oracle Database Security Guide for details regarding security.

How to Manage the Scheduler in a RAC Environment

There are no special requirements for using the Scheduler in a RAC environment.

Import/Export and the Scheduler

You must use the Data Pump utilities (impdp and expdp) to export Scheduler objects. You cannot use the earlier import/export utilities with the Scheduler. Also, Scheduler objects cannot be exported while the database is in read-only mode.

An export generates the DDL that was used to create the Scheduler objects. All attributes are exported. When an import is done, all the database objects are recreated in the new database. All schedules are stored with their time zones, which are maintained in the new database. For example, schedule "Monday at 1 PM PST in a database in San Francisco" would be the same if it was exported and imported to a database in Germany.


See Also:

Oracle Database Utilities for details regarding import and export

Examples of Using the Scheduler

This section discusses the following topics:

Examples of Creating Jobs

This section contains several examples of creating jobs.

Example 28-1 Creating a Job

The following statement creates a job called my_job1 in the oe schema:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'oe.my_job1',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'DBMS_STATS.GATHER_TABLE_STATS(''oe'',''sales'');',
   start_date           => '15-JUL-03 1.00.00AM US/Pacific',
   repeat_interval      => 'FREQ=DAILY', 
   end_date             => '15-SEP-03 1.00.00AM US/Pacific',
   enabled              =>  TRUE,
   comments             => 'Gather table statistics');
END;
/

This job gathers table statistics on the sales table. It will run for the first time on July 15th and then once a day until September 15. To verify that the job was created, issue the following statement:

SELECT JOB_NAME FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_JOB1';

JOB_NAME
------------------------------
MY_JOB1

Example 28-2 Creating a Job

The following statement creates a job called my_job2 in the SYSTEM schema:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'system.my_job1',
   job_type           =>  'PLSQL_BLOCK',
   job_action         =>  'INSERT INTO sales VALUES( 7987, ''SALLY'',
      ''ANALYST'', NULL, NULL, NULL, NULL, NULL);',
   start_date         =>  '28-APR-03 07.00.00.000000 AM Europe/Warsaw',
   repeat_interval    =>  'FREQ=HOURLY;INTERVAL=2', /* every two hours */
   end_date           =>  '20-NOV-04 07.00.00.000000 AM Europe/Warsaw',
   comments           =>  'My new job');
END;
/

See Also:

PL/SQL Packages and Types Reference for detailed information about the CREATE_JOB procedure and "Creating Jobs" for further information

Examples of Creating Job Classes

This section contains several examples of creating job classes.

Example 28-3 Creating a Job Class

The following statement creates a job class:

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name              =>  'my_class1',
   service                     =>  'my_service1', 
   comments                    =>  'This is my first job class');
END;
/

This creates my_class1 in SYS. It uses a service called my_service1. To verify that the job class was created, issue the following statement:

SELECT JOB_CLASS_NAME FROM DBA_SCHEDULER_JOB_CLASSES
WHERE JOB_CLASS_NAME = 'MY_CLASS1';

JOB_CLASS_NAME
------------------------------
MY_CLASS1

Example 28-4 Creating a Job Class

The following statement creates a job class:

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name             =>  'finance_jobs', 
   resource_consumer_group    =>  'finance_group',
   comments                   =>  'My financial class');
END;
/

This creates finance_jobs in SYS. It uses a resource consumer group called finance_group.


See Also:

PL/SQL Packages and Types Reference for detailed information about the CREATE_JOB_CLASS procedure and "Creating Job Classes" for further information

Examples of Creating Programs

This section contains several examples of creating programs.

Example 28-5 Creating a Program

The following statement creates a program in the oe schema:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name          => 'oe.my_program1',
   program_type          => 'PLSQL_BLOCK',
   program_action        => 'DBMS_STATS.GATHER_TABLE_STATS(''oe'',''sales'');',
   number_of_arguments   => 0,
   enabled               => TRUE,
   comments              => 'My comments here');
END;
/

This creates my_program1, which uses PL/SQL to gather table statistics on the sales table. To verify that the program was created, issue the following statement:

SELECT PROGRAM_NAME FROM DBA_SCHEDULER_PROGRAMS 
WHERE PROGRAM_NAME = 'MY_PROGRAM1';

PROGRAM_NAME
-------------------------
MY_PROGRAM1

Example 28-6 Creating a Program

The following statement creates a program in the oe schema:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name           => 'oe.my_saved_program1',
   program_action         => '/usr/local/bin/date',
   program_type           => 'EXECUTABLE',
   comments               => 'My comments here');
END;
/

This creates my_saved_program1, which uses an executable.


See Also:

PL/SQL Packages and Types Reference for detailed information about the CREATE_PROGRAM procedure and "Creating Programs" for further information

Examples of Creating Windows

This section contains several examples of creating windows.

Example 28-7 Creating a Window

The following statement creates a window called my_window1 in SYS:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
   window_name          =>  'my_window1',
   resource_plan        =>  'my_res_plan1',
   start_date           =>  '15-JUL-03 1.00.00AM US/Pacific',
   repeat_interval      =>  'FREQ=DAILY',
   end_date             =>  '15-SEP-03 1.00.00AM US/Pacific',
   duration             =>  interval '80' MINUTE,
   comments             =>  'This is my first window');
END;
/

This window will open once a day at 1AM for 80 minutes every day from May 15th to October 15th. To verify that the window was created, issue the following statement:

SELECT WINDOW_NAME FROM DBA_SCHEDULER_WINDOWS WHERE WINDOW_NAME = 'MY_WINDOW1';

WINDOW_NAME
------------------------------
MY_WINDOW1

Example 28-8 Creating a Window

The following statement creates a window called my_window2 in SYS:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW ( 
   window_name       => 'my_window2',
   schedule_name     => 'my_stats_schedule',
   resource_plan     => 'my_resourceplan1',
   duration          => interval '60' minute,
   comments          => 'My window');
END; 
/

See Also:

PL/SQL Packages and Types Reference for detailed information about the CREATE_WINDOW procedure and "Creating Windows" for further information

Example of Creating Window Groups

The following statement creates a window group called my_window_group1:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP ('my_windowgroup1');
END;
/

Then, you could add three windows (my_window1, my_window2, and my_window3) to my_window_group1 by issuing the following statements:

BEGIN
DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name   =>  'my_window_group1',
   window_list  =>  'my_window1, my_window2');

DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name   =>  'my_window_group1',
   window_list  =>  'my_window3');
END;
/

To verify that the window group was created and the windows added to it, issue the following statement:

SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;

WINDOW_GROUP_NAME    ENABLED   NUMBER_OF_WINDOWS   COMMENTS
-----------------    -------   -----------------   ---------------
MY_WINDOW_GROUP1     TRUE                      3   This is my first window group

See Also:

PL/SQL Packages and Types Reference for detailed information about the CREATE_WINDOW_GROUP and ADD_WINDOW_GROUP_MEMBER procedures and "Creating Window Groups" for further information

Examples of Setting Attributes

This section contains several examples of setting attributes.

Example 28-9 Setting the Frequency Attribute

The following example resets the frequency my_emp_job1 will run to daily:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_emp_job1',
   attribute      =>   'repeat_interval',
   value          =>   'FREQ=DAILY');
END;
/

To verify the change, issue the following statement:

SELECT JOB_NAME, REPEAT_INTERVAL FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME =  'MY_EMP_JOB1';

JOB_NAME             REPEAT_INTERVAL
----------------     ---------------
MY_EMP_JOB1          FREQ=DAILY

Example 28-10 Setting the Comments Attribute

The following example resets the comments for my_saved_program1:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_saved_program1',
   attribute      =>   'comments',
   value          =>   'For nightly table stats');
END;
/

To verify the change, issue the following statement:

SELECT PROGRAM_NAME, COMMENTS FROM DBA_SCHEDULER_PROGRAMS;

PROGRAM_NAME        COMMENTS
------------        -----------------------
MY_PROGRAM1         My comments here
MY_SAVED_PROGRAM1   For nightly table stats

Example 28-11 Setting the Duration Attribute

The following example resets the duration of my_window3 to 90 minutes:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_window3',
   attribute      =>   'duration',
   value          =>   interval '90' minute);
END;
/

To verify the change, issue the following statement:

SELECT WINDOW_NAME, DURATION FROM DBA_SCHEDULER_WINDOWS
WHERE WINDOW_NAME = 'MY_WINDOW3';

WINDOW_NAME        DURATION
-----------        ---------------
MY_WINDOW3         +000 00:90:00

See Also:

PL/SQL Packages and Types Reference for detailed information about the SET_SCHEDULER_ATTRIBUTE procedure and "Task 2E: Setting Scheduler Attributes"