Oracle事件驱动的调度作业
Oracle调度作业最常见的用法是基于时间的调度，设置特定的时间调度规则，依据规则在特定的时间点触发作业程序执行，大部分的作业都是这种类型。除此之外，还可以搭建基于事件的调度作业，就是依据特定的事件来触发调度作业的执行。基于事件驱动的调度作业是运用Oracle消息队列机制来实现的，以下例子给出具体的用法。
创建一个用来测试的用户并授权，要给予创建作业和管理队列的权限
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;
连接到测试用户，创建一个用来验证执行结果的表
conn u1/u1
create table t1(c1 varchar2(20), input_time date);
定义一个记录消息信息的类型
create or replace type event_queue_type as object(event_name varchar2(30));
/
创建队列表用于记录消息，指定表名和消息的类型名
begin
dbms_aqadm.create_queue_table(queue_table => 'event_queue_table',
queue_payload_type => 'event_queue_type',
multiple_consumers => true);
end;
/
创建消息队列，指定队列名和队列表
begin
dbms_aqadm.create_queue(queue_name => 'event_queue',
queue_table => 'event_queue_table');
end;
/
启动队列
begin
dbms_aqadm.start_queue(queue_name => 'event_queue');
end;
/
查看队列信息
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
为了维护队列，Oracle会创建多个表用于不同的消息存储，处于性能等多方面的考量，很多这样的表采用IOT（Index-Organized Table）结构
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
创建调度作业，指定执行的动作、触发作业的事件
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;
/
执行以下PL/SQL块，向队列中传递消息
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;
/
验证调度作业执行结果，测试表中已经插入了数据
select * from t1;
C1 INPUT_TIME
-------------------- -------------------
test 2018-02-07 13:12:00
查询调度作业执行历史
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
实验完毕删除测试用户及其所有对象
conn / as sysdba
drop user u1 cascade;
