Recycle Bin is one of the new feature introduced in Oracle 10g and works a little bit like the recycle bin in Windows.

Funtional point of view when this feature is enabled, any table the user drops, dropped table and its associated objects go into the recyclebin and can be restored from the recyclebin later unless objects are purge from the recycle bin.

Technical point of view when this feature is enabled, any table that user drops, it does not actually get dropped, Instead Oracle renames the table and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.

Each user has his/her own recycle bin.

This features can be enabled and disable through setting RECYCLEBIN initialization parameter to ON and OFF. By default it is ON in 10g.

Result of SELECT statement before drop

SQL> select tname from tab;

TNAME
——————————
DEPT
EMP
BONUS
SALGRADE
EMP1
EMP2
TEST

7 rows selected.

SQL> drop table test;

Table dropped.

Result of SELECT statement after drop

SQL> select tname from tab;

TNAME
——————————
DEPT
EMP
BONUS
SALGRADE
EMP1
EMP2
BIN$F3Anvs9/R8WQ5qynB5U1eQ==$0

7 rows selected.

See after DROP table TEST renamed to BIN$F3Anvs9/R8WQ5qynB5U1eQ==$0

Following statements shows two different ways to get information about dropped table.

SQL> select object_name, original_name, type, droptime from recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME
—————————— ——————————– ————————- ———-
BIN$A1hA+JCAQ9+UHORqytk5Hg==$0 UNIQUE_INDEX INDEX 2007-01-21:23:30:51
BIN$F3Anvs9/R8WQ5qynB5U1eQ==$0 TEST TABLE 2007-01-21:23:30:51

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEST BIN$F3Anvs9/R8WQ5qynB5U1eQ==$0 TABLE 2007-01-21:23:30:51

Following Statement shows how to recover drop table.

SQL> FLASHBACK TABLE TEST TO BEFORE DROP;

Flashback complete.

SQL> select tname from tab;

TNAME
——————————
DEPT
EMP
BONUS
SALGRADE
EMP1
EMP2
TEST

7 rows selected.

SQL> select object_name, original_name, type, droptime from recyclebin;

no rows selected

SQL> show recyclebin
SQL>

Following Statements demonstrate how to remove table test from recycle bin

SQL> drop table test;

Table dropped.

SQL> select object_name, original_name, type, droptime from recyclebin;

OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME
—————————— ——————————– ————————- ———-
BIN$eLg3SO8wS+SxW13qQ/BRdA==$1 BIN$A1hA+JCAQ9+UHORqytk5Hg==$0 INDEX 2007-01-21:23:36:29
BIN$pgSrvNNWS6CS/P6R8iRmEA==$0 TEST TABLE 2007-01-21:23:36:29

SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEST BIN$pgSrvNNWS6CS/P6R8iRmEA==$0 TABLE 2007-01-21:23:36:29

SQL> purge table test;

Table purged.

SQL> select object_name, original_name, type, droptime from recyclebin;

no rows selected

SQL> show recyclebin;
SQL>

Another way to remove entire content of recycle bin is as follows:
SQL> PURGE RECYCLEBIN;