在某些时候，容易误删存储过程，那么针对存储过程被删除了，，我们如何进行恢复呢 ? 这里为大家进行讲解。
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
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;
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 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;
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>
—-通过基表进行恢复
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;
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>
++++利用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
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！