欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle中如何恢复被删掉的存储过程?

程序员文章站 2022-06-02 14:37:48
...

在某些时候,容易误删存储过程,那么针对存储过程被删除了,我们如何进行恢复呢 ? 这里为大家进行讲解。 1. 创建测试存储过程 SQL conn roger/roger Connected. SQL CREATE OR REPLACE PROCEDURE proc_test_drop 2 AS 3 BEGIN 4 FOR x IN (SELECT sysdate FR

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

  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