Oracle中恢复被删掉的存储过程的案例

在某些时候，容易误删存储过程，那么针对存储过程被删除了，我们如何进行恢复呢 ？ 这里分享几种简单的处理方法！
首先我准备好一个测试用例，如下创建测试存储过程：
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>
如下是几种恢复的方法，供大家参考！
方法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 /*+ ordered */ 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=’ROGER’ 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.
SQL>
方法2：通过基表进行恢复
SQL> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
Session altered.
SQL> select sysdate from dual;
SYSDATE
——————-
2013-08-06 02:46:21
SQL> select obj# from obj$ as of timestamp to_timestamp(’2013-08-06 02:40: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(’2013-08-06 02:40: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>
方法3：利用主流数据抽取工具，例如odu等工具进行恢复
SYS_SOURCE$.sql：
CREATE TABLE “SYS”.”SOURCE$”
(
“OBJ#” NUMBER NOT NULL,
“LINE” NUMBER NOT NULL,
“SOURCE” VARCHAR2(4000)
);
SYS_SOURCE$.ctl:
–
–Generated by ODU,for table “SYS”.”SOURCE$”
–
OPTIONS(BINDSIZE=8388608,READSIZE=8388608,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE ‘SYS_SOURCE$.txt’ “STR X’0a’”
APPEND INTO TABLE “SYS”.”SOURCE$”
FIELDS TERMINATED BY X’7c’ TRAILING NULLCOLS
(
“OBJ#” ,
“LINE” ,
“SOURCE” CHAR(4000)
)
更改owner,然后将数据加载到roger用户中.
ODU> unload dict
CLUSTER C_USER# file_no: 1 block_no: 89
TABLE OBJ$ file_no: 1 block_no: 121
CLUSTER C_OBJ# file_no: 1 block_no: 25
CLUSTER C_OBJ# file_no: 1 block_no: 25
found IND$’s obj# 19
found IND$’s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found TABPART$’s obj# 266
found TABPART$’s dataobj#:266,ts#:0,file#:1,block#:2121,tab#:0
found INDPART$’s obj# 271
found INDPART$’s dataobj#:271,ts#:0,file#:1,block#:2161,tab#:0
found TABSUBPART$’s obj# 278
found TABSUBPART$’s dataobj#:278,ts#:0,file#:1,block#:2217,tab#:0
found INDSUBPART$’s obj# 283
found INDSUBPART$’s dataobj#:283,ts#:0,file#:1,block#:2257,tab#:0
found IND$’s obj# 19
found IND$’s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3
found LOB$’s obj# 151
found LOB$’s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6
found LOBFRAG$’s obj# 299
found LOBFRAG$’s dataobj#:299,ts#:0,file#:1,block#:2393,tab#:0
ODU> scan extent parallel 2
scan extent start: 2013-08-06 02:42:19
scanning extent…
scanning extent finished.
scan extent completed: 2013-08-06 02:43:23
ODU> unload table sys.source$
Unloading table: SOURCE$,object ID: 72
Unloading segment,storage(Obj#=72 DataObj#=72 TS#=0 File#=1 Block#=529 Cluster=0)
295765 rows unloaded
ODU> exit
[ora10g@killdb data]$ cp SYS_SOURCE$.sql create.sql
[ora10g@killdb data]$ sqlplus roger/roger
SQL*Plus: Release 10.2.0.5.0 – Production on Tue Aug 6 02:56:52 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @ create.sql
Table created.
SQL> exit