使用的Oracle版本：11G R2
這裡我們使用測試來進行示範，第一步，先建立測試存儲過程，第二步，恢複删除後的存儲過程。
SQL建立測試存儲過程：SQL> conn gel/gel
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;
03-MAR-16
PL/SQL procedure successfully completed.
SQL>
SQL> l
1* select text,name from dba_source where owner=’GEL’ 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 gel/gel
Connected.
SQL> drop PROCEDURE proc_test_drop;
Procedure dropped.
SQL> select text,name from dba_source where owner=’GEL’ and name=’PROC_TEST_DROP’;
no rows selected
下面是恢複的方法，緊供大家參考：
方法1：利用閃回查詢進行恢複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 AND o.subname is null))
union all
select distinct u.name, o.name, ‘JAVA SOURCE’, s.joxftlno, s.joxf
tsrc
from sys.obj$ o, x$joxfs s, sys.user$ u
where o.obj# = s.joxftobn
and o.owner# = u.user#
and o.type# = 28
SQL> conn /as sysdba
Connected.
SQL> select text from dba_source as of timestamp sysdate-5/60/24 where owner=’GEL’ and name=’PROC_TEST_DROP’;
TEXT
—————————————————————–
PROCEDURE proc_test_drop
AS
BEGIN
FOR x IN (SELECT sysdate FROM dual)
LOOP
DBMS_OUTPUT.put_line (x.sysdate);
END LOOP;
END proc_test_drop;
8 rows selected.
閃回技術的其中一個功能就是：恢複丢失的資料、撤銷不正确的且已經送出的改變。
方法2：通過基表進行恢複SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2016-03-03 15:39:40
SQL> select obj# from obj$ as of timestamp to_timestamp('2016-03-03 15:34:00', ‘YYYY-MM-DD HH24:MI:SS’) where name=’PROC_TEST_DROP’;
OBJ#
———-
52148
SQL>
SQL> set long 9999999
SQL> select source
2 from source$ as of timestamp to_timestamp('2016-03-03 15:34:00', ‘YYYY-MM-DD HH24:MI:SS’)
3 where obj# = 52148
4 order by line;
SOURCE
————————————————————————————————————————–
PROCEDURE proc_test_drop
AS
BEGIN
FOR x IN (SELECT sysdate FROM dual)
LOOP
DBMS_OUTPUT.put_line (x.sysdate);
END LOOP;
END proc_test_drop;
8 rows selected.
SQL>
基表就是存儲Oracle資訊的底層表，通過基表的資料，也可以做到恢複。