存储过程恢复

今天在家复习准备下午的考试，接到另外一个项目组同事的电话。说生产环境的存储过程被删了，问怎么恢复。

简单的问了一下他们说从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  /