PL/SQL 實現 Oracle 任務調度與閃回查詢：DBMS_JOB 與 DBMS_FLASHBACK 實戰

Oracle 內建 DBMS_JOB（任務調度）與 DBMS_FLASHBACK（閃回查詢）兩大包，分別解決「周期性自動執行」與「誤操作數據恢復」兩大經典痛點。本文系統講解配置、API、實戰場景與遷移建議。

一、DBMS_JOB：傳統任務調度（10g/11g 主流，12c+ 建議遷移至 DBMS_SCHEDULER）

初始化參數（需 DBA 權限修改 spfile/初始化文件並重啟）

JOB_QUEUE_PROCESSES = 10    -- 啟動的後台作業進程數，0 表示禁用調度
JOB_QUEUE_INTERVAL = 60     -- 每進程檢查新作業的間隔秒數，作業最小執行間隔

核心 API：SUBMIT 提交作業

VARIABLE v_jobno NUMBER;
BEGIN
    DBMS_JOB.SUBMIT(
        job       => :v_jobno,           -- OUT: 分配的作業號
        what      => 'MY_PKG.DAILY_CLEANUP;',  -- 要執行的 PL/SQL 塊（以分號結尾）
        next_date => SYSDATE,            -- 首次執行時間
        interval  => 'SYSDATE + 1'       -- 重複間隔表達式（每天）
    );
    COMMIT;  -- 必須提交才生效
END;
/
PRINT v_jobno;

常用間隔表達式

管理命令

-- 查看用戶作業
SELECT JOB, WHAT, NEXT_DATE, INTERVAL, BROKEN FROM USER_JOBS;

-- 修改作業（如變更執行內容）
EXEC DBMS_JOB.CHANGE(123, 'MY_PKG.NEW_LOGIC;');

-- 移除作業
EXEC DBMS_JOB.REMOVE(123);

-- 強制立即執行一次
EXEC DBMS_JOB.RUN(123);

-- 標記/取消標記損壞（停用/啟用）
EXEC DBMS_JOB.BROKEN(123, TRUE);  -- 停用
EXEC DBMS_JOB.BROKEN(123, FALSE); -- 啟用

二、DBMS_FLASHBACK：閃回查詢恢復誤刪/誤改數據

核心原理

Oracle 通過 Undo 段保留歷史版本，DBMS_FLASHBACK 允許會話級設置「查詢時間點（SCN 或 Timestamp）」，後續該會話所有查詢自動訪問該時刻的數據快照，不修改當前數據，極其安全。

關鍵程序

實戰：恢復誤刪除的員工記錄

-- 場景：10:00 誤執行 DELETE FROM EMP WHERE DEPTNO=10，需恢復
-- 步驟 1：確定誤操作前時間點（如 09:55）
DECLARE
    v_scn NUMBER;
BEGIN
    -- 獲取 09:55 對應的 SCN（或直接用 TIMESTAMP）
    v_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; -- 當前 SCN，實際需查閱歷史映射
    
    -- 啟用閃回
    DBMS_FLASHBACK.ENABLE_AT_TIME(TIMESTAMP '2024-01-15 09:55:00');
    
    -- 查詢被刪數據（此時看到 09:55 的快照）
    INSERT INTO EMP_RECOVERY
    SELECT * FROM EMP WHERE DEPTNO = 10;
    
    -- 關閉閃回，恢復正常會話
    DBMS_FLASHBACK.DISABLE;
    
    COMMIT;
END;
/

-- 步驟 2：驗證恢復數據
SELECT * FROM EMP_RECOVERY;

關鍵限制：閃回查詢依賴 Undo 保留時間（UNDO_RETENTION 參數，默認 900 秒）。若誤操作超過保留窗口，Undo 已被覆蓋，無法閃回。生產庫建議設置 UNDO_RETENTION = 86400 (1天) 並保證 Undo 表空間足夠大。

三、現代替代：DBMS_SCHEDULER (10gR2+ 推薦)

DBMS_JOB 為舊版調度器，功能受限（無事件驅動、無鏈式依賴、無資源管理、無遠程外部作業）。12c+ 新項目強制使用 DBMS_SCHEDULER。

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'DAILY_CLEANUP_JOB',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'MY_PKG.DAILY_CLEANUP',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',  -- Calendaring 語法
        enabled         => TRUE,
        comments        => '每日清理過期數據',
        auto_drop       => FALSE
    );
END;
/

-- 事件驅動示例：文件到達觸發
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'FILE_WATCHER_JOB',
        job_type => 'EXECUTABLE',
        job_action => '/app/scripts/process_file.sh',
        event_condition => 'tab.user_data.file_size > 1000',
        queue_spec => 'FILE_WATCH_Q',
        enabled => TRUE
    );
END;
/

四、總結與選型建議

掌握 DBMS_JOB 維護存量、DBMS_SCHEDULER 構建增量、DBMS_FLASHBACK 守護數據安全，構成 Oracle DBA 任務調度與數據恢復的核心技能組合。

1. 上一篇文章： SQL Server 精準測量語句執行時間：毫秒級計時模板
2. 下一篇文章： Oracle 高 CPU 消耗診斷實戰：vmstat 定位、SQL 追蹤、執行計劃分析與綁定變量優化

MySQL 資料庫相關知識

MySQL 中文亂碼問題徹底解決：字符集配置、連線協議、應用層最佳實踐

中文亂碼是 MySQL 使用者最常遇到的坑，本質是「編碼不一

MySQL Root 密碼遺忘重置圖文教學：--skip-grant-tables 模式完整步驟

遺忘 Root 密碼時，以 --skip-grant-tab

MySQL 從 MyISAM 遷移至 InnoDB 完整實戰記錄與錯誤排查

WordPress 預設使用 InnoDB 引擎，若 MyS

BigDump大數據庫導入避坑指南：七大關鍵注意事項

核心前提 目標庫必須為空：含數據會報錯停止，導入前DROP