Oracle DBMS_JOB 排程與預存程序實現月度資料歸檔

前言

大量資料表需定期將歷史資料移出熱表，維持查詢效能。Oracle DBMS_JOB (9i~12c 相容) 配合動態 SQL 預存程序，可實現月度分區式歸檔：建立歷史表、搶救當月資料、切換表名。本文提供完整實作代碼與排程設定。

業務場景

• 主表：test_ad, test_pd (巡檢資料)，月增百萬筆。
• 需求：每月 3 號 00:00 將上月資料歸檔至 PA_AD_YYYYMM, PA_PD_YYYYMM，熱表保留當月資料。
• 備援：若 3 號排程失敗，15 號 00:10 執行補檢程序。

核心歸檔預存程序

CREATE OR REPLACE PROCEDURE myproc AS
    v_ad_hist  VARCHAR2(30);
    v_pd_hist  VARCHAR2(30);
    v_cutoff   DATE;
    v_sql      VARCHAR2(500);
BEGIN
    -- 1. 計算切割點：上月最後一天 23:59:59
    v_cutoff := TRUNC(LAST_DAY(SYSDATE) + 1) - 1/86400;  -- 或 TRUNC(SYSDATE, 'MM') - 1/86400
    
    -- 2. 歷史表名稱
    v_ad_hist := 'PA_AD_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM');
    v_pd_hist := 'PA_PD_' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM');
    
    -- 3. 建立歷史表 (CTAS 含上月資料)
    v_sql := 'CREATE TABLE ' || v_ad_hist || 
             ' AS SELECT * FROM test_ad WHERE patroldate < :cutoff';
    EXECUTE IMMEDIATE v_sql USING v_cutoff;
    
    v_sql := 'CREATE TABLE ' || v_pd_hist || 
             ' AS SELECT * FROM test_pd WHERE patroldate < :cutoff';
    EXECUTE IMMEDIATE v_sql USING v_cutoff;
    
    -- 4. 建立當月資料暫存表
    v_sql := 'CREATE TABLE temp_ad AS SELECT * FROM test_ad WHERE patroldate >= :cutoff';
    EXECUTE IMMEDIATE v_sql USING v_cutoff;
    
    v_sql := 'CREATE TABLE temp_pd AS SELECT * FROM test_pd WHERE patroldate >= :cutoff';
    EXECUTE IMMEDIATE v_sql USING v_cutoff;
    
    -- 5. 刪除原熱表
    EXECUTE IMMEDIATE 'DROP TABLE test_ad';
    EXECUTE IMMEDIATE 'DROP TABLE test_pd';
    
    -- 6. 重命名暫存表為熱表
    EXECUTE IMMEDIATE 'RENAME temp_ad TO test_ad';
    EXECUTE IMMEDIATE 'RENAME temp_pd TO test_pd';
    
    -- 7. 記錄執行日誌
    INSERT INTO oper_proc_log (exec_date, proc_name, status) VALUES (SYSDATE, 'myproc', 0);
    COMMIT;
    
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO oper_proc_log (exec_date, proc_name, status, err_msg) 
        VALUES (SYSDATE, 'myproc', 1, SQLERRM);
        COMMIT;
        RAISE;
END myproc;
/

關鍵設計點

1. CTAS + DROP + RENAME：避免 DELETE 不釋放空間、產生大量 UNDO/REDO、索引碎片化。
2. 動態 SQL + 繫結變數：EXECUTE IMMEDIATE ... USING v_cutoff 防止 SQL 注入、利用游標共享。
3. 切割點精確：使用 TRUNC(LAST_DAY(SYSDATE)+1) 取得次月 1 號 00:00:00，減 1 秒得上月最後一刻。
4. 權限需求：執行使用者需 GRANT CREATE TABLE, DROP ANY TABLE (或擁有 DBA)。

補檢預存程序

CREATE OR REPLACE PROCEDURE pasupplyproc AS
    v_cnt NUMBER;
BEGIN
    -- 檢查本月是否已執行 myproc
    SELECT COUNT(*) INTO v_cnt FROM oper_proc_log
    WHERE proc_name = 'myproc'
      AND exec_date >= TRUNC(SYSDATE, 'MM');
    
    IF v_cnt = 0 THEN
        -- 未執行，立即呼叫
        EXECUTE IMMEDIATE 'BEGIN myproc; END;';
        COMMIT;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO oper_proc_log (exec_date, proc_name, status, err_msg)
        VALUES (SYSDATE, 'pasupplyproc', 1, SQLERRM);
        COMMIT;
END pasupplyproc;
/

DBMS_JOB 排程設定

-- 主排程：每月 3 號 00:00
DECLARE
    v_job NUMBER;
BEGIN
    DBMS_JOB.SUBMIT(
        job       => v_job,
        what      => 'myproc;',
        next_date => SYSDATE,
        interval  => 'TRUNC(LAST_DAY(SYSDATE) + 3)'  -- 次月 3 號 00:00
    );
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Job ' || v_job || ' submitted.');
END;
/

-- 補檢排程：每月 15 號 00:10
DECLARE
    v_job NUMBER;
BEGIN
    DBMS_JOB.SUBMIT(
        job       => v_job,
        what      => 'pasupplyproc;',
        next_date => SYSDATE,
        interval  => 'TRUNC(LAST_DAY(SYSDATE) + 14) + 10/1440'  -- 次月 15 號 00:10
    );
    COMMIT;
END;
/

INTERVAL 運算式解析

作業監控與維護

-- 查看所有 JOB
SELECT job, what, last_date, next_date, interval, failures, broken
FROM dba_jobs;

-- 手動執行特定 JOB
EXEC DBMS_JOB.RUN(123);

-- 移除 JOB
EXEC DBMS_JOB.REMOVE(123);

-- 修改 INTERVAL
EXEC DBMS_JOB.INTERVAL(123, 'TRUNC(LAST_DAY(SYSDATE)+3)');

-- 檢查執行日誌
SELECT * FROM oper_proc_log ORDER BY exec_date DESC;

遷移至 DBMS_SCHEDULER (10g+ 推薦)

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_MONTHLY_ARCHIVE',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'myproc',
        start_date      => TRUNC(SYSDATE) + 3,  -- 明天 3 號
        repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=3; BYHOUR=0; BYMINUTE=0; BYSECOND=0',
        enabled         => TRUE,
        comments        => 'Monthly archive for test_ad/test_pd'
    );
END;
/

優勢： 支援日曆語法、錯誤重試、資源管理、事件驅動、雲端控制台監控。

最佳實務總結

1. 歸檔策略：CTAS 建歷史表 → 暫存當月 → DROP 舊表 → RENAME，最小化鎖定時間與空間碎片。
2. 冪等性：補檢程序檢查日誌避免重複執行。
3. 日誌完整：記錄執行時間、狀態、錯誤訊息，便於稽核與告警。
4. 權限最小化：建立專用歸檔使用者，僅授 CREATE TABLE, DROP ANY TABLE 於特定 Schema。
5. 測試驗證：上線前在測試環境模擬跨月、跨年、閏月、排程失敗重跑等場景。

注意：DBMS_JOB 在 12c 後為相容性保留，新專案請直接使用 DBMS_SCHEDULER。若資料量極大 (TB 級)，考慮使用分區表 + Partition Exchange Load (PEL) 實現秒級切換，無需 DROP/CREATE。