Oracle Flashback Drop and Recycle bin

The Oracle 10g provides the ability to reinstating an accidentally dropped table, which is called Flashback Drop.

When a table is dropped, the database does not immediately remove the space associated with that table. Instead, the table is renamed to a system-defined name and placed in the Recycle bin. The flashback drop operations recovers from recycle bin.

SQL> DROP TABLE employee_tbl;

You can see the system-defined name of the table.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$gXxxELu7aQ/gQAoKd5l2Hg==$0 TABLE

You can see the dropped table in the recycle bin using
SQL> show Recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
-------------- ------------------------------ ------------ ----------------
EMPLOYEE_TBL BIN$gXxxELu7aQ/gQAoKd5l2Hg==$0 TABLE 2010-03-01:09:10:00

Recover the dropped table from Recycle bin using
SQL> FLASHBACK TABLE employee_tbl TO BEFORE DROP;

Recover the dropped table from Recycle bin with new name using
SQL> FLASHBACK TABLE employee_tbl TO BEFORE DROP RENAME TO employee_tmp;

You can also drop a table completely with out needing a flashback using
SQL> DROP TABLE employee_tbl PURGE;

The tablespace will not free up space until the table will remain until recycle bin is purged. You can purge table explicitly from recycle bin using
SQL> PURGE TABLE employee_tbl;

Purge all tables in recycle bin using
SQL> PURGE RECYCLEBIN;

As a DBA can purge all the objects in any tablespace using
SQL> PURGE DBA_RECYCLEBIN;

Purge all the objects in recycle bin in a tablespace using
PURGE TABLESPACE employee_tbs;

Purge all the objects in recycle bin in a tablespace specific to a user only using
PURGE TABLESPACE employee_tbs USER emp_user;

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com