Recover drop procedure in Oracle

With the help of oracle Flashback feature, you can recover the drop procedure, packages, index and tables .

What is Oracle Flashback ?
Oracle Flashback is oracle database feature that let you view past states of database obects or return database objects to a previous state without using media recovery.
With this feature you can do many things like

• Get past data.
• Recover table or rows to previous point.
• Get metadata that show a history of changes to the database

This flashback feature is depend on Automatic Undo Management system to obtain historic data. If you execute a UPDATE statement to change name from javaa-latte to java-latte, then oracle will store the value javaa-latte in undo data.

How to check whether Automatic Undo Management is enable or not ?
SQL> show parameters undo


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management     string AUTO If AUTO or null, enables automatic undo management
undo_retention     integer 900  minimum undo retention period (in seconds)  15 minutes
undo_tablespace     string UNDOTBS1 Optional, and valid only in automatic undo management mode

What Is the Recycle Bin?
The recycle bin is actually a data dictionary table containing information about dropped objects.Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space

How to enable/disable recycle bin ? 

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

ALTER SESSION SET recyclebin = ON;
ALTER SYSTEM SET recyclebin = ON;


When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names.
This could occur under the following circumstances:

• A user drops a table, re-creates it with the same name, then drops it again.
• Two users have tables with the same name, and both users drop their tables.

The renaming convention is as follows:

BIN$unique_id$version

USER_RECYCLEBIN This view can be used by users to see their own dropped objects in    the recycle bin. It has a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN This view gives administrators visibility to all dropped objects in the recycle bin

How to recover drop table?

SQL> create table abcd( java_latte varchar2(20));

Table created.
SQL> insert into abcd values('java-latte');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from abcd;
JAVA_LATTE
-------------------
java-latte
SQL> drop table abcd;
Table dropped.
SQL> commit;
Commit complete.

Now I'm using recycle bin to recover and see the content :

SQL> SELECT object_name, original_name FROM user_recyclebin;
OBJECT_NAME       ORIGINAL_NAME
------------------------------ --------------------------------
BIN$2qPsy7bMM4fgQ8QXCgrwdQ==$0 ABCD
SQL> select * from "BIN$2qPsy7bMM4fgQ8QXCgrwdQ==$0";
JAVA_LATTE
--------------------
java-latte
SQL> FLASHBACK TABLE abcd TO BEFORE DROP   RENAME TO abcd_new;
Flashback complete.
SQL> select * from abcd_new;
JAVA_LATTE
--------------------
java-latte

How to recover drop procedure or package?
SQL> create or replace procedure java_latte 
as
name varchar2(20):='JAVA_LATTE';
begin
dbms_output.put_line('name-'||name);
END;
 /
Procedure created.
SQL> commit;
Commit complete.
SQL>select OBJECT_NAME,OBJECT_TYPE from user_objects where object_name ='JAVA_LATTE';
OBJECT_NAME OBJECT_TYPE
-------------- --------------
JAVA_LATTE      PROCEDURE
SQL> drop procedure java_latte;
Procedure dropped.
SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'dd-Mon-YYYY hh24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD-
18-Apr-2013 19:44:37

Now I'm using AS OF TIMESTAMP to recover the drop procedure

SQL> select TEXT from dba_source  as of timestamp to_timestamp('18-Apr-2013 19:30:37','dd-Mon-YYYY hh24:MI:SS') where name='JAVA_LATTE';

TEXT
procedure java_latte
as
name varchar2(20):='JAVA_LATTE';
begin
dbms_output.put_line('name-'||name);
END;
6 rows selected.


if you find this information useful, please comment.