Oracle 11g : Using Flashback table and Managing the recycle bin

what is the recycle bin
Oracle says : "The recycle bin is actually a data dictionary table containing information about dropped objects.Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints."

Viewing the content of the recycle bin

SQL>select * from recyclebin;

SQL>desc RECYCLEBIN;
OBJECT_NAME      NOT NULL VARCHAR2(30)
ORIGINAL_NAME    VARCHAR2(32)
OPERATION        VARCHAR2(9)
TYPE             VARCHAR2(25)
TS_NAME          VARCHAR2(30)
CREATETIME       VARCHAR2(19)
DROPTIME         VARCHAR2(19)
DROPSCN          NUMBER
PARTITION_NAME   VARCHAR2(32)
CAN_UNDROP       VARCHAR2(3)
CAN_PURGE        VARCHAR2(3)
RELATED          NOT NULL NUMBER
BASE_OBJECT      NOT NULL NUMBER
PURGE_OBJECT     NOT NULL NUMBER
SPACE            NUMBER

Object naming in the recycle bin
When a dropped table is moved to the recycle bin, the table and its associated objects are given system-generated names.
Format of objects is : BIN$unique_id$version

where:

• unique_id is a 26-character globally unique identifier for this object, which makes the recycle bin name unique across all databases
• version is a version number assigned by the database

Enabling and Disabling the Recycle Bin
To disable the recycle bin, issue one of the following statements:

SQL>ALTER SESSION SET recyclebin = OFF;

or

SQL>ALTER SYSTEM SET recyclebin = OFF scope=SPFILE;

To enable the recycle bin, issue one of the following statements:

SQL>ALTER SESSION SET recyclebin = ON;

or

SQL>ALTER SYSTEM SET recyclebin = ON scope=SPFILE;

Remark :For modification at the system level, you need to restart your database.

Viewing the content of the recycle bin
2 views available :

• USER_RECYCLEBIN
• DBA_RECYCLEBIN

SQL>SELECT object_name, original_name FROM dba_recyclebin
   WHERE owner = 'XXX';

SQL>show recyclebin

Restoring table from recycle bin
Issue the following command :

FLASHBACK TABLE <my_table> TO BEFORE DROP;

If you want to rename in the same time the table :

SQL>FLASHBACK TABLE <my_table> TO BEFORE DROP RENAME TO <new_table_name>;

More reference on flashback database: see @ http://abcdba.com/abcdbabackupdataguardhowtoconfigureaflashbackdb