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 hh24: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;

Oracle事件驅動的排程作業

相關文章