RECYCLEBIN in Oracle

What is Recycle Bin
Oracle has introduced "Recycle Bin" Feature Oracle 10g to store all the dropped objects.
If any table in Oracle 10g is dropped then any associated objects to this table such as indexes,
constraints and other dependant objects are simply renamed with a prefix of BIN$$.

Why Recycle Bin
A user drops a very important table--accidentally, of course--and it needs to be revived as soon as possible.
Oracle9i Database introduced the concept of a Flashback Query option to retrieve data from a point in time in the past, but it can't flash back DDL operations such as dropping a table.
The only recourse is to use tables pace point-in-time recovery in a different database and then recreate
the table in the current database using export/import or some other method.
This procedure demands significant DBA effort as well as precious time, not to mention the use of a different database for cloning.
But with Oracle 10g Recycle bin Feature the user can easily restore the Dropped Object.

How to Enable/Disable Recycle Bin

The Following property sets the Recycle Bin to be enabled or not for the DB.

 
SQL > SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

Value
-----
On

if the Value Is “on” then recyclebin feature is enabled for the Database.
If the Value is “off” the recyclebin feature is disabled.

The following commands are used to enable or Disable the Feature

SQL > ALTER SYSTEM SET recyclebin = ON;
or
SQL > ALTER SESSION SET recyclebin = ON;

SQL > ALTER SYSTEM SET recyclebin = OFF;
or
SQL > ALTER SESSION SET recyclebin = OFF;

Show the Contents in RECYCLEBIN
Use the following commands to show all the objects that are stored in Recycle Bin,

SQL > SHOW RECYCLEBIN;
Or
SQL > SELECT * FROM USER_RECYCLEBIN;

Example
The Following Example explains the moving the object to the Recyclebin

SQL > CREATE TABLE TEST_RBIN(VAL   NUMBER);

SQL > INSERT INTO TEST_RBIN(VAL) VALUES(10);

SQL > COMMIT;

SQL > DROP TABLE TEST_RBIN;

Print the Recycle bin Entries,

SQL > SHOW RECYCLEBIN;

ORIGINAL NAME      RECYCLEBIN NAME                 OBJECT TYPE           DROP TIME
----------------   ------------------------------  --- ------------     -------------------
TEST_RBIN              BIN$7fq9jEy8RSadimoE4xGjWw==$0  TABLE          2010-05-26:11:27:12

Restore the Objects

User can restore the Dropped tables by issuing the following commands,
The following Commands can be used to restore the dropped Objects,

SQL > FLASHBACK TABLE <<Table_Name >> TO BEFORE DROP;

Example,

SQL > FLASHBACK TABLE TEST_RBIN TO BEFORE DROP;

SQL > SELECT * FROM TEST_RBIN;

       VAL
       ---
       10

It is possible to restore the table in to different name by issuing the following SQL Command,

SQL > FLASHBACK TABLE << Dropped Table Name >> TO BEFORE DROP RENAME TO <<New Table Name >>;

SQL > FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN1;

Using the above statement, its possible to restore the various version of the table data if the table
is created and Dropped more than once.

While restoring system restores the table in Descending order.

Example,

SQL > CREATE TABLE TEST_RBIN (COL1 NUMBER);

SQL > INSERT INTO TEST_RBIN VALUES (1);

SQL > COMMIT;

SQL > DROP TABLE TEST_RBIN;

SQL > CREATE TABLE TEST_RBIN (COL1 NUMBER);

SQL > INSERT INTO TEST_RBIN VALUES (2);

SQL > COMMIT;

SQL > DROP TABLE TEST_RBIN;

SQL > CREATE TABLE TEST_RBIN (COL1 NUMBER);

SQL > INSERT INTO TEST_RBIN VALUES (3);

SQL > COMMIT;

SQL > DROP TABLE TEST_RBIN;

SQL > SHOW RECYCLEBIN;

ORIGINAL NAME    RECYCLEBIN NAME            	     OBJECT TYPE      DROP TIME
 ---------------- ------------------------------  ------------     -------------------
TEST_RBIN        BIN$2e51YTa3RSK8TL/mPy+FuA==$0   TABLE        	2010-05-27:15:23:43
TEST_RBIN        BIN$5dF60S3GSEO70SSYREaqCg==$0   TABLE        	2010-05-27:15:23:43
TEST_RBIN        BIN$JHCDN9YwQR67XjXGOJcCIg==$0 TABLE        	2010-05-27:15:23:42

SQL > FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN1;

SQL > FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN2;

SQL > FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN3;

SQL > SELECT * FROM TEST_RBIN1;

      COL1
----------
         3
SQL > SELECT * FROM TEST_RBIN2;

      COL1
----------
         2

SQL > SELECT * FROM TEST_RBIN3;
      COL1
    ----------
         1

Note:
The un-drop feature brings the table back to its original name, but not the associated objects like
indexes and triggers, which are left with the recycled names. Sources such as views and
procedures defined on the table are not recompiled and remain in the invalid state.
These old names must be retrieved manually and then applied to the flashed-back table.

Clearing the Recycle Bin

To Clear the Recycle bin the following SQL Statements can be used.
To Clean only a particular table, the following Statement will be used,

SQL > PURGE TABLE << Table_Name >>

Its possible to Purge the table based on the System Generated table name also

SQL > PURGE TABLE << Table_Name >>

This command will remove the table and all dependent objects such as indexes, constraints,
and so on from the recycle bin, saving some space. To permanently drop an index from the recycle bin,
the following statement can be used

 

SQL > PURGE  INDEX  <<Index_Name >>;

This will remove the index only, leaving the copy of the table in the recycle bin.
Sometimes it might be useful to purge at a higher level. For instance, to purge all the objects in recycle bin
in a particular tablespace,

SQL > PURGE TABLESPACE <<Table Space Name>>;

It is possible to purge only the recycle bin for a particular user in that tablespace.
This approach could come handy in data warehouse-type environments where users create and drop many transient tables.:

SQL > PURGE TABLESPACE <<Table Space Name>> USER <<User Name>>;

To clear the complete recycle bin , the following statement can be used

SQL > PURGE Recyclebin;

It is possible to purge the table while dropping the table itself.
The following statement would be used to achieve this,

SQL > DROP TABLE  << Table_Name >> PURGE;

Example,
Create the Table and then Drop the table,

SQL >  CREATE TABLE TEST_RBIN (COL1 NUMBER);

SQL >  INSERT INTO TEST_RBIN VALUES (1);

SQL > COMMIT;

SQL > DROP TABLE TEST_RBIN;

View the recycle bin entries with show recycle bin option,

SQL > SHOW RECYCLEBIN;

ORIGINAL NAME    RECYCLEBIN NAME            	     OBJECT TYPE      DROP TIME
 ---------------- ------------------------------  ------------     -------------------
TEST_RBIN        BIN$2e51YTa3RSK8TL/mPy+FuA==$0   TABLE        	2010-05-27:15:23:43

Clear the table by executing the following Statement

SQL > PURGE TABLE TEST_RBIN; or

SQL > PURGE TABLE “BIN$2e51YTa3RSK8TL/mPy+FuA==$0”

Verify the Recycle bin, it will not have any entries for the table TEST_RBIN,

SQL > SHOW RECYCLEBIN;

Note:
Once the Table is purged from recycle bin, it will not be possible to restore by using FLASHBACK command.

Space/Quota Issue

Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides
to permanently remove them using the new PURGE command.
The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature.
Objects in the Recycle Bin will be automatically purged by the space reclamation process if

1. A user creates a new table or adds data that causes his/her quota to be exceeded.
2. The tablespace needs to extend its file size to accommodate create/insert operations