Oracle® Database Administrator's Guide 10g Release 2 (10.2) Part Number B14231-02 |
|
|
View PDF |
This chapter provides step-by-step instructions for configuring, administering, and monitoring Oracle Scheduler (the Scheduler). It contains the following sections:
Examples of Using the Scheduler
Note:
This chapter discusses the use of the Oracle-suppliedDBMS_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.
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.
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
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 forCREATE_JOB_CLASS
syntax, "Creating Job Classes" for further information on job classes, and "Examples of Creating Job Classes" for more examples of creating job classesTask 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 forCREATE_WINDOW
syntax, "Creating Windows" for further information on windows, and "Examples of Creating Windows" for more examples of creating job classesTask 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 plansTask 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 forCREATE_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 groupsTask 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.
The following sections discuss how to monitor and manage the Scheduler:
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. |
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;
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.
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 |
---|---|
|
The job is disabled. |
|
The job is scheduled to be executed. |
|
The job is currently running. |
|
The job has completed, and is not scheduled to run again. |
|
The job was scheduled to run once and was stopped while it was running. |
|
The job is broken. |
|
The job was scheduled to run once and failed. |
|
The job has failed at least once and a retry has been scheduled to be executed. |
|
The job was scheduled to run once and completed successfully. |
|
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 |
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
viewsThe 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.
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.
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.
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.
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.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.
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.
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 theCREATE_JOB_CLASS
and SET_ATTRIBUTE
procedures and "Task 2E: Setting Scheduler Attributes"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.
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');
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.
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.
The following privileges are important when using the Scheduler.
Table 28-3 Scheduler Privileges
Privilege Name | Operations Authorized |
---|---|
System Privileges: |
|
|
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 |
|
This privilege enables you to create, alter, and drop jobs, chains, schedules, and programs in any schema except |
|
This privilege is required to create jobs that run outside of the database. Owners of jobs of type ' |
|
This privilege enables your jobs to use programs or chains from any schema. |
|
This privilege enables your jobs to run under any job class. |
|
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: |
|
|
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. |
|
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 jobs ( chains ( programs ( schedules ( |
|
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
.
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; /
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
.
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:
See Also:
"Job Recovery After a Failure"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.
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.
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
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
'.
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 theSET_ATTRIBUTE
procedureYou 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.
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.
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.
You do not need to perform any special operations for the Scheduler in the event of a system or slave process failure.
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.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 exportThis section discusses the following topics:
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 theCREATE_JOB
procedure and "Creating Jobs" for further informationThis 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 theCREATE_JOB_CLASS
procedure and "Creating Job Classes" for further informationThis 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 theCREATE_PROGRAM
procedure and "Creating Programs" for further informationThis 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 theCREATE_WINDOW
procedure and "Creating Windows" for further informationThis 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 theCREATE_WINDOW_GROUP
and ADD_WINDOW_GROUP_MEMBER
procedures and "Creating Window Groups" for further informationThis 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 theSET_SCHEDULER_ATTRIBUTE
procedure and "Task 2E: Setting Scheduler Attributes"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 theCREATE_CHAIN
, DEFINE_CHAIN_STEP
, and DEFINE_CHAIN_RULE
procedures and "Task 2E: Setting Scheduler Attributes"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 theCREATE_JOB
and CREATE_EVENT_SCHEDULE
procedures