The RECYCLEBIN 10g features

Since the 10g version, when you drop a table, you could see another table in the USER/ALL/DBA_TABLES views.
The name of this new table looks a little bit funny, like : BIN$B9405sOZRq2GkbE/z5Gwaw==$0

What is this ?

This is the new RECYCLEBIN feature that allows to keep a copy of the dropped table.
This copy could be restored later with the FLASHBACK new order.

If you want to drop the table with no undo possibility, a new keyword have been added to the DROP TABLE statement

DROP TABLE [ schema. ]table
   [ CASCADE CONSTRAINTS ]
   [ PURGE ] ;

PURGE

Specify PURGE if you want to drop the table and release the space associated with it in a single step.
If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin.

So, what is the recycle bin ?

SQL> desc recyclebin
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 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

SQL>

OBJECT_NAME contents a unique system generated name (because you can store several copies of the same object)

When you want to definitively drop these objects, you could use the PURGE statement:

PURGE
   { { TABLE table
     | INDEX index
     }
   | { RECYCLEBIN | DBA_RECYCLEBIN }
   | TABLESPACE tablespace
     [ USER username ]
   } ;

With the TABLE or INDEX keyword, you can choose a single table or index name

With the USER keyword, you can purge every copies of the object for the specified user

with the RECYCLEBIN keyword, you purge the current user's recycle bin content

With the DBA_RECYCLEBIN keyword, you can purge the whole content of the recycle bin (must have the SYSDBA system privilege)

with the TABLESPACE keyword, you could purge all the objects residing in the specified tablespace from the recycle bin

To remove the entire contents of your recycle bin, issue the following statement:

PURGE RECYCLEBIN;

Restore a dropped table

While the dropped table is present in the recycle bin, you can restore it with the FLASHBACK TABLE order:

FLASHBACK TABLE
   [ schema. ]table
     [, [ schema. ]table ]...
TO { { SCN | TIMESTAMP } expr
        [ { ENABLE | DISABLE } TRIGGERS ]
      | BEFORE DROP [ RENAME TO table ]
      } ;

You can restore from the recycle bin objects choosen by their table name, their SCN or TIMESTAMP value, or their last DROP TABLE order

To retrieve from the recycle bin a table that has been dropped, use the TO BEFORE DROP Clause.

SQL> select object_name, original_name, type from recyclebin ;

OBJECT_NAME                    ORIGINAL_NAME                    TYPE
------------------------------ -------------------------------- -------
BIN$B9405sOZRq2GkbE/z5Gwaw==$0 T                                TABLE

SQL>

SQL> flashback table t to before drop ;

Flashback complete.

SQL> select count(*) from t ;

  COUNT(*)
----------
         6

SQL>

SQL> select object_name, original_name, type from recyclebin ;

no rows selected

SQL>

Francois