Shulou(Shulou.com)06/01 Report--

The most common use of Oracle scheduling jobs is time-based scheduling, which sets specific time scheduling rules and triggers job programs to execute at specific points in time according to the rules. Most jobs are of this type. In addition, event-based scheduling jobs can also be built, that is, scheduling jobs are triggered according to specific events. Event-driven scheduling jobs are implemented using Oracle Message Queue mechanisms. The following examples show how to use them.

Create a user to test and authorize, giving permission to create jobs and manage queues

conn / as sysdba

create user u1 identified by u1;

grant connect, resource to u1;

grant create job to u1;

grant aq_administrator_role to u1;

Connect to the test user and create a table to verify the execution results

conn u1/u1

create table t1(c1 varchar2(20), input_time date);

Defines a type that records message information

create or replace type event_queue_type as object(event_name varchar2(30));

/

Create a queue table for logging messages, specifying the table name and the type name of the message

begin

dbms_aqadm.create_queue_table(queue_table => 'event_queue_table',

queue_payload_type => 'event_queue_type',

multiple_consumers => true);

end;

/

Create a message queue, specifying the queue name and queue table

begin

dbms_aqadm.create_queue(queue_name => 'event_queue',

queue_table => 'event_queue_table');

end;

/

initiation queue

begin

dbms_aqadm.start_queue(queue_name => 'event_queue');

end;

/

View queue information

col queue_table for a30

col user_comment for a30

select name, queue_table, qid, queue_type, enqueue_enabled, dequeue_enabled, user_comment from user_queues;

NAME QUEUE_TABLE QID QUEUE_TYPE ENQUEUE_ENABLED DEQUEUE_ENABLED USER_COMMENT

------------------------------ ------------------------------ ---------- -------------------- --------------- --------------- ------------------------------

AQ$_EVENT_QUEUE_TABLE_E EVENT_QUEUE_TABLE 76605 EXCEPTION_QUEUE NO NO exception queue

EVENT_QUEUE EVENT_QUEUE_TABLE 76606 NORMAL_QUEUE YES YES

In order to maintain queues, Oracle creates multiple tables for different message storage. For performance and other considerations, many of these tables adopt IOT (Index-Organized Table) structure.

select table_name, tablespace_name, iot_name, iot_type from user_tables;

TABLE_NAME TABLESPACE_NAME IOT_NAME IOT_TYPE

------------------------------ ------------------------------ ------------------------------ ------------

T1 USERS

EVENT_QUEUE_TABLE USERS

AQ$_EVENT_QUEUE_TABLE_S USERS

AQ$_EVENT_QUEUE_TABLE_L USERS

SYS_IOT_OVER_76597 USERS AQ$_EVENT_QUEUE_TABLE_G IOT_OVERFLOW

AQ$_EVENT_QUEUE_TABLE_T IOT

AQ$_EVENT_QUEUE_TABLE_H IOT

AQ$_EVENT_QUEUE_TABLE_I IOT

AQ$_EVENT_QUEUE_TABLE_G IOT

Create scheduled jobs, specify actions to execute, events to trigger jobs

begin

dbms_scheduler.create_job(job_name => 'event_based_job',

job_type => 'plsql_block',

job_action => 'insert into t1 values(''test'', sysdate);commit;',

start_date => systimestamp,

event_condition => 'tab.user_data.event_name = ''test_event''',

queue_spec => 'event_queue',

enabled => true);

end;

/

Execute the following PL/SQL block to deliver messages to the queue

declare

my_enqueue_options dbms_aq.enqueue_options_t;

my_message_properties dbms_aq.message_properties_t;

my_message_handle raw(16);

my_queue_msg event_queue_type;

begin

my_queue_msg := event_queue_type('test_event');

dbms_aq.enqueue(queue_name => 'event_queue',

enqueue_options => my_enqueue_options,

message_properties => my_message_properties,

payload => my_queue_msg,

msgid => my_message_handle);

commit;

end;

/

Verify the scheduling job execution results, test that data has been inserted into the table

select * from t1;

C1 INPUT_TIME

-------------------- -------------------

test 2018-02-07 13:12:00

Query Scheduling Job Execution History

conn / as sysdba

col owner for a10

col job_name for a20

col status for a10

col run_duration for a20

select *

from (select owner,

job_name,

status,

to_char(actual_start_date, 'yyyy-mm-dd hh34:mi:ss') actual_start_date,

run_duration

from dba_scheduler_job_run_details

where job_name = 'EVENT_BASED_JOB'

order by actual_start_date desc)

where rownum < 10;

OWNER JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION

---------- -------------------- ---------- ------------------- --------------------

U1 EVENT_BASED_JOB SUCCEEDED 2018-02-07 13:12:00 +000 00:00:00

Experiment complete Delete test user and all objects

conn / as sysdba

drop user u1 cascade;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.