Flashback Drop and Recycle bin...go hand in hand

Flashback drop is the remedy when someone accidently drops a table...and then says ohh! Shit

When you drop a table, the database does not immediately remove the space associated with the table. Instead, the table is renamed and, along with any associated objects, it is placed in the 

Recycle Bin of the database. The Flashback Drop operation recovers the table from the recycle bin.

Recycle Bin?

The recycle bin is a logical container for all dropped tables and their dependent objects. When a table is dropped, the database will store the table, along with its dependent objects in the recycle bin so that they can be recovered later. Dependent objects which are stored in the recycle bin include indexes, constraints, triggers, nested tables, LOB segments and LOB index segments.

For example, this statement places the FD table, along with any indexes, constraints, or other dependent objects listed previously, in the recycle bin:

SQL> DROP TABLE FD;

Table Dropped

If you are sure that you will not want to recover a table later, you can drop it immediately and permanently, instead of placing it in the recycle bin, by using the PURGE option of the DROP TABLE statement, as shown in this example:

DROP TABLE FD PURGE;

When a table and its dependent objects are moved to the recycle bin, they are assigned unique names, to avoid name conflicts

BIN$$globalUID$version

where:

globalUID is a globally unique, 24 character long identifier generated for the object.

version is a version number assigned by the database

The recycle bin name of an object is always 30 characters long.

USER_RECYCLEBIN

Lets users see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.

DBA_RECYCLEBIN

Lets administrators see all dropped objects in the recycle bin

There is no fixed amount of space pre-allocated for the recycle bin. Therefore, there is no guaranteed minimum amount of time during which a dropped object will remain in the recycle bin.

FLASHBACK TABLE FD TO BEFORE DROP;

Flashback Drop of Multiple Objects With the Same Original Name

You can create, and then drop, several objects with the same original name, and they will all be stored in the recycle bin. For example, consider these SQL statements:

SQL> create table fd (id number); ----------------->VERSION 1

Table created.

SQL> insert into fd values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table fd;

Table dropped.

SQL> create table fd (id number); ----------------->VERSION 2

Table created.

SQL> insert into fd values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table fd;

Table dropped.

SQL> create table fd (id number); ----------------->VERSION 3

Table created.

SQL> insert into fd values(3);

1 row created.

SQL> commit;

Commit complete.

SQL> drop table fd;

Table dropped.

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

FD               BIN$GGX7foNcRamWZpiyMB8Qfw==$0 TABLE        2011-04-07:11:47:41

FD               BIN$VXS3kNmYSDyuNREjJ+i7hg==$0 TABLE        2011-04-07:11:46:07

FD               BIN$NliVyLkSSHiQ/EGCiqY7hQ==$0 TABLE        2011-04-07:11:45:41

FVQ              BIN$eSpBwr8FSzaTPggDWK8taA==$0 TABLE        2011-04-06:15:02:38

SQL> flashback table fd to before drop;

Flashback complete.

SQL> select * from fd;

        ID

----------

         3

SQL> flashback table fd to before drop rename to fd1;

Flashback complete.

SQL> select * from fd1;

        ID

----------

         2

SQL> flashback table fd to before drop rename to fd3;

Flashback complete.

SQL> select * from fd3;

        ID

----------

         1

In such a case, each table FD is assigned a unique name in the recycle bin when it is dropped. You can use a FLASHBACK TABLE... TO BEFORE DROP statement with the original name of the table, as shown in the above example:

FLASHBACK TABLE FD TO BEFORE DROP;

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name.

The above example shows the retrieval from the recycle bin of all three dropped FD tables from the previous example, with each assigned a new name.

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO FD_3;

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO FD_2;

FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO FD_1;