函数存储过程被误删恢复步骤

大家都知道存储过程、函数等的源代码都是保存在dba_source里，当drop某个存储过程或函数时，oracle会在源表删除对象对应的记录，



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('20xx-xx-25 17:20:00', 'YYYY-MM-DD HH24:MI:SS') where owner='xx' and name= 'xx' 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
.....


 
 


2、重新创建就OK了啦。