Learn about Oracle Scheduler features 2

Iv. use of events

Event literal translation corresponds to the Chinese interpretation of the incident, but simply speaking of events is too abstract, for example to describe it. A (corresponding to an application, or a process in Oracle) suddenly Wrinkly said, "No, there is a situation ahead, this can be done." At this time, saw it seriously think, after a while on the face of a happy said: "There, as the saying goes early to ask AH late report, appear to find leadership, hurriedly to the leader to send a message bai." B (also corresponding to an application or Oracle process) received a "XX situation ahead" message, this process is called event (including a Send message and b receive message).

There are two things that trigger an event in SCHEDULER: The events triggered by SCHEDULER

Events triggered in the Scheduler generally mean that the status of the job in the current Schduler is modified, such as job startup, or the end of the run, or the running time, and so on, can throw an event, The applicate that receives the event can be properly processed based on this information.

For example, because the system is too busy, more than 30 minutes after the job startup time, the job still failed to start, so this time, scheduler can throw an event to the external application, so that external applications can promptly notify the DBA, processing. Events triggered by application

External applications can also trigger events and are scheduler to receive and process events of this type. The so-called scheduler processing event means that the scheduler initiates the corresponding job to perform the related operation, which specifically declares event handling when the job is created, so that when the event is received, such job starts.

Scheduler uses Oracle advanced queues to throw and destroy events. When the events that are triggered by Schduler are thrown, scheduler queues the message to the default event queue, and application handles events by examining the queue. When the events that application triggers are thrown, application queues the messages into the queue that handles the job.

Below we also follow these two types to introduce the events in scheduler. events Thrown by 4.1 scheduler

As I said earlier, the events thrown by scheduler are usually triggered when the job state changes, so it is not said that as long as the job status has changed, it will trigger events, which is not the case, because the job does not trigger events by default.

The job in Scheduler has a property called Raise_events, which is specifically used to set the condition that the job triggers events, which cannot be performed at create_job time, so the property is not assigned by default and does not automatically trigger an event. To set the Raise_events property, you can only modify the Raise_events property of the job through the Set_attribute procedure after the job creation completes.

For example, modify the job-created earlier, enable the Raise_events property, and execute the following statement:

Sql> BEGIN

2 Dbms_scheduler. Set_attribute (' insert_test_tbl ', ' raise_events ', Dbms_scheduler. job_all_events)

3 END;

4/pl/sql procedure successfully completed.

The property value of the Raise_events property specified in the above example Dbms_scheduler. Job_all_events, is the trigger condition that throws EVENTS.

Events are triggered by the following types, representing different operations: Job_started:job start; job_succeeded:job successful end; job_failed:job execution failed; Job_broken:job was placed broken status Job_completed:job The maximum number of runs, or the end date of the run, Job_stopped:job is placed by the stop_job process to stop execution, and the schedule of the job_sch_lim_reached:job reaches the defined value The job_disabled:job is placed in a disable state; Job_chain_stalled: Jobs running in chain are placed in chain_stalled State; Job_all_events: including all the types mentioned above; job_ Run_completed: The job ran out of error, ended successfully, or was manually stopped.

After the raise_events is activated, SCHEDULER will follow the set trigger condition, and when the trigger condition is reached, the event information will be thrown to the sys.scheduler$_event_queue queue.

For example, manually perform a insert_test_tbl once to see if the information is logged to the queue, as follows:

sql> exec dbms_scheduler.run_job (' insert_test_tbl '); Pl/sql procedure successfully completed.

Execute the following script, out team data:

Sql> set Serveroutput on

Sql> DECLARE

2 l_dequeue_options dbms_aq.dequeue_options_t;

3 L_message_properties dbms_aq.message_properties_t;

4 L_message_handle RAW (16);

5 l_queue_msg Sys.scheduler$_event_info;

6 BEGIN

7 l_dequeue_options.consumer_name: = ' TEST ';

8

9 Dbms_aq.dequeue (queue_name => ' SYS. Scheduler$_event_queue ',

Dequeue_options => L_dequeue_options,

One message_properties => l_message_properties,

Payload => l_queue_msg,

MsgId => L_message_handle);

COMMIT;

15

Dbms_output.put_line (' event_type: ' | | | l_queue_msg.event_type);

Dbms_output.put_line (' Object_owner: ' | | | l_queue_msg.object_owner);

Dbms_output.put_line (' object_name: ' | | | l_queue_msg.object_name);

Dbms_output.put_line (' Event_timestamp: ' | | | l_queue_msg.event_timestamp);

Dbms_output.put_line (' Error_code: ' | | | l_queue_msg.error_code);

Dbms_output.put_line (' event_status: ' | | | l_queue_msg.event_status);

Dbms_output.put_line (' log_id: ' | | | l_queue_msg.log_id);

Dbms_output.put_line (' Run_count: ' | | | l_queue_msg.run_count);

Dbms_output.put_line (' Failure_count: ' | | | l_queue_msg.failure_count);

Dbms_output.put_line (' Retry_count: ' | | | l_queue_msg.retry_count);

The end;

27/

event_type:job_started

Object_owner:test

Object_name:insert_test_tbl

event_timestamp:25-aug-09 12.49.29.558758 PM +08:00

error_code:0

Event_status:1

LOG_ID:

Run_count:1

failure_count:0

Retry_count:0 Pl/sql procedure successfully completed.

As you can see from the returned information, the type of event is job_started, which indicates that the job is started. In fact, Job:insert_test_tbl executes at least two event messages into the queue, one for job_started, one for job_succeeded (or possibly job_failed), not in detail here, Interested friends may wish to test themselves. Tip: The Sys.scheduler$_event_queue queue is based on the Sys.scheduler$_event_qtab queue table, so the query Sys.scheduler$_event_qtab can also get the above information.

SYS. Scheduler$_event_queue is a fixed queue, the actual application of the process, the DBA should be based on the actual situation, the table access to the relevant users, in order to smooth out the team of events in the queue information.

In addition, friendship reminder, by default SCHEDULER only keep the events of the last 24 hours information, if you want to modify this setting, you can through the Set_scheduler_attribute process, modify the SCHEDULER Event_expiry_ Time property, the property value of the Item property is in seconds. events Thrown by 4.2 application

The first thing to say is that the application here is a pronoun, which can represent an application outside of an Oracle database, or a procedure in an Oracle database, so that you understand it as a user-created object.

Scheduler can throw events for external application processing, external applications can also throw events for Scheduler to start job processing, but not any job can respond to events thrown by external applications, You must explicitly specify the events that respond when you create jobs. So how to specify it. Relies on the following two additional parameters: Queue_spec: Specifies the queue name in which the event message that is thrown by the external application is queued; Event_condition: Specifies the condition that triggers the job start, and the parameter value of the parameter should be set based on its own properties. Because the property of the message is defined by application when the event message is queued, it should also be set based on the property values when setting the trigger condition.

Next, we'll show you how to create a job that starts with event triggering, and before that, you first need to do some preparation tools, such as creating queues, because queues need to be based on a queue table, so before you create a queue, you first create a queue table, considering that the queue table relies on an object type So before you create a queue table, you have to create a type .... Complex, the specific steps are as follows, Sir can be looked at carefully:

sql> Create or replace type Jss_type 1 as Object

2 (

3 event_type VARCHAR2 (1 0),

4 Object_owner VARCHAR2 (30),

5 object_name VARCHAR2 (3 0)

6);

7/

Type created.

Sql> begin

2 Dbms_aqadm.create_queue_table (

3 queue_table => ' MY_QUEUE_TBL1 ',

4 queue_payload_type => ' Jss_type1 ',

5 multiple_consumers => true);

6 end;

7/

Pl/sql procedure successfully completed.

Sql> begin

2 Dbms_aqadm.create_queue (

3 queue_name => ' event_t1 ',

4 queue_table => ' my_queue_tbl1 ');

5 end;

6/pl/sql procedure successfully completed.

OK, the preparation is complete, and the following is to create a job that triggers the start of the event, creating the following script:

Sql> BEGIN

2 Dbms_scheduler. Create_job (

3 job_name => ' event_job_t1 ',

4 job_type => ' stored_procedure ',

5 job_action => ' P_insertintotest ',

6 event_condition => ' tab.user_data.event_type = ' Op_insert ',

7 queue_spec => ' Event_t1 ',

8 enabled => TRUE);

9 End;

10/pl/sql procedure successfully completed.

The above script is only demo, so the created job still executes the p_insertintotest process.

Think twice about not being prepared to write an external application to trigger, just to demonstrate the application trigger job startup example, so think twice about deciding how to add a message directly to the EVENT_T1 queue by Pl/sql, triggering the job start, as follows.

The first step is to perform the dbms_aqadm. Start_queue the process, place the EVENT_T1 on the Allow team and out team status (the queues created by default are not allowed to be queued and queued), as follows:

sql> exec dbms_aqadm.start_queue (queue_name => ' event_t1 ', enqueue => true,dequeue-=> true); Pl/sql procedure successfully completed.

To perform a JOIN operation:

Sql> Declare

2 V_message Jss_type1;

3 V_enqueueoptions dbms_aq.enqueue_options_t;

4 V_messageproperties dbms_aq.message_properties_t;

5 V_msg_handle Raw (16);

6 begin

7 V_message: = Jss_type1 (' op_ SELECT ', user, ' tmpobj ');

8

9 Dbms_aq.enqueue (queue_name => ' event_t1 ',

Enqueue_options => V_enqueueoptions,

One message_properties => v_messageproperties,

Payload => V_message,

MsgId => V_msg_handle);

commit;

15

End;

17/pl/sql procedure successfully completed.

Query the data in the queue table: