How do you recover deleted stored procedures in Oracle?

At some point, it's easy to accidentally delete stored procedures, so the stored procedures are deleted, how do we recover? Here for you to explain.

1. Create a test stored procedure

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 name= ' 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 ' and name= ' proc_test_drop ';

No rows selected

Sql>

--Using a flash back query for recovery

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 ',

One, ' PACKAGE body ', ' TRIGGER ', ' type ', ', ' type ',

' 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, one,) OR

(o.type# = 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 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>

--recovery from base tables

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 Name= ' PRO C_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>

++++ uses tools such as ODU to recover

Sys_source$.sql:

CREATE TABLE "SYS". source$ "

(

' obj# ' number is not NULL,

"Line" number is 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)

)

Change owner, and then load the data into the Roger user.

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)

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, 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