Oracle 刪除預存程序後的三種恢復實戰：閃回查詢、基表、ODU 工具

誤刪 PROCEDURE/FUNCTION/PACKAGE 後，若無版控備份，可嘗試三種層級遞進的恢復手段：閃回查詢（最簡）、基表歷史（中階）、ODU 磁碟掃描（終極）。本文以實測案例完整記錄。

一、建立測試環境

CONN roger/roger
CREATE OR REPLACE PROCEDURE proc_test_drop AS
BEGIN
  FOR x IN (SELECT SYSDATE FROM DUAL) LOOP
    DBMS_OUTPUT.PUT_LINE(x.SYSDATE);
  END LOOP;
END;
/
EXEC proc_test_drop;  -- 正常執行

-- 查詢原始碼
SELECT TEXT, NAME FROM DBA_SOURCE WHERE OWNER='ROGER' AND NAME='PROC_TEST_DROP';

-- 誤刪
DROP PROCEDURE proc_test_drop;
SELECT TEXT FROM DBA_SOURCE WHERE OWNER='ROGER' AND NAME='PROC_TEST_DROP';  -- no rows

二、方法一：閃回查詢

前提：UNDO_RETENTION 足夠、UNDO 表空間未被覆寫、具備 FLASHBACK ANY TABLE 權限。

CONN / AS SYSDBA
-- 以 5 分鐘前時間點查詢
SELECT TEXT FROM DBA_SOURCE AS OF TIMESTAMP SYSDATE - 5/60/24
WHERE OWNER='ROGER' AND NAME='PROC_TEST_DROP';

輸出：完整原始碼 8 行，直接複製重建即可。

三、方法二：基表歷史查詢

針對 SYS.OBJ$、SYS.SOURCE$ 直接閃回，繞過視圖限制。

1. 確認刪除時間點（如 2013-08-06 02:40:00）。
2. 查詢 OBJ#：

   SELECT OBJ# FROM OBJ$ AS OF TIMESTAMP TO_TIMESTAMP('2013-08-06 02:40:00','YYYY-MM-DD HH24:MI:SS')
   WHERE NAME='PROC_TEST_DROP';  -- 返回 52148

3. 查詢原始碼：

   SET LONG 9999999
   SELECT SOURCE FROM SOURCE$ AS OF TIMESTAMP TO_TIMESTAMP('2013-08-06 02:40:00','YYYY-MM-DD HH24:MI:SS')
   WHERE OBJ# = 52148 ORDER BY LINE;

同樣可獲得完整原始碼。

四、方法三：ODU (Oracle Data Unloader) 磁碟層掃描

適用於 UNDO 已覆寫、資料庫無法開啟、系統表空間損壞的極端場景。

步驟

1. ODU 解析資料字典：UNLOAD DICT（載入 OBJ$、USER$ 等）。
2. 掃描區塊：SCAN EXTENT PARALLEL 2。
3. 卸載 SOURCE$ 表：UNLOAD TABLE SYS.SOURCE$，產生 SYS_SOURCE$.txt、.sql、.ctl。
4. 建立臨時表並載入資料：

   -- 建表腳本 SYS_SOURCE$.sql
   CREATE TABLE "SYS"."SOURCE$" ("OBJ#" NUMBER NOT NULL, "LINE" NUMBER NOT NULL, "SOURCE" VARCHAR2(4000));
   
   -- SQL*Loader 控制檔 SYS_SOURCE$.ctl
   LOAD DATA INFILE 'SYS_SOURCE$.txt' "STR X'0a'"
   APPEND INTO TABLE "SYS"."SOURCE$"
   FIELDS TERMINATED BY X'7c' TRAILING NULLCOLS
   ("OBJ#", "LINE", "SOURCE" CHAR(4000))

5. 修改 OWNER 為目標使用者，匯入目標 Schema。

五、方法對照與選擇建議

六、預防措施

1. 啟用 DDL TRIGGER 記錄結構變更（CREATE OR REPLACE TRIGGER ... BEFORE DROP ON DATABASE）。
2. 版控管理：所有物件原始碼納入 Git/SVN，部署時同步。
3. 設定合理 UNDO_RETENTION（建議 ≥ 24 小時）並保證 UNDO 表空間足夠大。
4. 定期 EXPDP METADATA_ONLY 備份結構定義。