存儲過程定義儲存在資料字典 (DBA_SOURCE) 中,修改字典等同於修改普通表資料,舊版本會寫入 UNDO。利用 閃回查詢 (AS OF TIMESTAMP) 即可在 UNDO 保留期內找回被覆蓋/刪除的程式碼。
關鍵:越早操作成功率越高,UNDO 空間被覆寫後就無法恢復。
GG_TESTDROP PROCEDURE GG_TEST-- 1. 以 SYS 權限登入
-- 2. 利用閃回查詢將 DBA_SOURCE 歷史版本存入臨時表
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';
-- 3. 取出原始碼
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;
AS OF SCN 配合 V$FLASHBACK_DATABASE_LOG 推算DBA_PROCEDURES、DBA_OBJECTS 等字典視圖FLASHBACK DATABASE 並設定合理 UNDO_RETENTION