Home > Oracle > Recover a package – overwritten or dropped

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.

About these ads
  1. sampath
    December 6, 2011 at 12:00 pm

    Excellant idea

  2. Ravi de Silva
    April 19, 2012 at 8:34 am

    Hi, we can do this ONLY logging as SYS. How to recover from the very same schema it was overwritten??

  3. srikanth
    July 25, 2012 at 5:16 am

    My case is different ball game…..

    What would you suggest if the undo retention time has crossed.

    • July 30, 2012 at 4:31 am

      When you have crossed undo retention or more accurately UNDO is re-used, I do not think you can the flashback any table. So, this is not possible.

  1. May 31, 2012 at 4:56 am
  2. February 21, 2013 at 9:28 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: