how to recover dropped package

Sometimes you drop something by accident – you know, the syndrome called "An Enter Too Far".

If you drop a table, the case is easy – you can use recyclebin to restore it. But when you drop a package or procedure… well, you have a problem 🙂

Of course, you could use a flashback query on the DBA_SOURCE view to check it’s previous contents, but sometimes you can have bad luck and your UNDO data can be out of date.

If this is your scenario, there is one more thing you can try – one year ago, I’ve created a tool called "RICO" https://blog.ora-600.pl/2015/09/03/rico/ for restoring data directly from the datafile.

This approach can be used for restoring dropped packages, procedures, functions, types, triggers…

All of them are stored as lines in table SOURCE$.

Let’s create a simple package:

SQL> ed
Wrote file afiedt.buf

  1  create or replace package pkg_skiper is
  2    procedure get_me_a_fish;
  3* end;
SQL> /

Package created.

SQL> get pkg_body
  1  create or replace package body pkg_skiper is
  2    procedure get_me_a_fish is
  3    begin
  4	  dbms_output.put_line('very good fish!');
  5    end;
  6* end;
SQL> /

Package body created.

We can see the contents of this package in SOURCE$ table and we check the block number in the SYSTEM tablespace in which we can find these data:

SQL> ;
  1  select dbms_rowid.rowid_block_number(s.rowid), dbms_rowid.rowid_row_number(s.rowid), s.*
  2  from dba_objects o, source$ s
  3  where o.object_id=s.obj#
  4* and  o.object_name=upper('pkg_skiper')
SQL> /

DBMS_ROWID.ROWID_BLOCK_NUMBER(S.ROWID) DBMS_ROWID.ROWID_ROW_NUMBER(S.ROWID)	  OBJ#	     LINE SOURCE
-------------------------------------- ------------------------------------ ---------- ---------- ------------------------------------------------------------
				100234					 40	 93342		1 package pkg_skiper is
				100234					 41	 93342		2   procedure get_me_a_fish;
				100234					 42	 93342		3 end;
				100234					 43	 93343		1 package body pkg_skiper is
				100234					 44	 93343		2   procedure get_me_a_fish is
				100234					 45	 93343		3   begin
				100234					 46	 93343		4      dbms_output.put_line('very good fish!');
				100234					 47	 93343		5   end;
				100234					 48	 93343		6 end;

9 rows selected.

As you can see – my package is stored in block 100234 of SYSTEM tablespace.

We can dump this piece of block to check its content:

BBED> d /v
 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_csw6so72_.dbf (1)
 Block: 100234  Offsets: 6103 to 6614  Dba:0x0041878a
-------------------------------------------------------
 2c000304 c30a222b 02c10216 7061636b l ,...�."+.�..pack
 61676520 706b675f 736b6970 65722069 l age pkg_skiper i
 730a2c00 0304c30a 222b02c1 031b2020 l s.,...�."+.�..
 70726f63 65647572 65206765 745f6d65 l procedure get_me
 5f615f66 6973683b 0a2c0003 04c30a22 l _a_fish;.,...�."
 2b02c104 04656e64 3b2c0003 04c30a22 l +.�..end;,...�."

Let’s check what will happen with this block after I drop this package:

BBED> d /v
 File: /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_csw6so72_.dbf (1)
 Block: 100234  Offsets: 6103 to 6614  Dba:0x0041878a
-------------------------------------------------------
 3c010304 c30a222b 02c10216 7061636b l <...�."+.�..pack
 61676520 706b675f 736b6970 65722069 l age pkg_skiper i
 730a3c01 0304c30a 222b02c1 031b2020 l s.<...�."+.�..
 70726f63 65647572 65206765 745f6d65 l procedure get_me
 5f615f66 6973683b 0a3c0103 04c30a22 l _a_fish;.<...�."
 2b02c104 04656e64 3b2c0003 04c30a22 l +.�..end;,...�."

As you can see, the only thing that has changed is the contents of the first 2 bytes (from 2C00 to 3C01). Value 3C means, that this row is deleted.
I’ve modified RICO to scan SYSTEM tablespace in search for all deleted rows from SOURCE$ table.

You can use it like this:

[oracle@rico ~]$ ./rico_source -h

        /@@@@@@@\
      (@@@@@ # @@@@@\
     (` \@@@@@@@@~~~~
    /`    \@@@@@|
   /@@     ''''  \
  /@@@@\          |
 /@@@@@@@\        |
/@@@@@@@@@        |
|@@@@@@@@         |
|@@@@@@@          |
|@@@@@@@          |
|@@@'@@@@        |
|@@@ '@@@        ;
|@@@  @@;       ;
|@@@  ''       ;
(@@@@         ;
 (@@@@        |
  (__@@_______)

ORA-600 [Database Whisperers] [Kamil Stawiarski]

THIS IS DEMO VERSION FOR READING DELETED ENTRIES FROM SOURCE$ ONLY. FOR FULL VERSION CONTACT kstawiarski@ora-600.pl (@ora600pl)
-f	Path to a datafile
-b	Block size
[oracle@rico ~]$ ./rico_source -b 8192 -f /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_csw6so72_.dbf > source.sql
[oracle@rico ~]$ tail -10 source.sql
package pkg_skiper is
  procedure get_me_a_fish;
end;
/
package body pkg_skiper is
  procedure get_me_a_fish is
  begin
     dbms_output.put_line('very good fish!');
  end;
end;[oracle@rico ~]$

Of course since the row has been marked as deleted, it can be reused by other data. So the question is… are you feeling lucky? 🙂

You can download rico_source.x86_64 compiled for Linux from here: http://ora-600.pl/oinstall/rico_source.x86_64

Thanks to Robert Tomaka for this idea.

Enjoy!

P.S.
Don’t forget to meet the best world Oracle experts – this year in Warsaw at poug.org !