Oracle DBMS_JOB 定時排程實作全攻略：從架構概念到實戰部署

為什麼需要資料庫內建排程？

傳統上，定時任務常交由作業系統處理（如 Linux crontab、Windows 排程器）。但對於「大量資料表操作」（如證券收盤結算、每日備份、資料清洗），OS 層級排程存在三大痛點：

1. 程式複雜度高：需撰寫外部程式連線 DB、處理例外、記錄日誌
2. 權限管理困擾：OS 帳號與 DB 帳號對應易導致權限不一致錯誤
3. 效率瓶頸：網路往返與連線建立開銷大，無法發揮資料庫內部處理優勢

Oracle Job Queue（任務佇列） 正是為了解決這些問題而生。它在資料庫核心層執行，由 SNP 背景程序 驅動，具備高效能、彈性排程、失敗重試與監控等企業級特性。

核心架構：SNP 進程與初始化參數

啟用條件：解除受限會話

SNP 進程僅在「非受限模式」下啟動。以 SYSDBA 身分執行：

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

註：舊版文獻常見 svrmgrl 工具，9i 後已整合入 SQL*Plus。

關鍵初始化參數（init.ora / spfile）

修改後須重啟資料庫生效。 若使用 SPFILE，可用 ALTER SYSTEM SET job_queue_processes=10 SCOPE=BOTH; 動態調整（10g+ 支援）。

實作四步驟

步驟 1：授予執行權限

SQL> GRANT EXECUTE ON DBMS_JOB TO scott;

步驟 2：撰寫要排程的 PL/SQL 程序

建立儲存程序 my_job（範例：每日統計銷售資料）：

CREATE OR REPLACE PROCEDURE my_job AS
BEGIN
  -- 實際業務邏輯：彙總、備份、清理等
  INSERT INTO daily_sales_summary
  SELECT TRUNC(SYSDATE), COUNT(*), SUM(amount)
  FROM sales WHERE sale_date = TRUNC(SYSDATE)-1;
  COMMIT;
END;
/

步驟 3：提交作業至佇列

SQL> VARIABLE jobno NUMBER
SQL> BEGIN
  DBMS_JOB.SUBMIT(
    job       => :jobno,
    what      => 'my_job;',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1/24'  -- 每小時執行一次
  );
  COMMIT;
END;
/
SQL> PRINT jobno

參數詳解：

• job：輸出參數，回傳作業編號
• what：要執行的 PL/SQL 區塊（以分號結尾）
• next_date：首次執行時間
• interval：再次執行間隔運算式，NULL 表示僅執行一次

步驟 4：監控與維護

查詢使用者作業狀態：

SQL> SELECT job, next_date, failures, broken
  FROM user_jobs;

輸出範例：

JOB  NEXT_DATE           FAILURES BROKEN
----- ------------------- -------- ------
300  2024-01-15 14:30:00        0 N

• FAILURES >= 16 會自動標記 BROKEN='Y' 停止執行
• 手動重跑：DBMS_JOB.RUN(300);
• 修正後重啟：DBMS_JOB.BROKEN(300, FALSE);
• 修改排程：DBMS_JOB.INTERVAL(300, 'SYSDATE+1');
• 移除作業：DBMS_JOB.REMOVE(300);

進階：DBMS_SCHEDULER（10g+ 建議）

Oracle 10g 引入功能更強大的 DBMS_SCHEDULER，支援：

• 行事曆語法（如 FREQ=DAILY; BYHOUR=2）
• 鏈式作業依賴
• 資源管理與並行控制
• 事件驅動觸發

遷移建議： 新專案優先使用 Scheduler；既有 DBMS_JOB 可共存並逐步遷移。

最佳實務檢核表

• [ ] 確認 job_queue_processes > 0
• [ ] 執行帳號具備 EXECUTE ON DBMS_JOB
• [ ] 作業邏輯加入例外處理與日誌記錄
• [ ] 設定合理的 interval 避免作業重疊
• [ ] 定期檢視 DBA_JOBS 監控失敗作業
• [ ] 重要作業建立告警機制（如結合 Enterprise Manager）