函式儲存過程被誤刪恢復步驟

剛接到同事一個電話反應10分鐘之前誤刪了一個儲存過程，看能不能找回來？


大家都知道儲存過程、函式等的原始碼都是儲存在dba_source裡，當drop某個儲存過程或函式時，oracle會在源表刪除物件對應的記錄，
既然知道了是儲存的源表，那就好辦了啦，閃回查詢即可。不過千萬要注意查詢時間不要距離現在時間過長，否則會因為undo
被覆蓋而永遠丟失。


dba_source儲存的物件型別如下：
PACKAGE
PACKAGE BODY
TYPE BODY
FUNCTION
JAVA SOURCE
PROCEDURE
LIBRARY
TRIGGER
TYPE
以上任一型別的物件都可以透過該方法找回.


找回儲存過程SQL：
select text from dba_source as of timestamp to_timestamp('2014-11-25 17:20:00', 'YYYY-MM-DD HH24:MI:SS') 
where owner='DB_T' and name='P_LOAD_001' and type='PROCEDURE' order by line;


以下是找回儲存過程詳細步驟：
一、查詢被刪的儲存過程文字
SQL> select text from dba_source as of timestamp to_timestamp('2014-11-25 14:00:00', 'YYYY-MM-DD HH24:MI:SS') where owner='DB_T' and name= 'P_LOAD_001' order by line;
select text from dba_source as of timestamp to_timestamp('2014-11-25 14:00:00', 'YYYY-MM-DD HH24:MI:SS') where owner='DB_T' and name= 'P_LOAD_001' order by line
                 *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 35 with name "_SYSSMU35_1925328261$" too small


SQL> select text from dba_source as of timestamp to_timestamp('2014-11-25 17:20:00', 'YYYY-MM-DD HH24:MI:SS') where owner='DB_T' and name= 'P_LOAD_001' order by line;


TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE P_LOAD_001(V_EPARCHY_CODE IN VARCHAR2,
                        V_SETT_MONTH   IN VARCHAR2,
                        V_BACK_ID      OUT NUMBER,
                        V_BACK_MSG     OUT VARCHAR2) IS
.....


273 rows selected.
(保密原則，原始碼刪除，你懂得！)


2、重新建立就OK了啦。