Oracle 存儲過程被覆蓋後的閃回恢復實戰

核心原理

存儲過程定義儲存在資料字典（`DBA_SOURCE`/`USER_SOURCE`）中。修改或刪除過程本質上是對字典表的 DML 操作，Oracle 會在 Undo 段保留修改前的映像。利用 閃回查詢（`AS OF TIMESTAMP`）即可在 Undo 保留期內找回舊版本程式碼。

關鍵：必須盡快操作，Undo 空間被覆寫後將無法恢復。

實戰演練場景

步驟 1：建立測試程序 (14:31)

CONN test/test
CREATE OR REPLACE PROCEDURE GG_TEST AS
  l_cnt NUMBER;
BEGIN
  FOR i IN 1 .. 10000 LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM t WHERE x = ' || i INTO l_cnt;
  END LOOP;
END;
/
SHOW ERRORS

步驟 2：誤刪除程序 (14:33)

DROP PROCEDURE GG_TEST;

步驟 3：SYS 執行閃回查詢恢復 (14:35)

CONN / AS SYSDBA

-- 1. 將閃回時間點設為刪除前 (14:33) 的資料字典快照建立臨時表
CREATE TABLE p_temp AS
SELECT *
FROM dba_source AS OF TIMESTAMP TO_TIMESTAMP('2014-05-04 14:33:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE type = 'PROCEDURE'
  AND owner = 'TEST'
  AND name = 'GG_TEST';

-- 2. 按行號順序提取程式碼文本
SELECT text
FROM p_temp
WHERE name LIKE UPPER('%GG_TEST%')
  AND owner = 'TEST'
ORDER BY line;

查詢結果：完整恢復的程式碼

TEXT
---------------------------------------------------------------------------
PROCEDURE GG_TEST
AS l_cnt NUMBER;
BEGIN
  FOR i IN 1 .. 10000 LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM t WHERE x = ' || i INTO l_cnt;
  END LOOP;
END;

關鍵點總結

1. 時間點精確：`AS OF TIMESTAMP` 需指定到秒，最好在誤操作發生後立即記錄時間。
2. 權限要求：需 `SELECT ANY DICTIONARY` 或 `DBA` 角色才能查詢 `DBA_SOURCE`。
3. Undo 保留：由 `UNDO_RETENTION` 參數控制，生產庫建議設定 ≥ 24 小時。
4. 適用範圍：同樣適用于套件、觸發器、視圖等所有儲存在 `*_SOURCE` 字典的物件。

替代方案：啟用 DDL 觸發器審計

為預防未來誤刪，可建立 DDL 觸發器記錄所有 `DROP/CREATE/ALTER` 操作：

CREATE OR REPLACE TRIGGER audit_ddl_trg
AFTER DDL ON DATABASE
BEGIN
  INSERT INTO ddl_audit_log (osuser, current_user, ddl_type, object_name, sql_text, created)
  VALUES (SYS_CONTEXT('USERENV','OS_USER'), SYS_CONTEXT('USERENV','CURRENT_USER'),
          ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, ORA_SQL_TXT, SYSDATE);
END;
/
-- 需先建立 ddl_audit_log 表