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;