Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
The Scheduler works with two kinds of events:
Events that the Scheduler raises to notify applications of a change in job state (for example, job complete)
Events that applications raise to notify the Scheduler to start a job
This section provides details on how to work with both kinds of events, and includes the following topics:
See Also:
"Events" for an overview of Scheduler events
"Using Chains" for information on how to use events with chains to achieve precise control over process flow
You can set up a job so that the Scheduler raises an event when the job changes state. You do so by setting the raise_events
job attribute. Because you cannot set this attribute with the CREATE_JOB
procedure, you must first create the job and then alter the job with the SET_ATTRIBUTE
procedure.
By default, until you alter a job with SET_ATTRIBUTE
, a job does not raise any state change events.
Table 27-7 summarizes the one administration task involving events raised by the Scheduler.
Table 27-7 Event Tasks and Their Procedures for Events Raised by the Scheduler
Task | Procedure | Privilege Needed |
---|---|---|
Altering a Job to Raise Events |
|
|
After you enable job state change events for a job, the Scheduler raises these events by enqueuing messages onto the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE
. This queue is a secure queue, so depending on your application, you may have to configure the queue to enable certain users to perform operations on it. See Oracle Streams Concepts and Administration for information on secure queues.
To prevent unlimited growth of the Scheduler event queue, events raised by the Scheduler expire in 24 hours by default. (Expired events are deleted from the queue.) You can change this expiry time by setting the event_expiry_time
Scheduler attribute with the SET_SCHEDULER_ATTRIBUTE
procedure. See Oracle Database PL/SQL Packages and Types Reference for more information.
To enable job state change events for a job, you use the SET_ATTRIBUTE
procedure to turn on bit flags in the raise_events
job attribute. Each bit flag represents a different job state to raise an event for. For example, turning on the least significant bit enables "job started" events to be raised. To enable multiple state change event types in one call, you add the desired bit flag values together and supply the result as an argument to SET_ATTRIBUTE
. For a list of bit flags, see the discussion of DBMS_SCHEDULER
.SET_ATTRIBUTE
in Oracle Database PL/SQL Packages and Types Reference.
The following example enables multiple state change events for job dw_reports
. It enables the following event types, both of which indicate some kind of error. (Event types are defined as constants in the package.)
job_failed
(bit flag value = 4)
job_sch_lim_reached
(bit flag value = 64)
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE('dw_reports', 'raise_events', DBMS_SCHEDULER.JOB_FAILED + DBMS_SCHEDULER.JOB_SCH_LIM_REACHED); END; /
To consume Scheduler events, your application must subscribe to the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE
. This queue is a secure queue and is owned by SYS
. To create a subscription to this queue for a user, do the following:
Log in to the database as the SYS
user or as a user with the MANAGE
ANY
QUEUE
privilege.
Subscribe to the queue using a new or existing agent.
Run the package procedure DBMS_AQADM.ENABLE_DB_ACCESS
as follows:
DBMS_AQADM.ENABLE_DB_ACCESS(agent_name, db_username);
where agent_name
references the agent that you used to subscribe to the events queue, and db_username
is the user for whom you want to create a subscription.
There is no need to grant dequeue privileges to the user. The dequeue privilege is granted on the Scheduler event queue to PUBLIC
.
As an alternative, the user can subscribe to the Scheduler event queue using the ADD_EVENT_QUEUE_SUBSCRIBER
procedure, as shown in the following example:
DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER(subscriber_name);
where subscriber_name
is the name of the Oracle Streams Advanced Queuing (AQ) agent to be used to subscribe to the Scheduler event queue. (If it is NULL
, an agent is created whose name is the user name of the calling user.) This call both creates a subscription to the Scheduler event queue and grants the user permission to dequeue using the designated agent. The subscription is rule-based. The rule permits the user to see only events raised by jobs that the user owns, and filters out all other messages. After the subscription is in place, the user can either poll for messages at regular intervals or register with AQ for notification.
See Oracle Streams Advanced Queuing User's Guide for more information.
Scheduler Event Queue
The Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE
is of type scheduler$_event_info
. The following are details on this type.
create or replace type sys.scheduler$_event_info as object ( event_type VARCHAR2(4000), object_owner VARCHAR2(4000), object_name VARCHAR2(4000), event_timestamp TIMESTAMP WITH TIME ZONE, error_code NUMBER, error_msg VARCHAR2(4000), event_status NUMBER, log_id NUMBER, run_count NUMBER, failure_count NUMBER, retry_count NUMBER, spare1 NUMBER, spare2 NUMBER, spare3 VARCHAR2(4000), spare4 VARCHAR2(4000), spare5 TIMESTAMP WITH TIME ZONE, spare6 TIMESTAMP WITH TIME ZONE, spare7 RAW(2000), spare8 RAW(2000), );
Attribute | Description |
---|---|
event_type |
One of "JOB_STARTED ", "JOB_SUCCEEDED ", "JOB_FAILED ", "JOB_BROKEN ", "JOB_COMPLETED ", "JOB_STOPPED ", "JOB_SCH_LIM_REACHED ", "JOB_DISABLED ", "JOB_CHAIN_STALLED ", "JOB_OVER_MAX_DUR ".
For descriptions of these event types, see the |
object_owner |
Owner of the job that raised the event. |
object_name |
Name of the job that raised the event. |
event_timestamp |
Time at which the event occurred. |
error_code |
Applicable only when an error is thrown during job execution. Contains the top-level error code. |
error_msg |
Applicable only when an error is thrown during job execution. Contains the entire error stack. |
event_status |
Adds further qualification to the event type. If event_type is "JOB_STARTED ," a status of 1 indicates that it is a normal start, and a status of 2 indicates that it is a retry.
If If |
log_id |
Points to the ID in the scheduler job log from which additional information can be obtained. Note that there need not always be a log entry corresponding to an event. In such cases, log_id is NULL . |
run_count |
Run count for the job when the event was raised. |
failure_count |
Failure count for the job when the event was raised. |
retry_count |
Retry count for the job when the event was raised. |
spare1 – spare8 |
Currently not implemented |
Your application can raise an event to notify the Scheduler to start a job. A job started in this way is referred to as an event-based job. The job can optionally retrieve the message content of the event.
To create an event-based job, you must set these two additional attributes:
queue_spec
A queue specification that includes the name of the queue where your application enqueues messages to raise job start events, or in the case of a secure queue, the queue name followed by a comma and the agent name.
event_condition
A conditional expression based on message properties that must evaluate to TRUE for the message to start the job. The expression must have the syntax of an Oracle Streams Advanced Queuing rule. Accordingly, you can include user data properties in the expression, provided that the message payload is an object type, and that you prefix object attributes in the expression with tab.user_data
.
For more information on rules, see the DBMS_AQADM
.ADD_SUBSCRIBER
procedure in Oracle Database PL/SQL Packages and Types Reference.
The following example sets event_condition
to select only card-swipe events that occur after midnight and before 9:00 a.m. Assume that the message payload is an object with two attributes called event_type
and event_timestamp
.
event_condition = 'tab.user_data.event_type = ''CARD_SWIPE'' and extract hour from tab.user_data.event_timestamp < 9'
You can specify queue_spec
and event_condition
as inline job attributes, or you can create an event schedule with these attributes and point to this schedule from the job.
Note:
The Scheduler runs the event-based job for each occurrence of an event that matchesevent_condition
. However, events that occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job.Table 27-8 describes common administration tasks involving events raised by an application (and consumed by the Scheduler) and the procedures associated with them.
Table 27-8 Event Tasks and Their Procedures for Events Raised by an Application
Task | Procedure | Privilege Needed |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
See Also:
Oracle Streams Advanced Queuing User's Guide for information on how to create queues and enqueue messages.You use the CREATE_JOB
procedure or Enterprise Manager to create an event-based job. The job can include event information inline as job attributes or can specify event information by pointing to an event schedule.
Like jobs based on time schedules, event-based jobs are not auto-dropped unless the job end date passes, max_runs
is reached, or the maximum number of failures (max_failures
) is reached.
To specify event information as job attributes, you use an alternate syntax of CREATE_JOB
that includes the queue_spec
and event_condition
attributes.
The following example creates a job that starts whenever someone swipes a badge to enter a data center:
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'my_job', program_name => 'my_program', event_condition => 'tab.user_data.event_type = ''CARD_SWIPE''', queue_spec => 'entry_events_q, entry_agent1', enabled => TRUE, comments => 'Start job when someone swipes a badge'); END; /
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the CREATE_JOB
procedure.
To specify event information with an event schedule, you set the job's schedule_name
attribute to the name of an event schedule, as shown in the following example:
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'my_job', program_name => 'my_program', schedule_name => 'entry_events_schedule', enabled => TRUE, comments => 'Start job when someone swipes a badge'); END; /
See "Creating an Event Schedule" for more information.
You alter an event-based job by using the SET_ATTRIBUTE
procedure. For jobs that specify the event inline, you cannot set the queue_spec
and event_condition
attributes individually with SET_ATTRIBUTE
. Instead, you must set an attribute called event_spec
, and pass an event condition and queue specification as the third and fourth arguments, respectively, to SET_ATTRIBUTE
.
The following is an example of using the event_spec
attribute:
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ('my_job', 'event_spec', 'tab.user_data.event_type = ''BAD_BADGE''', 'entry_events_q, entry_agent1'); END; /
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the SET_ATTRIBUTE
procedure.
You can create a schedule that is based on an event. You can then reuse the schedule for multiple jobs. To do so, use the CREATE_EVENT_SCHEDULE
procedure, or use Enterprise Manager. The following is an example of creating an event schedule:
BEGIN DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE ( schedule_name => 'entry_events_schedule', start_date => SYSTIMESTAMP, event_condition => 'tab.user_data.event_type = ''CARD_SWIPE''', queue_spec => 'entry_events_q, entry_agent1'); END; /
You can drop an event schedule using the DROP_SCHEDULE
procedure. See Oracle Database PL/SQL Packages and Types Reference for more information on CREATE_EVENT_SCHEDULE
.
You alter the event information in an event schedule in the same way that you alter event information in a job. For more information, see "Altering an Event-Based Job".
The following example demonstrates how to use the SET_ATTRIBUTE
procedure and the event_spec
attribute to alter event information in an event schedule.
BEGIN DBMS_SCHEDULER.SET_ATTRIBUTE ('entry_events_schedule', 'event_spec', 'tab.user_data.event_type = ''BAD_BADGE''', 'entry_events_q, entry_agent1'); END; /
See Oracle Database PL/SQL Packages and Types Reference for more information regarding the SET_ATTRIBUTE
procedure.
Through a metadata argument, the Scheduler can pass to an event-based job the message content of the event that started the job. The following rules apply:
The job must use a named program of type STORED_PROCEDURE
.
One of the named program's arguments must be a metadata argument with metadata_attribute
set to EVENT_MESSAGE
.
The stored procedure that implements the program must have an argument at the position corresponding to the named program's metadata argument. The argument type must be the data type of the queue where your application queues the job-start event.
If you use the RUN_JOB
procedure to manually run a job that has an EVENT_MESSAGE
metadata argument, the value passed to that argument is NULL
.
The following example shows how to construct an event-based job that can receive the event message content:
create or replace procedure my_stored_proc (event_msg IN event_queue_type) as begin -- retrieve and process message body -- do other work end; / begin dbms_scheduler.create_program ( program_name => 'my_prog', program_action=> 'my_stored_proc', program_type => 'STORED_PROCEDURE', number_of_arguments => 1, enabled => FALSE) ; dbms_scheduler.define_metadata_argument ( program_name => 'my_prog', argument_position => 1 , metadata_attribute => 'EVENT_MESSAGE') ; dbms_scheduler.enable ('my_prog'); exception when others then raise ; end ; / begin dbms_scheduler.create_job ( job_name => 'my_evt_job' , program_name => 'my_prog', schedule_name => 'my_evt_sch', enabled => true, auto_Drop => false) ; exception when others then raise ; end ; /