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;