在某些时候，容易误删存储过程，那么针对存储过程被删除了，我们如何进行恢复呢 ？ 这里为大家进行讲解。

1.  创建测试存储过程
SQL> conn roger/roger
Connected.
SQL> CREATE OR REPLACE PROCEDURE proc_test_drop
  2  AS
  3  BEGIN
  4     FOR x IN (SELECT sysdate FROM dual)
  5     LOOP
  6        DBMS_OUTPUT.put_line (x.sysdate);
  7     END LOOP;
  8  END proc_test_drop;
  9  /

Procedure created.

SQL> set serveroutput on
SQL> exec proc_test_drop;

06-AUG-13

PL/SQL procedure successfully completed.

SQL>

SQL> l
  1* select text,name from dba_source where owner='ROGER' and name='PROC_TEST_DROP'
SQL> /

TEXT                                              NAME
------------------------------------------------- ------------------------------
PROCEDURE proc_test_drop                          PROC_TEST_DROP
AS                                                PROC_TEST_DROP
BEGIN                                             PROC_TEST_DROP
   FOR x IN (SELECT sysdate FROM dual)            PROC_TEST_DROP
   LOOP                                           PROC_TEST_DROP
      DBMS_OUTPUT.put_line (x.sysdate);           PROC_TEST_DROP
   END LOOP;                                      PROC_TEST_DROP
END proc_test_drop;                               PROC_TEST_DROP

8 rows selected.

SQL> show user
USER is "SYS"
SQL> conn roger/roger
Connected.
SQL> drop PROCEDURE proc_test_drop;

Procedure dropped.

SQL> select text,name from dba_source where owner='ROGER' and name='PROC_TEST_DROP';

no rows selected

SQL>

—-利用闪回查询进行恢复
CREATE OR REPLACE FORCE VIEW “SYS”.”DBA_SOURCE” (“OWNER”, “NAME”, “TYPE”, “LINE”, “TEXT”) AS
  select u.name, o.name,
decode(o.type#, 7, ‘PROCEDURE’, 8, ‘FUNCTION’, 9, ‘PACKAGE’,
11, ‘PACKAGE BODY’, 12, ‘TRIGGER’, 13, ‘TYPE’, 14, ‘TYPE BODY’,
‘UNDEFINED’),
s.line, s.source
from sys.obj$ o, sys.source$ s, sys.user$ u
where o.obj# = s.obj#
  and o.owner# = u.user#
  and ( o.type# in (7, 8, 9, 11, 12, 14) OR
       ( o.type# = 13