Oracle DBMS_JOB 完整實戰指南：JOB_QUEUE_PROCESSES 設定、核心 API、Broken 處理與啟動除錯

一、啟用 Job Queue 背景程序

-- 動態調整，立即生效 (需 > 0)
ALTER SYSTEM SET job_queue_processes = 10 SCOPE = BOTH;
-- 最大值 1000，視併發排程數量設定

-- 查看背景程序狀態
SELECT name, description FROM v$bgprocess WHERE name LIKE 'J%';
-- J000 ~ J999 為 Job Queue Processes

注意：11gR2 起建議改用 DBMS_SCHEDULER (更強大、支援鏈條、事件驅動、資源管理)，DBMS_JOB 僅為相容保留。

二、DBMS_JOB 核心子程序速查

三、BROKEN 處理詳解

PROCEDURE Broken (
  job       IN BINARY_INTEGER,
  broken    IN BOOLEAN,
  next_date IN DATE := SYSDATE
);

• job：SUBMIT 回傳的 Job Number
• broken：TRUE 標記失效 (不再執行)；FALSE 恢復正常
• next_date：下次執行時間，預設目前時間

Job 為何變 Broken？

連續 16 次執行失敗 (例外未捕捉) → Oracle 自動標記 Broken。

兩種恢復方式

1. 立即手動執行驗證

   
   BEGIN
     DBMS_JOB.RUN(:jobno);  -- :jobno 為 SUBMIT 回傳值
   END;
   /
   

2. 重設 Broken 標記並指定下次執行時間

   
   BEGIN
     DBMS_JOB.BROKEN(:jobno, FALSE, SYSDATE + 1/24); -- 1 小時後執行
   END;
   /
   

四、完整建立與管理範例

1. 建立測試表與 Procedure

CREATE TABLE test_job (run_time DATE);

CREATE OR REPLACE PROCEDURE my_job_proc AS
BEGIN
  INSERT INTO test_job VALUES (SYSDATE);
  COMMIT;
END;
/

2. 提交 Job (每分鐘執行一次)

VARIABLE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    job     => :jobno,
    what    => 'my_job_proc;',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1/1440'  -- 1 分鐘 = 1/1440 天
  );
  COMMIT;
END;
/
PRINT jobno;

3. 立即測試執行

BEGIN
  DBMS_JOB.RUN(:jobno);
END;
/
SELECT * FROM test_job;

4. 查看 Job 狀態

SELECT job, next_date, next_sec, failures, broken
FROM user_jobs;

5. 修改間隔 / 內容 / 下次時間

-- 改為每小時
BEGIN
  DBMS_JOB.INTERVAL(:jobno, 'SYSDATE + 1/24');
  COMMIT;
END;
/

-- 改內容
BEGIN
  DBMS_JOB.WHAT(:jobno, 'my_job_proc;');
  COMMIT;
END;
/

6. 刪除 Job

BEGIN
  DBMS_JOB.REMOVE(:jobno);
  COMMIT;
END;
/

五、遷移至 DBMS_SCHEDULER 建議 (11g+)

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'MY_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'MY_JOB_PROC',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
    enabled         => TRUE,
    comments        => '遷移自 DBMS_JOB'
  );
END;
/

優勢：支援時區、事件驅動、鏈條、資源消費控制、詳細日誌 (DBA_SCHEDULER_JOB_RUN_DETAILS)。

六、常見問題排查