今天在家复习准备下午的考试，接到另外一个项目组同事的电话。说生产环境的存储过程被删了，问怎么恢复。
简单的问了一下他们说从v$sql里看到这个存储过程大约是在10：00左右被删除的。
下面模拟一下解决过程。
SQL> drop procedure job_inst;
Procedure dropped
SQL> SELECT r.object_name ,r.original_name,r.operation ,r.droptime FROM user_recyclebin r;
OBJECT_NAME ORIGINAL_NAME OPERATION DROPTIME
------------ -------------- --------- -----------
SQL> conn / as sysdba
已连接。
SQL> col name for a10;
SQL> col text for a60;
SQL> SELECT NAME, TEXT
2 FROM DBA_SOURCE AS OF TIMESTAMP TO_TIMESTAMP('2011-04-15 12:20:00', 'yyyy-mm-dd hh24:mi:ss')
3 WHERE OWNER = 'REPORT'
4 AND TYPE = 'PROCEDURE'
5 AND NAME = 'JOB_INST';
NAME TEXT
---------- ------------------------------------------------------------
JOB_INST procedure job_inst is
JOB_INST begin
JOB_INST execute immediate('truncate table t_job');
JOB_INST execute immediate ('insert into t_job
JOB_INST select INSTANCE_NUMBER,INSTANCE_NAME ,sysdate from v$instance');
JOB_INST commit;
JOB_INST end job_inst;
SQL> create or replace procedure job_inst is
2 begin
3 execute immediate('truncate table t_job');
4 execute immediate ('insert into t_job
5 select INSTANCE_NUMBER,INSTANCE_NAME ,sysdate from v$instance');
6 commit;
7 end job_inst;
8 /