今天在家复习准备下午的考试，接到另外一个项目组同事的电话。说生产环境的存储过程被删了，问怎么恢复。
简单的问了一下他们说从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 isJOB_INST beginJOB_INST execute immediate('truncate table t_job');JOB_INST execute immediate ('insert into t_jobJOB_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 /
