Home

Oracle Job Scheduling - Events - Part 2

Author: Packt Publishing Published on: 3rd Nov 2009

In other third-party scheduling packages, I have seen these notification actions implemented as part of the chain definitions because they lack a Scheduler event queue. In such packages, messages are sent by mail in extra chain steps. In the Oracle Scheduler, this queue is present and is very useful for us.

Ads

Compared to 10g, nothing has changed in 11g. An event monitoring package can de-queue from the SCHEDULER$_EVENT_QUEUE variable into a sys.scheduler$_event_info type variable. The definition is shown in the following screenshot:

What you can do with an event handler is up to your imagination. The following DB Console screenshot shows the interface that can be used to specify which events to raise:

It is easy to generate an event for every possible event listed above and have the handler decide (by the rules defined in tables) what to do. This does sound a little creepy, but it is not very different from having a table that can be used as a lookup for the job found in the event message where—most of the time—a notification mail is sent, or not sent. Sometimes, a user wants to get a message when a job starts running; and most of the time, they want a message when a job ends.

In a chain, it is especially important to be able to tell in which step the event happened and what that step was supposed to do. In the event message, only the job name is present and so you have to search a bit to find the name of the step that failed.

For this, we can use the LOG_ID to find the step name in the SCHEDULER_JOB_LOGS (user/dba/all_SCHEDULER_JOB_LOG) view, where the step name is listed as JOB_SUBNAME. The following query can be used to find the step_name from the dba all_scheduler_log view, assuming that the event message is received in msg:

Sample Code

SELECT job_subname FROM all_scheduler_job_log WHERE
log_id = msg.log_id;

To enable the delivery of all the events a job can generate, we can set the raise_events attribute to a value of:

Sample Code

dbms_scheduler.job_started + dbms_scheduler.job_succeeded +
dbms_scheduler.job_failed + dbms_scheduler.job_broken +
dbms_scheduler.job_completed + dbms_scheduler.job_stopped +
dbms_scheduler.job_sch_lim_reached + dbms_scheduler.job_disabled + dbms_scheduler.job_chain_stalled

Or in short, we can set it to: dbms_scheduler.job_all_events.

Ads

There are many things that can be called events. In the job system, there are basically two types of events: events caused by jobs (which we already discussed) and events that makes a job execute.

This tutorial is part of a Oracle 11g Scheduler tutorial series. Read it from the beginning and learn yourself.

Oracle 11g Scheduler