How to retrieve a dropped package

by Prabakara
(Chennai,TamilNadu, India)


i have not enabled any flashblack options.
Is there any way to retrieve the dropped package.

The easiest way to restore a dropped package in Oracle is, obviously, to reload/recompile the source. If that is not possible, then restoration is a little trickier but not necessarily impossible.

One option that is possible for tables (and dependent objects) is to restore from the database recycle bin.

You can find out what's in the recycle bin with a query such as the following.

SELECT object_name, original_name, object_type FROM user_recyclebin;

You can also use the show recyclebin COMMAND in SQL*Plus.

To restore a dropped table you simply need to issue the following SQL statement.

FLASHBACK TABLE <table_name> TO BEFORE DROP; where <table_name> is the original name of the table to be restored.

Unfortunately Oracle's recycle bin only stores tables and dependent objects such as indexes, constraints and nested tables so you can't restore a dropped procedure from the recycle bin.

From Oracle 11g release 2 there is a feature called edition-based redefinition which is designed to be able to upgrade an application whilst it is in use. One aspect of this feature is that it enables you to have multiple versions (editions) of PL/SQL objects such as procedures and functions. Therefore if you drop an object such as a procedure in a child edition it will still be available in the parent edition.

Editions need to be enabled for a particular user by use of the CREATE USER or ALTER USER SQL commands. For example:

The root edition is called ora$base and child editions are created with the statement CREATE EDITION <edition_name> where <edition_name> is the name of the new edition.

All child editions automatically inherit all parent objects and any changes to editionable objects (packages, procedures, functions, types etc). To switch to the child edition you need to enter the command
ALTER SESSION SET EDITION = <edition_name> where <edition_name> is the name of the new edition. If you drop an object in the current child edition, assuming it was inherited from the parent edition and wasn't a new object, then it will still exist in the parent edition, therefore all you need to do is to switch back to the parent edition by using the ALTER SESSION command.


returns you to the root edition.

If you're not using editions or Oracle 11gR2 or later then the only other option is to recover from a backup. This could be a backup made using RMAN or datapump. If you only have a backup created by RMAN you would have to restore the database from the backup and then recover to the point in time before the package was dropped. However doing this means you would lose all changes made after the package was dropped. With a datapump export you can just import the dropped package without affecting else.

For information on using RMAN see the Oracle Database Backup and Recovery User's Guide and here for a short guide on data pump.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.