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