当前位置:Gxlcms > mysql > Oracle中如何恢复被删掉的存储过程?

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

时间:2021-07-01 10:21:17 帮助过:64人阅读

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

人气教程排行