Oracle Scheduler 事件驅動調度實戰 (二)：Advanced Queuing 整合與 Shell 腳本自動化載數

Oracle Scheduler 事件驅動調度實戰 (二)：Advanced Queuing 整合與 Shell 腳本自動化載數

延續「基於時間的調度」，本文深入實作 基於事件的調度：利用 Oracle Advanced Queuing (AQ) 作為事件總線，結合 DBMS_SCHEDULER 實現「訊息到達即觸發作業」模式。涵蓋兩大完整案例：PL/SQL 區塊觸發與外部 Shell 腳本載數 (SQL*Loader)，並總結 Job 調度 Shell 的四大避坑鐵律。

一、核心架構回顧

• 事件來源：應用程式、PL/SQL、外部系統透過 DBMS_AQ.ENQUEUE 發送訊息。
• 事件隊列：AQ 隊列表 + 隊列，承載自訂物件型別 (Payload)。
• 事件消費者：Scheduler Job 定義 EVENT_CONDITION (訊息篩選) + QUEUE_SPEC (綁定隊列) + JOB_ACTION (執行邏輯)。
• 觸發機制：訊息入隊 → AQ 通知 Scheduler → 條件匹配 → Job 執行 (非同步、保證投遞)。

二、案例一：PL/SQL 區塊事件觸發 (HR Schema)

1. 建立測試表與序列

CONN hr/hr
CREATE TABLE event_job_test (id NUMBER, createdate DATE);
ALTER TABLE event_job_test ADD CONSTRAINT pk_event_job_test PRIMARY KEY (id);
CREATE SEQUENCE seq_event_job_test;

2. 定義事件 Payload 型別 (物件型別)

CREATE OR REPLACE TYPE t_event_queue AS OBJECT (
  object_owner VARCHAR2(50),
  event_name   VARCHAR2(50)
);

3. 建立 AQ 隊列表、隊列並啟動 (需 SYS 權限)

CONN / AS SYSDBA
GRANT EXECUTE ON DBMS_AQADM TO hr;
CONN hr/hr
BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    QUEUE_TABLE        => 'event_queue_tab',
    QUEUE_PAYLOAD_TYPE => 't_event_queue',
    MULTIPLE_CONSUMERS => TRUE);  -- 多消費者模式
END;
/
BEGIN
  DBMS_AQADM.CREATE_QUEUE(
    QUEUE_NAME  => 'event_queue',
    QUEUE_TABLE => 'event_queue_tab');
END;
/
BEGIN
  DBMS_AQADM.START_QUEUE(QUEUE_NAME => 'event_queue');
END;
/
-- 此時隊列已就緒，可供 ENQUEUE/DEQUEUE

4. 建立事件驅動 Job (SYS 建立，指定 HR 佇列)

CONN / AS SYSDBA
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB(
    JOB_NAME        => '"HR"."EVENT_BASE_JOB"',
    JOB_TYPE        => 'PLSQL_BLOCK',
    JOB_ACTION      => 'BEGIN INSERT INTO hr.event_job_test VALUES (seq_event_job_test.NEXTVAL, SYSDATE); COMMIT; END;',
    EVENT_CONDITION => 'tab.user_data.object_owner = ''HR'' AND tab.user_data.event_name = ''give_me_an_event''',
    QUEUE_SPEC      => 'HR.EVENT_QUEUE',
    START_DATE      => SYSTIMESTAMP AT TIME ZONE '+8:00',
    JOB_CLASS       => 'DEFAULT_JOB_CLASS',
    AUTO_DROP       => FALSE,
    ENABLED         => TRUE);
END;
/
-- EVENT_CONDITION 使用 SQL WHERE 語法篩選 Payload 屬性

5. 測試：發送訊息觸發 Job

CONN hr/hr
SELECT * FROM event_job_test;  -- 空表

-- 發送事件訊息
CONN / AS SYSDBA
GRANT EXECUTE ON DBMS_AQ TO hr;
CONN hr/hr
DECLARE
  l_enq_opt   DBMS_AQ.ENQUEUE_OPTIONS_T;
  l_msg_prop  DBMS_AQ.MESSAGE_PROPERTIES_T;
  l_msg_id    RAW(16);
  l_payload   T_EVENT_QUEUE;
BEGIN
  l_payload := T_EVENT_QUEUE('HR', 'give_me_an_event');
  DBMS_AQ.ENQUEUE(
    QUEUE_NAME         => 'event_queue',
    ENQUEUE_OPTIONS    => l_enq_opt,
    MESSAGE_PROPERTIES => l_msg_prop,
    PAYLOAD            => l_payload,
    MSGID              => l_msg_id);
  COMMIT;
END;
/
SELECT * FROM event_job_test;  -- 應出現 1 筆記錄，ID=1

6. 清理

BEGIN DBMS_SCHEDULER.DROP_JOB(JOB_NAME => '"HR"."EVENT_BASE_JOB"', FORCE => TRUE); END;

三、案例二：外部 Shell 腳本自動化載數 (SCOTT Schema + SQL*Loader)

場景：資料檔 /u01/data.txt 到達檔案系統 → 觸發 Job 執行 sqlldr 載入表 SCOTT.T。

1. 準備控制檔與 Shell 腳本

-- load.ctl
LOAD DATA
INFILE '/u01/data.txt'
BADFILE '/u01/bad.emp'
DISCARDFILE '/u01/discard.emp'
TRUNCATE
INTO TABLE t
FIELDS TERMINATED BY ',' TRAILING NULLCOLS
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

-- load.sh (需執行權限 chmod +x)
#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2/db_1
export ORACLE_SID=orcl
$ORACLE_HOME/bin/sqlldr scott/tiger control=/u01/load.ctl log=/u01/load.log

2. 產生測試資料檔

SQL> SET TRIMS ON
SQL> SPOOL /u01/data.txt
SQL> SELECT EMPNO||','||ENAME||','||JOB||','||MGR||','||HIREDATE||','||SAL||','||COMM||','||DEPTNO FROM emp;
SQL> SPOOL OFF

3. 定義含時間戳的事件型別

CONN scott/tiger
CREATE OR REPLACE TYPE t_event_queue AS OBJECT (
  object_owner    VARCHAR2(10),
  object_name     VARCHAR2(20),
  event_type      VARCHAR2(20),
  event_timestamp NUMBER(2)  -- 小時數，用於判斷是否在特定時段
);

4. 建立隊列 (同案例一，改 SCOTT)

CONN / AS SYSDBA
GRANT EXECUTE ON DBMS_AQADM TO scott;
CONN scott/tiger
BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(QUEUE_TABLE=>'event_queue_tab', QUEUE_PAYLOAD_TYPE=>'t_event_queue', MULTIPLE_CONSUMERS=>TRUE); END;/
BEGIN DBMS_AQADM.CREATE_QUEUE(QUEUE_NAME=>'event_queue', QUEUE_TABLE=>'event_queue_tab'); END;/
BEGIN DBMS_AQADM.START_QUEUE(QUEUE_NAME=>'event_queue'); END;/

5. 建立執行 Shell 的 Job (JOB_TYPE=EXECUTABLE, 必須由 SYS 建立)

CONN / AS SYSDBA
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB(
    JOB_NAME        => '"SYS"."PERFORM_DATA_LOAD"',
    JOB_TYPE        => 'EXECUTABLE',
    JOB_ACTION      => '/u01/load.sh',
    EVENT_CONDITION => 'tab.user_data.object_owner = ''SCOTT'' AND tab.user_data.object_name = ''DATA.TXT'' AND tab.user_data.event_type = ''FILE_ARRIVAL'' AND tab.user_data.event_timestamp < 9',
    QUEUE_SPEC      => 'SCOTT.EVENT_QUEUE',
    START_DATE      => SYSTIMESTAMP AT TIME ZONE '+8:00',
    JOB_CLASS       => 'DEFAULT_JOB_CLASS',
    AUTO_DROP       => FALSE,
    ENABLED         => TRUE);
END;
/
-- 邏輯：若 DATA.TXT 在上午 9 點前到達 (event_timestamp < 9)，自動觸發載入

6. 測試觸發

CONN scott/tiger
SELECT * FROM t;  -- 空表

-- 發送檔案到達事件 (模擬 8 點到達)
CONN / AS SYSDBA
GRANT EXECUTE ON DBMS_AQ TO scott;
CONN scott/tiger
DECLARE
  l_enq_opt   DBMS_AQ.ENQUEUE_OPTIONS_T;
  l_msg_prop  DBMS_AQ.MESSAGE_PROPERTIES_T;
  l_msg_id    RAW(16);
  l_payload   T_EVENT_QUEUE;
BEGIN
  l_payload := T_EVENT_QUEUE('SCOTT', 'DATA.TXT', 'FILE_ARRIVAL', 8);
  DBMS_AQ.ENQUEUE(QUEUE_NAME=>'event_queue', ENQUEUE_OPTIONS=>l_enq_opt, MESSAGE_PROPERTIES=>l_msg_prop, PAYLOAD=>l_payload, MSGID=>l_msg_id);
  COMMIT;
END;
/
SELECT * FROM t;  -- 應載入 emp 全部資料

四、Oracle Scheduler 調度 Shell 腳本四大鐵律 (避坑指南)

1. Shebang 必須：腳本首行 #!/bin/bash (或 #!/bin/sh) 指定解釋器。
2. 環境變數全內建：ORACLE_BASE、ORACLE_HOME、ORACLE_SID、PATH、LD_LIBRARY_PATH 全部在腳本內 export，不可依賴外部 profile。
3. 路徑必須絕對：控制檔、資料檔、Log 檔、SQL*Loader 執行檔全用絕對路徑。
4. Job 擁有者必須為 SYS：JOB_TYPE=EXECUTABLE 的 Job 僅允許 SYS 建立 (安全機制)，JOB_ACTION 指向腳本絕對路徑。

-- 查看預設維護窗口與客戶端
SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;

-- 關閉自動統計收集、空間建議、SQL Tuning
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto optimizer stats collection');
  DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'auto space advisor');
  DBMS_AUTO_TASK_ADMIN.DISABLE(CLIENT_NAME => 'sql tuning advisor');
END;
/
-- 開啟同理用 ENABLE