展会信息港展会大全

Oracle中如何恢复被删掉的存储过程?
来源:互联网   发布日期:2016-01-28 13:07:05   浏览:2211次  

导读:/roger</p><p> Connected.</p><p> SQL CREATE OR REPLACE PROCEDURE proc_test_drop</p><p> 2 AS</p><p> 3 BEGIN</p><p> 4 FOR x IN (SELECT sysdate FROM dual)</p><p> 5 LOOP</p><p> 6 D...

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

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' andname='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' andname='PROC_TEST_DROP';

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 whereowner='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>

—-通过基表进行恢复

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-0602: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>

++++利用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#=529Cluster=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 Testingoptions

SQL> @ create.sql

Table created.

SQL> exit

赞助本站

人工智能实验室
AiLab云推荐
展开

热门栏目HotCates

Copyright © 2010-2024 AiLab Team. 人工智能实验室 版权所有    关于我们 | 联系我们 | 广告服务 | 公司动态 | 免责声明 | 隐私条款 | 工作机会 | 展会港