刚接到同事一个电话反应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了啦。