Oracle 排程任務實戰補充：DBMS_JOB 參數構造技巧與監控視圖完整速查

補充說明：INTERVAL 字串的「求值時機」陷阱

INTERVAL 參數為字串表達式，在每次 Job 執行完成後由資料庫求值決定下次執行時間。故必須包含 SYSDATE 等動態函數，否則會固定在建立時的時間點。

INTERVAL 構造黃金法則

-- 原則：回傳下一次執行的 DATE 值
-- 錯誤：'SYSDATE + 1'  -- 若 Job 執行耗時 2 小時，下次仍按「執行完成時刻」+1 天
-- 正確：'TRUNC(SYSDATE + 1) + 2/24'  -- 強制對齊至翌日 02:00，不受執行耗時影響

進階間隔模式庫

監控視圖完整速查

實用監控查詢

1. 失敗 Job 告警

SELECT job, schema_user, what, failures, last_date, last_sec
FROM dba_jobs
WHERE failures > 0 AND broken = 'N'
ORDER BY last_date DESC;

2. 長時間運行 Job 檢測

SELECT j.job, j.schema_user, j.what, r.start_date,
       (SYSDATE - r.start_date) * 24 * 60 AS running_minutes
FROM dba_jobs_running r, dba_jobs j
WHERE r.job = j.job
AND (SYSDATE - r.start_date) * 24 * 60 > 60;  -- 超過 60 分鐘

3. 間隔異常 Job (下次執行時間異常)

SELECT job, schema_user, next_date, interval
FROM dba_jobs
WHERE next_date > SYSDATE + 30  -- 超過 30 天未執行
OR next_date < SYSDATE - 1;     -- 過期未執行

4. Job 佇列進程狀態

SELECT * FROM v$job_queue_processes;
-- 或
SHOW PARAMETER job_queue_processes;

從 DBMS_JOB 遷移到 DBMS_SCHEDULER 對照表

遷移範例：每日 02:00 執行統計過程

-- DBMS_JOB 寫法
DBMS_JOB.SUBMIT(:j, 'PROC_STATS;', TRUNC(SYSDATE+1)+2/24, 'TRUNC(SYSDATE+1)+2/24');

-- DBMS_SCHEDULER 寫法
DBMS_SCHEDULER.CREATE_JOB(
  job_name        => 'DAILY_STATS_JOB',
  job_type        => 'STORED_PROCEDURE',
  job_action      => 'PROC_STATS',
  start_date      => TRUNC(SYSDATE+1) + 2/24,
  repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
  enabled         => TRUE
);

總結：排程任務三層防護

1. 建立層：INTERVAL 強制對齊、COMMIT 必提交、WHAT 權限預驗
2. 運行層：FAILURES 監控告警、長運行檢測、BROKEN 防誤停
3. 演進層：簡單用 JOB、複雜用 SCHEDULER、統一納入變更管理

Oracle 排程任務的藝術在於：用字串表達式駕馭時間，用字典視圖洞察狀態，用遷移路徑適應複雜度。掌握 INTERVAL 構造與監控視圖，讓定時任務成為系統穩定運行的隱形引擎。