PL/SQL 實作 Oracle 資料庫任務排程與資料救援

前言

資料庫操作中常遇兩類情境：疏失誤刪/誤改關鍵資料，以及需週期性執行的重要任務。前者屬備份恢復範疇，後者屬任務排程。本文從應用開發角度，以 PL/SQL 為平台提供解決方案。

技術基礎：兩大核心套件

PL/SQL 為 SQL 引入程序化能力，但功能不如 C/Java 強大，Oracle 提供大量開發套件增強能力。本文聚焦 DBMS_FLASHBACK（閃回查詢）與 DBMS_JOB（任務排程）。

1. DBMS_FLASHBACK：時光機般的閃回查詢

一般查詢針對當前時間，此套件可設定查詢時間點，檢視歷史資料狀態，對誤操作救援極具價值。核心函數：

• ENABLE / DISABLE：啟動/關閉閃回模式，每次啟動前必先關閉。
• ENABLE_AT_TIME：設定查詢時間點（以當前時間為基準）。

2. DBMS_JOB：內建排程器

允許 PL/SQL 區塊按指定時間自動執行，類似定時器。需先設定兩個 init.ora 參數：

• JOB_QUEUE_PROCESSES：背景處理程序數，為 0 則不執行作業。
• JOB_QUEUE_INTERVAL：檢查新作業的等待秒數，期間內作業最多執行一次。

核心函數 SUBMIT(job, what, next_date, interval)：提交作業並回傳作業編號。

資料救援實戰：閃回查詢恢復誤刪資料

假設基表 emp_table 有 3 筆資料，誤刪第一筆並已 COMMIT，無法 ROLLBACK。若誤操作發生於 5 分鐘前，可執行：

EXECUTE dbms_flashback.enable_at_time(sysdate - 5/1440);

此時查詢將顯示 5 分鐘前的完整資料。隨即備份：

CREATE TABLE emp_table_bk AS SELECT * FROM emp_table;

即完成資料恢復。

使用限制與注意事項

• 前提：資料庫必須啟用自動 UNDO 管理，建立 UNDO 表空間並設定保留時間窗口。
• UNDO 表空間大小決定可閃回的時間範圍，建議查詢 5 天內資料以提高成功率。

任務排程實戰：月薪自動調整

需每月對員工考核調薪，建立更新程序：

CREATE OR REPLACE PROCEDURE salary_upt(v_emp_no VARCHAR2, v_salary NUMBER) AS
BEGIN
  UPDATE emp_table SET emp_salary = v_salary WHERE emp_no = v_emp_no;
  COMMIT;
END;

排程每 30 天執行一次：

VARIABLE v_jobNum NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:v_jobNum, 'salary_upt', SYSDATE, 'SYSDATE+30');
  COMMIT;
END;

停止排程：DBMS_JOB.REMOVE(:v_jobNum);

此法易於與應用整合，操作便捷。

開發環境與小結

• 伺服器端：UNIX + Oracle 9.2
• 客戶端：Windows 2000 Pro + TOAD / SQL*Plus
• 所有代碼經測試通過

資料庫功能可透過應用程式擴展，後台開發人員除熟悉架構外，掌握應用開發能力至關重要。