28 Administering the Scheduler

This chapter provides step-by-step instructions for configuring, administering, and monitoring Oracle Scheduler (the Scheduler). It contains the following sections:

Configuring the Scheduler
Monitoring and Managing the Scheduler
Import/Export and the Scheduler
Examples of Using the Scheduler

Note:
This chapter discusses the use of the Oracle-supplied DBMS_SCHEDULER package to administer scheduling capabilities. You can also use Oracle Enterprise Manager (EM) as an easy-to-use graphical interface for many of the same capabilities.
See the Oracle Database PL/SQL Packages and Types Reference for DBMS_SCHEDULER syntax and the Oracle Enterprise Manager documentation set for more information regarding EM.

Configuring the Scheduler

The following tasks are necessary when configuring the Scheduler:

Task 1: Setting Scheduler Privileges
Task 2: Configuring the Scheduler Environment

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.

Setting Chain Privileges

Scheduler chains use underlying Oracle Streams Rules Engine objects along with their associated privileges. To create a chain in his own schema, a user must have the CREATE JOB privilege in addition to the Rules Engine privileges required to create rules, rule sets, and evaluation contexts in his own schema. These can be granted by issuing the following statement:

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_RULE_OBJ, 'username'),
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
   DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 'username'),
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
   DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ, 'username')
END;
/

To create a chain in a different schema, a user must have the CREATE ANY JOB privilege in addition to the privileges required to create rules, rule sets, and evaluation contexts in schemas other than his own. These can be granted by issuing the following statement:

BEGIN
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(DBMS_RULE_ADM.CREATE_ANY_RULE, 'username'),
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
   DBMS_RULE_ADM.CREATE_ANY_RULE_SET, 'username'),
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
   DBMS_RULE_ADM.CREATE_ANY_EVALUATION_CONTEXT, 'username')
END;
/

Altering or dropping chains in schemas other than the user's schema will require corresponding system Rules Engine privileges for rules, rule sets, and evaluation contexts. See the usage notes for DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE for more information on Streams Rules Engine privileges.

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
Task 2B: Creating Windows
Task 2C: Creating Resource Plans
Task 2D: Creating Window Groups
Task 2E: Setting Scheduler Attributes

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:

Oracle Database 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;
/

To verify that the window was created properly, 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:

Oracle Database 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.

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:

Oracle Database PL/SQL Packages and Types Reference for CREATE_WINDOW_GROUP syntax, "Using 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, max_job_slave_processes, and event_expiry_time. 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:

default_timezone

Repeating jobs and windows that use the calendaring syntax need to know which time zone to use for their repeat intervals. They normally retrieve the time zone from start_date, but if no start_date is provided (which is not uncommon), they retrieve the time zone from the default_timezone Scheduler attribute.

Scheduler derives the value of default_timezone from the operating system environment. If Scheduler can find no compatible value from the operating system, it sets default_timezone to NULL.

It is crucial that you verify that default_timezone is set properly, and if not, that you set it. To verify it, run this query:
```
SQL> select dbms_scheduler.stime from dual;
 
STIME
---------------------------------------------------------------------------
14-OCT-04 02.56.03.206273000 PM US/PACIFIC
```
To ensure that daylight savings adjustments are followed, it is strongly recommended that you set default_timezone to a region name instead of an absolute time zone offset. For example, if your database resides in Miami, Florida, USA, issue the following statement:
```
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','US/Eastern');
```
To see a list of valid region names, run this query:
```
SELECT DISTINCT TZNAME FROM V$TIMEZONE_NAMES;
```
If you do not properly set default_timezone, the default time zone for repeating jobs and windows will be the absolute offset retrieved from SYSTIMESTAMP (the time zone of the operating system environment of the database), which means that repeating jobs and windows that do not have their start_date set will not follow daylight savings adjustments.
log_history

This enables you to control the amount of logging the Scheduler performs. To prevent the job log and the window log from growing indiscriminately, the Scheduler has an attribute that specifies how much history (in days) to keep. Once a day, the Scheduler automatically purges all log entries from both the job log as well as the window log that are older than the specified history. The default is 30 days.

You can change the default by using the SET_SCHEDULER_ATTRIBUTE procedure. For example, to change it to 90 days, issue the following statement:
```
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','90');
```
The range of valid values is 1 through 999.
max_job_slave_processes

This enables you to set a maximum number of slave processes for a particular system configuration and load. Even though the Scheduler automatically determines what the optimum number of slave processes is for a given system configuration and load, you still might want to set a fixed limit on the Scheduler. If this is the case, you can set this attribute. The default value is NULL, and the valid range is 1-999.

Although the number set by max_job_slave_processes is a real maximum, it does not mean the Scheduler will start the specified number of slaves. For example, even though this attribute is set to 10, the Scheduler might still determine that is should not start more than 3 slave processes. However, if it wants to start 15, but it is set to 10, it will not start more than 10.
event_expiry_time

This enables you to set the time in seconds before an event generated by the Scheduler expires (in other words, is automatically purged from the queue).

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the SET_SCHEDULER_ATTRIBUTE procedure.

Monitoring and Managing the Scheduler

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

How to View Scheduler Information
How to View the Currently Active Window and Resource Plan
How to View Scheduler Privileges
How to Find Information About Currently Running Jobs
How the Job Coordinator Works
How to Monitor and Manage Window and Job Logs
How to Manage Scheduler Privileges
How to Drop a Job
How to Drop a Running Job
Why Does a Job Fail to Run?
Job Recovery After a Failure
How to Change Job Priorities
How to Monitor Running Chains
Why Does a Program Become Disabled?
Why Does a Window Fail to Take Effect?
How the Scheduler Guarantees Availability
How to Handle Scheduler Security
How to Manage the Scheduler in a RAC Environment

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_ARGS	These views show all arguments defined for all programs as well as the default values if they exist.
*_SCHEDULER_JOBS	These views show all jobs, enabled as well as disabled.
*_SCHEDULER_RUNNING_CHAINS	These views show all chains that are running.
*_SCHEDULER_CHAIN_STEPS	These views show all steps for all chains.
*_SCHEDULER_CHAINS	These views show all chains.
*_SCHEDULER_CHAIN_RULES	These views show all rules for all chains.
*_SCHEDULER_GLOBAL_ATTRIBUTE	These views show the current values of Scheduler attributes.
*_SCHEDULER_JOB_ARGS	These views show all set argument values for all jobs.
*_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.
*_SCHEDULER_JOB_LOG	These views show all state changes made to jobs.
*_SCHEDULER_WINDOW_LOG	These views show all state changes made to windows.
*_SCHEDULER_WINDOW_DETAILS	These views show all completed window runs.

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 a job's 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.
`stopped`	The job was scheduled to run once and was stopped while it was running.
`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.
`chain_stalled`	The job is of type chain and has no steps running, no steps scheduled to run, and no event steps waiting on an event, and the chain `evaluation_interval` is set to `NULL`. No progress will be made in the chain unless there is manual intervention.

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

SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;

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

You can find out information about a job that is part of a running chain by issuing the following statement:

SELECT * FROM ALL_SCHEDULER_RUNNING_CHAINS WHERE JOB_NAME='MY_JOB1';

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 Manag e Window and Job Logs

Logs have a new entry for each event that occurs so that 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 an operation of UPDATE. Dropping a job is logged in these views with an operation of DROP.

See Also:

Oracle Database Reference for details on the *_SCHEDULER_JOB_LOG views and other Scheduler log views.

Job Logs

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     ENABLE        SYS
MY_EMP_JOB1     UPDATE        SYS
MY_JOB1         CREATE        SCOTT
MY_EMP_JOB1     UPDATE        SYS
MY_EMP_JOB      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_JOB14        DROP          OE

When logging_level for a job is set to LOGGING_FULL, the additional_info column of the job log contains the before and after values of the modified attribute on update operations, and contains the values of all attributes on drop operations. This enables you to trace backwards from the current job state to the state of the job on previous job runs.

Job Run Details

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

select log_id, job_name, status, 
to_char(log_date, 'DD-MON-YYYY HH24:MI') log_date 
from dba_scheduler_job_run_details
where job_name = 'MY_JOB14';

    LOG_ID JOB_NAME               STATUS       LOG_DATE
---------- ---------------------- ------------ -----------------
        69 MY_JOB14               SUCCEEDED    02-JUN-2005 03:14
       124 MY_JOB14               SUCCEEDED    03-JUN-2005 03:15
       133 MY_JOB14               FAILURE      04-JUN-2005 03:00
       146 MY_JOB14               FAILURE      05-JUN-2005 03:01

For every row in SCHEDULER_JOB_LOG that is of operation RUN, RETRY_RUN, or RECOVERY_RUN, there will be a corresponding row in the *_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 that 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:

Oracle Database 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.

To see the contents of the window log, query the DBA_SCHEDULER_WINDOW_LOG view. The following statement shows sample output from this view:

SELECT LOG_ID, TO_CHAR(LOG_DATE, 'MM/DD/YYYY'), WINDOW_NAME, OPERATION
  FROM DBA_SCHEDULER_WINDOW_LOG;

    LOG_ID TO_CHAR(LO WINDOW_NAME       OPERATION
---------- ---------- ----------------- ------------------------------
         1 10/01/2004 WEEKNIGHT_WINDOW  CREATE
         2 10/01/2004 WEEKNIGHT_WINDOW  UPDATE
         3 10/01/2004 WEEKNIGHT_WINDOW  UPDATE
         4 10/01/2004 WEEKEND_WINDOW    CREATE
         5 10/01/2004 WEEKEND_WINDOW    UPDATE
         6 10/01/2004 WEEKEND_WINDOW    UPDATE
        22 10/06/2004 WEEKNIGHT_WINDOW  OPEN
        25 10/06/2004 WEEKNIGHT_WINDOW  CLOSE
        26 10/06/2004 WEEKNIGHT_WINDOW  OPEN
        29 10/06/2004 WEEKNIGHT_WINDOW  CLOSE

The DBA_SCHEDULER_WINDOWS_DETAILS view provides information about every window that was active and is now closed (completed). The following statement shows sample output from that view:

SELECT LOG_ID, WINDOW_NAME, ACTUAL_START_DATE, ACTUAL_DURATION
  FROM DBA_SCHEDULER_WINDOW_DETAILS;

    LOG_ID WINDOW_NAME      ACTUAL_START_DATE                    ACTUAL_DURATI
---------- ---------------- ------------------------------------ -------------
        25 WEEKNIGHT_WINDOW 06-OCT-04 03.12.48.832438 PM PST8PDT +000 01:02:32
        29 WEEKNIGHT_WINDOW 06-OCT-04 06.19.37.025704 PM PST8PDT +000 03:02:00

Notice that log IDs correspond in both of these views, and that in this case the rows in the DBA_SCHEDULER_WINDOWS_DETAILS view correspond to the CLOSE operations in the DBA_SCHEDULER_WINDOW_LOG view.

Purging Logs

To prevent job and window logs from growing indiscriminately, use the SET_SCHEDULER_ATTRIBUTE procedure to specify how much history (in days) to keep. Once per day, the Scheduler automatically purges all log entries that are older than the specified history period from both the job log and the window log. The default history period is 30 days. For example, to change the history period to 90 days, 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. For example, suppose that there are three job classes (class1, class2, and class3), and that you want to keep 10 days of history for the window log, 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.

Note that log entries pertaining to steps of a chain run are not purged until the entries for the main chain job are purged.

Purging Logs Manually

The PURGE_LOG procedure enables you to manually purge logs. 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 is not affected by this statement.

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

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

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

How to Manage Sched uler Privileges

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.

If users will create chains in their own schemas, they need to have the CREATE JOB privilege in addition to the Rules Engine privileges required to create rules, rule sets, and evaluation contexts in their own schemas. If users will create chains in different schemas, they must have the CREATE ANY JOB privilege in addition to the privileges required to create rules, rule sets, and evaluation contexts in schemas other than their own. See "Setting Chain Privileges" for the statements necessary to enable users to create chains and "Chain Tasks and Their Procedures" for more information regarding chain privileges.

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, chains, 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, chains, schedules, and programs in any schema except `SYS`. This privilege is very powerful and should be used with care because it allows the grantee to execute code as any other user.
`CREATE` `EXTERNAL` `JOB`	This privilege is required to create jobs that run outside of the database. Owners of jobs of type '`EXECUTABLE`' or jobs that point to programs of type '`EXECUTABLE`' require this privilege. To run a job of type '`EXECUTABLE`', you must have this privilege and the `CREATE` `JOB` privilege.
`EXECUTE` `ANY` `PROGRAM`	This privilege enables your jobs to use programs or chains 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 Privileges:
`EXECUTE`	This privilege can only be granted for programs, chains, and job classes. It enables you to create a job that runs with the program, chain, or job class. It also enables you to view object attributes.
`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, jobs, and chains, this privilege enables you to view object attributes. This privilege can only be granted on jobs, chains, 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`, `ALTER_RUNNING_CHAIN`, `SET_JOB_ARGUMENT_VALUE`, `RESET_JOB_ARGUMENT_VALUE`, `SET_JOB_ANYDATA_VALUE`) and (`STOP_JOB` without the `force` option) chains (`DROP_CHAIN`, `ALTER_CHAIN`, `DEFINE_CHAIN_RULE`, `DEFINE_CHAIN_STEP`, `DEFINE_CHAIN_EVENT_STEP`, `DROP_CHAIN_RULE`, and `DROP_CHAIN_STEP`) programs (`DROP_PROGRAM`, `DEFINE_PROGRAM_ARGUMENT`, `DEFINE_ANYDATA_ARGUMENT`, `DEFINE_METADATA_ARGUMENT`, `DROP_PROGRAM_ARGUMENT`, `SET_ATTRIBUTE_NULL`) schedules (`DROP_SCHEDULE`)
`ALL`	This privilege authorizes operations allowed by all other object privileges possible for a given object. It can be granted on jobs, programs, chains, schedules and job classes.

The SCHEDULER_ADMIN role is created with all of the system privileges shown in Table 28-3 (with the ADMIN option). The SCHEDULER_ADMIN role is granted to DBA (with the ADMIN option).

The following object privileges are granted to PUBLIC: SELECT ALL_SCHEDULER_* views, SELECT USER_SCHEDULER_* views, SELECT SYS.SCHEDULER$_JOBSUFFIX_S (for generating a job name), and EXECUTE SYS.DEFAULT_JOB_CLASS.

How to Dro p 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:

Oracle Database PL/SQL Packages and Types Reference for DROP_JOB procedure syntax

How to Dr op 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 Fail to Run?

A job may fail to run for several reasons. Before troubleshooting a job that you suspect did not run, 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                    FAILED
MY_NEW_JOB1                    DISABLED
MY_NEW_JOB2                    BROKEN
MY_NEW_JOB3                    COMPLETED

There are four types of jobs that are not running:

Failed Jobs
Broken Jobs
Disabled Jobs
Completed Jobs

See Also:

"Job Recovery After a Failure"

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, chain, or schedule that it points to was dropped
A window or window group is its schedule and the window or window group is dropped

Completed Jobs

A job will be completed if end_date or max_runs is reached. (If a job recently completed successfully but is scheduled to run again, the job state is SCHEDULED.)

Job Rec overy After a Failure

The Scheduler attempts to recover jobs that are interrupted when:

The database abnormally shuts down
A job slave process is killed or otherwise fails
For an external job, the external job process that starts the executable or script is killed or otherwise fails. (The external job process is extjob on Unix. On Windows, it is the external job service.)
For an external job, the process that runs the end-user executable or script is killed or otherwise fails.

Job recovery proceeds as follows:

The Scheduler adds an entry to the job log for the instance of the job that was running when the failure occurred. In the log entry, the OPERATION is 'RUN', the STATUS is 'STOPPED', and ADDITIONAL_INFO contains one of the following:
- REASON="Job slave process was terminated"
- REASON="ORA-01014: ORACLE shutdown in progress"
If restartable is set to TRUE for the job, the job is restarted.
If restartable is set to FALSE for the job:
- If the job is a run-once job and auto_drop is set to TRUE, the job run is done and the job is dropped.
- If the job is a run-once job and auto_drop is set to FALSE, the job is disabled and the job state is set to 'STOPPED'.
- If the job is a repeating job, the Scheduler schedules the next job run and the job state is set to 'SCHEDULED'.

When a job is restarted as a result of this recovery process, the new run is entered into the job log with the operation 'RECOVERY_RUN'.

How to Chang e 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:

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

How to M onitor Running Chains

You can check the state of a running chain by querying the *_SCHEDULER_RUNNING_CHAINS views. The results contain a row describing the current state of every step in every running instance of a chain. For example, the following statement displays the state of all steps in the running job MY_CHAIN_JOB. It also shows the state of all steps of any nested chain jobs that are running or have completed.

SELECT * FROM USER_SCHEDULER_RUNNING_CHAINS WHERE JOB_NAME = 'MY_CHAIN_JOB';

See "Using Chains" for more information regarding chains.

Why Does a Program Become Disabled?

A program can become disabled if a program argument is dropped or number_of_arguments is changed so that all arguments are no longer defined.

See "Using Programs" for more information regarding programs.

Why Does a Window Fail to Take Effect?

A window can fail to take effect for the following reasons:

A window becomes disabled when it is at the end of its schedule
A window that points to a schedule that no longer exists is disabled

See "Using Windows" for more information regarding windows.

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 Sched uler 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. Only users that need to run jobs outside of the database should be allowed to do so. You must grant the CREATE EXTERNAL JOB system privilege to those users. 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.

Note:

When upgrading from Oracle Database 10g Release 1 to 10g Release 2, CREATE EXTERNAL JOB is automatically granted to all users and roles that have the CREATE JOB privilege. Oracle recommends that you revoke this privilege from users that don't need it.

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
Examples of Creating Job Classes
Examples of Creating Programs
Examples of Creating Windows
Examples of Setting Attributes
Examples of Creating Chains
Examples of Creating Jobs and Schedules Based on Events

Examples of Creating Jobs

This section contains several examples of creating jobs. To create a job, you use the CREATE_JOB procedure.

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           => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                            ''sales''); END;',
   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

See Also:

Oracle Database 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. To create a job class, you use the CREATE_JOB_CLASS procedure.

Example 28-2 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-3 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:

Oracle Database 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. To create a program, you use the CREATE_PROGRAM procedure.

Example 28-4 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        => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                             ''sales''); END;',
   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-5 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:

Oracle Database 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. To create a window, you use the CREATE_WINDOW procedure.

Example 28-6 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-7 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:

Oracle Database 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

This section contains an example of creating a window group. To create a window group, you use the CREATE_WINDOW_GROUP procedure.

Example 28-8 Creating a Window Group

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:

Oracle Database 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. To set attributes, you use SET_ATTRIBUTE and SET_SCHEDULER_ATTRIBUTE procedures.

Example 28-9 Setting the Repeat Interval 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

Example 28-12 Setting the Event Expiration Attribute

The following example sets the time in seconds to 3600 when an event expires:

BEGIN
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (
   attribute     =>   event_expiry_time,
   value         =>   3600);
END;
/

See Also:

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

Examples of Creating Chains

This section contains examples of creating chains. To create chains, you use the CREATE_CHAIN procedure. After creating a chain, you add steps to the chain with the DEFINE_CHAIN_STEP procedure and define the rules with the DEFINE_CHAIN_RULE procedure.

Example 28-13 Creating a Chain

The following example creates a chain where my_program1 runs before my_program2 and my_program3. my_program2 and my_program3 run in parallel after my_program1 has completed.

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name            =>  'my_chain1',
   rule_set_name         =>  NULL,
   evaluation_interval   =>  NULL,
   comments              =>  NULL);
END;
/
 
--- define three steps for this chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step1', 'my_program1');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step2', 'my_program2');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step3', 'my_program3');
END;
/

--- define corresponding rules for the chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain1', 'TRUE', 'START step1');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain1', 'step1 COMPLETED', 'Start step2, step3');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain1', 'step2 COMPLETED AND step3 COMPLETED', 'END');
END;
/

Example 28-14 Creating a Chain

The following example creates a chain where first my_program1 runs. If it succeeds, my_program2 runs; otherwise, my_program3 runs.

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name            => 'my_chain2',
   rule_set_name         => NULL,
   evaluation_interval   => NULL,
   comments              => NULL);
END;
/
 
--- define three steps for this chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step1', 'my_program1');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step2', 'my_program2');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step3', 'my_program3');
END;
/
 
--- define corresponding rules for the chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('my_chain2', 'TRUE', 'START step1');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain2', 'step1 SUCCEEDED', 'Start step2');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain2', 'step1 COMPLETED AND step1 NOT SUCCEEDED', 'Start step3');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain2', 'step2 COMPLETED OR step3 COMPLETED', 'END');
END;
/

See Also:

Oracle Database PL/SQL Packages and Types Reference for detailed information about the CREATE_CHAIN, DEFINE_CHAIN_STEP, and DEFINE_CHAIN_RULE procedures and "Task 2E: Setting Scheduler Attributes"

Examples of Creating Jobs and Schedules Based on Events

This section contains examples of creating event-based jobs and event schedules. To create event-based jobs, you use the CREATE_JOB procedure. To create event-based schedules, you use the CREATE_EVENT_SCHEDULE procedure.

These examples assume the existence of an application that, when it detects the arrival of a file on a system, enqueues an event onto the queue my_events_q.

Example 28-15 Creating an Event-Based Schedule

The following example illustrates creating a schedule that can be used to start a job whenever the Scheduler receives an event indicating that a file arrived on the system before 9AM:

BEGIN
DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
   schedule_name     =>  'scott.file_arrival',
   start_date        =>  systimestamp,
   event_condition   =>  'tab.user_data.object_owner = ''SCOTT'' 
      and tab.user_data.event_name = ''FILE_ARRIVAL'' 
      and extract hour from tab.user_data.event_timestamp < 9',
   queue_spec        =>  'my_events_q');
END;
/

Example 28-16 Creating an Event-Based Job

The following example creates a job that starts when the Scheduler receives an event indicating that a file arrived on the system:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  my_job,
   program_name        =>  my_program,
   start_date          =>  '15-JUL-04 1.00.00AM US/Pacific',
   event_condition     =>  'tab.user_data.event_name = ''FILE_ARRIVAL''',
   queue_spec          =>  'my_events_q'
   enabled             =>  TRUE,
   comments            =>  'my event-based job');
END;
/

See Also:

Oracle Database PL/SQL Packages and Types Reference for detailed information about the CREATE_JOB and CREATE_EVENT_SCHEDULE procedures

28 Administering the Scheduler

Configuring the Scheduler

Monitoring and Managing the Scheduler

How to View Scheduler Information

How to View the Currently Active Window and Resource Plan

How to View Scheduler Privileges

How to Find Information About Currently Running Jobs

How the Job Coordinator Works

Job Coordinator and Real Application Clusters

Using DBMS_SCHEDULER and DBMS_JOB at the Same Time

Scheduler Attribute max_job_slave_processes

How to Monitor and Manage Window and Job Logs

Job Logs

Job Run Details

Controlling Job Logging

Window Logs

Purging Logs

How to Manage Scheduler Privileges

Types of Privileges and Their Descriptions

How to Drop a Job

How to Drop a Running Job

Why Does a Job Fail to Run?

Failed Jobs

Broken Jobs

Disabled Jobs

Completed Jobs

Job Recovery After a Failure

How to Change Job Priorities

How to Monitor Running Chains

Why Does a Program Become Disabled?

Why Does a Window Fail to Take Effect?

How the Scheduler Guarantees Availability

How to Handle Scheduler Security

How to Manage the Scheduler in a RAC Environment

Import/Export and the Scheduler

Examples of Using the Scheduler

Examples of Creating Jobs

Examples of Creating Job Classes

Examples of Creating Programs

Examples of Creating Windows

Example of Creating Window Groups

Examples of Setting Attributes

Examples of Creating Chains

Examples of Creating Jobs and Schedules Based on Events

How to Monitor and Manag e Window and Job Logs

How to Manage Sched uler Privileges

How to Dro p a Job

How to Dr op a Running Job

Job Rec overy After a Failure

How to Chang e Job Priorities

How to M onitor Running Chains

How to Handle Sched uler Security