Oracle Recycle Bin

This is a new feature in Oracle10g.Prior to 10g,when you issue the DROP command
e.g. DROP table EMP;

The particular table is dropped and also its dependent objects (indexes,costraints and triggers,etc).
In Oracle 10g it will be renamed to a system generated string and logically placed in the recycle bin.Each dropped object will be placed in the recycle bin which is in the same tablespace the schema from which it was dropped from.

The recycle bin is actually a new data dictionary table(SYS.RECYCLEBIN$) that records information about dropped tables.When an object is dropped,Oracle just renames the table and all its associated objects (indexes, triggers,etc) to a system-generated name that begins with BIN$.
There are two recyclebin views, USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.

Users will retain all writes and privileges to the recyclebin object just as before the object was dropped.
There is no special storage for these dropped objects because it will still remain in the tablespace they were dropped from.
Dropped objects is still counted in the users's quota.

Here are some examples.Have fun!!!

SQL> create table t (name varchar2(8));
Table created.

SQL> insert into t values ('ali');
1 row created.

SQL> insert into t values ('abu');
1 row created.

SQL> select * from t;
NAME
--------
ali
abu

SQL> drop table t;
Table dropped.

SQL> select * from t;select * from t
*ERROR at line 1:ORA-00942: table or view does not exist

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

OBJECT_ NAME ORIGINAL_NAME TS_NAME DROPTIME
---------------------- ------------------- ------------ ---------------
BIN$DgYaFTOBTrScJ+2ba85IXA==$0 T USERS 2007-03-15:10:14:22

You can also type

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME-------------- --------------- --------------------- --------------- -------------
T BIN$DgYaFTOBTrScJ+2ba85IXA==$0 TABLE 2007-03-15:10:14:22


To query the content of a table in recyclebin

SQL> select * from "BIN$DgYaFTOBTrScJ+2ba85IXA==$0";
NAME
--------
ali
abu

to recover the dropped table
SQL> flashback table t to before drop;
Flashback complete.

SQL> select * from t;
NAME
--------
ali
abu

SQL> select * from recyclebin;
no rows selected

Some other commands
1.to recover the table to a different name
SQL> flashback table t to before drop rename to t1;

2. To drop permanently(do not go to recyclebin)
SQL> DROP table t purge;

When you issue the DROP TABLESPACE....INCLUDING CONTENTS,all objects will be drop and won't go to the recyclebin and all objects in the recyclebin will be removed.

Usually indexes,constraints and triggers should retain the original name after the dropped table is recovered but now the indexes has system generated names not original names.This is a bug in Oracle.