威凡网全力打造:网页编程、软件开发编程、平面设计、服务器端开发、操作系统等在线学习平台!学编程,上威凡网!
数据库>> Mysql Sqlserver Oracle SQlite Access Sybase SQL其他
当前位置:首页 > 数据库 > Oracle
上一节 下一节
 Oracle中如何恢复被删掉的存储过程?

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

  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


申明:本教程内容由威凡网编辑整理并提供IT程序员分享学习,如文中有侵权行为,请与站长联系(QQ:254677821)!
上一节 下一节
相关教程  
其他教程  
Mysql
Sqlserver
Oracle
SQlite
Access
Sybase
SQL其他

违法和不良信息举报中心】邮箱:254677821@qq.com
Copyright©威凡网 版权所有 苏ICP备2023020142号
站长QQ:254677821