Flashback table – by default when a table is dropped oracle does not immediately remove the space, but renames it and places the table and associated objects in recycle bin.   This allows it to be recovered at a later time.

 

The recycle bin is a data dictionary table that contains info needed to recover dropped tables.  Original table stays in place and occupies same space until purged or database needs space for storage.

 

tablespaces are not placed in recycle bin and once dropped are gone.   All tables in that tablespace are removed from recycle bin if they have previously been dropped.

 

Recycle bin can be disabled with recyclebin initialization parameter.

 

FLASHBACK TABLE ….  TO BEFORE DROP;    statement is used to recover tables from recyclebin.  You must use the system generated recyclebin name.  You can rename the table with RENAME clause during recovery.  The USER_RECYCLEBIN view can be used to find the system generated recyclebin name.

example:

 

FLASHBACK TABLE test2_bak TO BEFORE DROP RENAME test2;

 

Larry Catt

OCP