Oracle Job Scheduling - Events - Part 2

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.

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:

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

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

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.