解決 Oracle JOB INTERVAL 參數過長限制：利用 BROKEN 參數實現一次性執行並保留紀錄

Oracle 內部 JOB 機制中，INTERVAL 運算式長度有限制。Tom Kyte 提出利用 NEXT_DATE 輸出參數動態指定下次執行時間。本文延伸該思路，利用 BROKEN 參數實現**一次性執行後自動標記 BROKEN=Y 保留紀錄**，避免普通一次性 JOB 執行後即從視圖消失、需自建日誌表的麻煩。

核心技巧

在預存程序中輸出 BROKEN 參數並設為 TRUE：

CREATE OR REPLACE PROCEDURE p_test (p_broken OUT BOOLEAN) AS
BEGIN
  p_broken := TRUE;           -- 執行後標記 BROKEN
  DBMS_LOCK.SLEEP(5);         -- 模擬工作
END;

建立 JOB

GRANT EXECUTE ON DBMS_LOCK TO yangtk;
DECLARE
  v_job NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(v_job, 'p_test(BROKEN);', SYSDATE, 'SYSDATE + 1/1440'); -- 1 分鐘後執行
  COMMIT;
END;

驗證結果

SELECT job, what, total_time, broken, failures FROM user_jobs;
-- 第一次查詢：BROKEN=N (執行中)
-- 幾秒後再查：BROKEN=Y, TOTAL_TIME=5 秒, FAILURES=0
-- JOB 留在視圖中，不再重複執行

此法讓一次性 JOB 執行紀錄永久保留於 USER_JOBS，方便稽核與排查，無需額外日誌表。