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; -- 正常輸出
DROP PROCEDURE proc_test_drop; -- 誤刪
SELECT text FROM dba_source WHERE owner='ROGER' AND name='PROC_TEST_DROP'; -- no rows
-- 需具備 FLASHBACK ANY TABLE 或 SYS 權限
SELECT text FROM dba_source
AS OF TIMESTAMP SYSDATE - 5/1440 -- 5 分鐘前
WHERE owner='ROGER' AND name='PROC_TEST_DROP';
輸出完整源碼,直接 CREATE OR REPLACE PROCEDURE ... 重建。
前提:UNDO 保留時間(UNDO_RETENTION)足夠、UNDO 表空間未被覆寫、表未啟用NOROWDEPENDENCIES。
-- 1. 確認刪除前 OBJ#
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
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
-- 2. 讀取 SOURCE$ 歷史源碼
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;
優點:不依賴 DBA_SOURCE 視圖定義,直接讀取基表歷史版本,精準度最高。
ODU> UNLOAD DICTODU> SCAN EXTENT PARALLEL 2ODU> UNLOAD TABLE SYS.SOURCE$ → 產生 SYS_SOURCE$.txt、.sql、.ctlSQL> @create.sql -- 建立 SYS_SOURCE$ 表SQL> LOAD DATA INFILE 'SYS_SOURCE$.txt' ... (使用 .ctl 控制檔)適用場景:資料庫無法開啟、UNDO 已覆寫、系統表空間損壞、ASM 元資料丟失等極端情況。
| 方法 | 適用條件 | 操作難度 | 恢復完整度 | 風險 |
|---|---|---|---|---|
| 閃回查詢 | UNDO 保留充足、資料庫開啟 | 低 | 完整 | 極低 |
| 基表歷史 | UNDO 保留充足、需 SYS 權限 | 中 | 完整 | 低 |
| ODU | 資料庫掛掉、UNDO 丟失、極端災難 | 高 | 視區塊完整度 | 中(需專業知識) |
DDL TRIGGER 記錄所有 DDL 操作至審計表。DBMS_METADATA.GET_DDL 匯出備份。UNDO_RETENTION ≥ 24 小時,UNDO 表空間啟用 RETENTION GUARANTEE。誤刪存儲過程,首選閃回查詢(秒級恢復),次選基表歷史(分鐘級),末選 ODU(小時級)。建立「DDL 審計 + 版本控制 + UNDO 保障」三層防線,才能從容應對人為誤操作。