Oracle Event-Based Scheduling

• Jobs can be triggered based on events. An application can notify the Scheduler to start a job by enqueuing a message onto an Oracle Streams AQ queue. In other words, the job runs when the event is raised.

• There are two types of events:

o User- or application-generated events: An application can raise an    event to be consumed by the Scheduler. The Scheduler reacts to the event    by starting a job. Example of such events: a running job completes; a file    arrives on the file  system; an account within the database is locked; and    the inventory reaches a low threshold.

o Scheduler-generated events: The Scheduler can raise an event to    indicate state changes that occur within the Scheduler itself. For example,    the Scheduler can raise an event when a job starts, when a job completes,    when a job exceeds its allotted run time, and so on.

To create an event-based job, you must set these two attributes with the CREATE_JOB procedure:

o 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>, <agent_name> pair

o event_condition: A conditional expression based on message properties    that must evaluate to TRUE for the message to start the job. The    expression must use the same syntax as an Oracle Streams AQ rule    condition. You can include user data properties in the expression, provided    that the message payload is a user-defined object type, and that you prefix    object attributes in the expression with tab.user_data.

For more information about how to create queues and enqueue messages, refer to the Oracle Streams Advanced Queuing User’s Guide and Reference documentation.

First you must create the job by using the CREATE_JOB procedure and then use the SET_ATTRIBUTE procedure to modify the attribute’s default value. The Scheduler then raises the events by enqueuing messages onto the default event queue SYS.SCHEDULER$_EVENT_QUEUE.

The queue is based on the SCHEDULER$_EVENT_INFO type, which contains the following attributes:

event_type,object_owner, object_name,
event_timestamp, error_code, error_msg,
event_status, log_id, run_count and
failure_count.

The event type can be one of the following:

o JOB_START: A job has started for the first time, or a job was started on   a retry attempt. To determine which is the case, you can use the   EVENT_STATUS field for further details: 0x01 - normal start, 0x02 - retry

o JOB_SUCCEEDED

o JOB_FAILED: The job resulted in an error or was not able to run due to   process death or database shutdown. The EVENT_STATUS field   indicates the cause of failure: 0x04: Error during job execution, 0x08: Slave   crash or forced shutdown

o JOB_BROKEN: The job is marked broken after retrying unsuccessfully.

o JOB_COMPLETED: The job has a status of COMPLETED after it has   reached its maximum number of runs or its end date.

o JOB_STOPPED: The job terminated normally after a soft or hard kill was   issued. The EVENT_STATUS field indicates how the job was stopped:   0x10 - Stop without force, 0x20 - Stop with force

o JOB_OVER_MAX_DUR: The job has been running for a longer amount   of time than was specified by the job max_run_duration attribute.

o JOB_SCH_LIM_REACHED: The schedule limit for a job has been   exceeded and the job has been rescheduled.

DBMS_SCHEDULER.SET_ATTRIBUTE('hr.do_backup',
'raise_events', DBMS_SCHEDULER.JOB_FAILED)
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'ADMIN.REPORT_FAILED_BACKUP',
job_type => 'STORED_PROCEDURE',
job_action => 'ADMIN.REPORT_BACKUP_FAILURE',
start_date => SYSTIMESTAMP,
event_condition =>
'tab.user_data.object_owner = ''HR'' and
tab.user_data.object_name = ''DO_BACKUP''
and tab.user_data.event_type
='DBMS_SCHEDULER.JOB_FAILED',
queue_spec =>
'SYS.SCHEDULER$_EVENT_QUEUE,QUEUE_AGT')

DBA_SCHEDULER_JOBS - This view provides the status and general information about scheduled jobs in your database.

DBA_SCHEDULER_RUNNING_JOBS - This view provides you with information regarding currently running jobs.

DBA_SCHEDULER_JOB_RUN_DETAILS - This view provides information about status and the duration of execution for all jobs in your database.

DBA_SCHEDULER_SCHEDULES - This view provides information on all current schedules in your database.