Recover a package – overwritten or dropped

Last week, a developer reached me with a problem – a large package with 5000+ lines got over written by an old version.  Almost 2 months they worked on the new procedure, interesting there no having a copy of the new version. Now how to recover the procedure – thanks to Oracle for the various Flashback features.  Here is my test case – how did I undo the overwrite.

Below is the good procedure.

SQL> create or replace procedure myproc as
begin
dbms_output.put_line(‘Thia is a test Procedure’);
end;
/
  
Procedure created.
SQL> set serveroutput on
SQL> exec myproc
Thia is a test Procedure

PL/SQL procedure successfully completed.

Everything is good at this point.    To make the life easier, let me get the timestamp at this point where I have the good version of the procedure.

SQL> select to_char(sysdate,’dd-Mon-YYYY hh24:MI:SS’) from dual;

TO_CHAR(SYSDATE,’DD-
——————–
27-Apr-2011 12:50:14

Now I am going overwrite my procedure with a different one.

SQL> create or replace procedure myproc as
begin
dbms_output.put_line(‘Thia is my new procedure’);
end;
/  2    3    4    5

Procedure created.

SQL> exec myproc
Thia is my new procedure

PL/SQL procedure successfully completed.

 Now my actual procedure is overwritten with different copy, not the latest one and I want the previous good version.

SQL> select TEXT from dba_source where NAME=’MYPROC’ and owner=’TEST’;

TEXT
——————————————————————————–
procedure myproc as
begin
dbms_output.put_line(‘Thia is my new procedure’);
end;

I used SQL falshback – AS OF TIMESTAMP – to extract the previous version of the procedure.

SQL> select TEXT from dba_source  as of timestamp to_timestamp(’27-Apr-2011 12:50:13′,’DD-Mon-YYYY hh24:MI:SS’) where NAME=’MYPROC’ and owner=’TEST’;

TEXT
——————————————————————————–
procedure myproc as
begin
dbms_output.put_line(‘Thia is a test Procedure’);
end;

You can’t say wow for my 4 line procedure – but the developer was thrilled to recover his 5000+ lines good code.  You can also recover the dropped procedures.

SQL> drop procedure myproc;

Procedure dropped.

SQL> select TEXT from dba_source where NAME=’MYPROC’ and owner=’TEST’;

no rows selected
SQL> select TEXT from dba_source  as of timestamp to_timestamp(’27-Apr-2011 12:50:13′,’DD-Mon-YYYY hh24:MI:SS’) where NAME=’MYPROC’ and owner=’TEST’;

TEXT
——————————————————————————–
procedure myproc as
begin
dbms_output.put_line(‘Thia is a test Procedure’);
end;

And nevertheless to say, you can do these operations only as long as UNDO data is available.   Once the undo data is over written,  is again going to be different ball game.