Starting from 10g, The recyclebin is enabled by default and there are two recyclebin views a) USER_RECYCLEBIN and b)DBA_RECYCLEBIN. The synonym RECYCLEBIN points to session USER_RECYCLEBIN. To turn it on or off you need to change the RECYCLEBIN initialization parameter, at the session or system level.

When the recyclebin is enabled, any table that you drop is not actually gets dropped instead it is just renamed which beings with BIN$ and all its associated objects indexes, triggers, etc also gets a system generated name which also begins with BIN$.

A Simple example will be:

create table t1
(col1 varchar2(10),
col2 number);

insert into t1 values (‘a’,1);
insert into t1 values (‘b’,2);

Commit;

select * from t1;

COL1    COL2
—-    —-
a    1
b    2

DROP TABLE T1;

create table t1
(col1 varchar2(10),
col2 date);

insert into t1 values (‘a’,sysdate);
insert into t1 values (‘b’,sysdate);
insert into t1 values (‘c’,sysdate);

Commit;

select * from t1;

COL1    COL2
—–    ——————-
a    08/02/2011 04:17:55
b    08/02/2011 04:17:55
c    08/02/2011 04:17:55

DROP TABLE T1;

SQL> select object_name, original_name, operation from user_recyclebin;

OBJECT_NAME        ORIGINAL_NAME    OPERATION
———————  —————    ———-
BIN$GI74VOhyTp29f91G/w1VOg==$0    T1    DROP
BIN$LPbnFGYmST+6vTnvrdb9WQ==$0    T1    DROP

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

OBJECT_NAME    ORIGINAL_NAME    TYPE    UND    PUR    DROPTIME
————-    ————–    —-    —-    —    ———
BIN$GI74VOhyTp29f91G/w1VOg==$0    T1    TABLE    YES    YES    2011-02-08:04:23:01
BIN$LPbnFGYmST+6vTnvrdb9WQ==$0    T1    TABLE    YES    YES    2011-02-08:04:22:26

Table t1 was dropped, recreated then dropped again and both version were available in recyclebin. We can query these table just like any other tables

SQL> select * from “BIN$LPbnFGYmST+6vTnvrdb9WQ==$0″;

COL1    COL2
—-    —-
a    1
b    2

SQL> select * from “BIN$GI74VOhyTp29f91G/w1VOg==$0″;

COL1    COL2
—–    ——————-
a    08/02/2011 04:17:55
b    08/02/2011 04:17:55
c    08/02/2011 04:17:55

To drop table without having it in recyclebin use

SQL> DROP TABLE t1 purge;
Table dropped.

SQL> select * from user_recyclebin;
no rows selected

To purge table that was dropped earlier

SQL> PURGE TABLE “BIN$LPbnFGYmST+6vTnvrdb9WQ==$0″;
Table purged.

To remove or empty the user recyclebin use

SQL> PURGE recyclebin;
Recyclebin purged.

To remove or empty the system wide recyclebin use

SQL> PURGE DBA_RECYCLEBIN;
Recyclebin purged.