Oracle DBMS_JOB 定時任務完整實戰指南：建立、監控與管理

Oracle 定時排程核心：DBMS_JOB 套件

DBMS_JOB 是 Oracle 傳統作業排程機制（10g 前主流，11g+ 建議遷移至 DBMS_SCHEDULER），適用於簡單週期性 PL/SQL 執行需求。本文以實戰範例完整示範建立、監控、停止全流程。

前置準備：建立測試表與程序

-- 建立測試表
CREATE TABLE HWQY.TEST (
  CARNO VARCHAR2(30),
  CARINFOID NUMBER
);

-- 建立序列與存儲過程
CREATE SEQUENCE S_CarInfoID START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE PRO_TEST AS
  V_CARINFO_ID NUMBER;
BEGIN
  SELECT S_CarInfoID.NEXTVAL INTO V_CARINFO_ID FROM DUAL;
  INSERT INTO TEST (CARNO, CARINFOID) VALUES (V_CARINFO_ID, '123');
  COMMIT;
END PRO_TEST;

核心操作：提交定時作業

VARIABLE JOBNO NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    JOB     => :JOBNO,
    WHAT    => 'PRO_TEST;',
    NEXT_DATE => SYSDATE,
    INTERVAL  => 'SYSDATE + 1/24/12'  -- 每 5 分鐘執行一次
  );
  COMMIT;
END;
/

參數說明：

• WHAT：要執行的 PL/SQL 區塊（必須以分號結尾）
• NEXT_DATE：首次執行時間
• INTERVAL：重複間隔運算式，回傳下一次執行日期

常用 INTERVAL 寫法對照表

作業監控與查詢

-- 查詢使用者作業佇列
SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN 
FROM USER_JOBS;

-- 查詢詳細執行狀態
SELECT JOB, LAST_DATE, LAST_SEC, THIS_DATE, THIS_SEC, 
       FAILURES, BROKEN, INTERVAL
FROM USER_JOBS;

關鍵欄位：BROKEN='Y' 表示作業中斷需人工介入，FAILURES 累計失敗次數（達 16 次自動標記 BROKEN）。

作業控制操作

-- 停用/移除作業 (JOB=1)
BEGIN DBMS_JOB.REMOVE(1); COMMIT; END;
/

-- 暫停作業 (標記 BROKEN)
BEGIN DBMS_JOB.BROKEN(1, TRUE); COMMIT; END;
/

-- 恢復作業
BEGIN DBMS_JOB.BROKEN(1, FALSE); COMMIT; END;
/

-- 強制立即執行一次
BEGIN DBMS_JOB.RUN(1); COMMIT; END;
/

-- 修改執行間隔
BEGIN DBMS_JOB.INTERVAL(1, 'SYSDATE + 1/24'); COMMIT; END;
/

-- 修改下次執行時間
BEGIN DBMS_JOB.NEXT_DATE(1, SYSDATE + 1/24); COMMIT; END;
/

關鍵環境參數檢查

-- 檢查作業佇列進程數 (必須 > 0)
SHOW PARAMETER JOB_QUEUE_PROCESSES;

-- 啟用/調整 (需重啟實例或 ALTER SYSTEM)
ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 10 SCOPE=BOTH;

完整語法範本（含命名參數）

VARIABLE JOBNO NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT(
    JOB       => :JOBNO,
    WHAT      => 'PRO_TEST;',
    NEXT_DATE => TO_DATE('21-02-2008 17:37:26', 'DD-MM-YYYY HH24:MI:SS'),
    INTERVAL  => 'SYSDATE + 1/24/12'
  );
  COMMIT;
END;
/

遷移建議：DBMS_SCHEDULER

Oracle 10g+ 強烈建議遷移至 DBMS_SCHEDULER，提供：

• 更豐富的排程表達式（CALendaring syntax）
• 作業鏈、事件驅動、資源管理整合
• 更完善的日誌與錯誤處理
• Enterprise Manager 圖形化管理