What is Flashback drop in Oracle

This article is aimed at  explaining the ‘Flashback drop’ option and managing the ‘Oracle Recycle Bin’.

Flashback drop – A quick  intro:
• Have you  ever thought of restoring a  table which is dropped accidently?
• When you drop a table, the database does not immediately remove the space associated with the table.
• The database renames the table and places it and any associated objects in a ‘recycle bin’, where, in case the table was dropped accidently, it can be recovered at a later time. (Very similar to our ‘Restore’ from Windows Recycle bin)
• This feature is called Flashback Drop, and the ‘FLASHBACK TABLE’ statement is used to restore the table. This was introduced as a part of Oracle 10g.

Oracle Recycle Bin  – A quick  intro:
• The recycle bin is actually a ‘data dictionary table’ containing information about dropped objects.
• Dropped tables and any associated objects such as indexes, constraints etc. remain in the recycle bin.
• They continue to remain so, until specifically ‘purged’ from the recycle bin or the unlikely situation where they must be purged by the database because of table space constraints.
• Each user can be thought of as having his/her own recycle bin and he/she can view his/her dropped objects by using
 
SELECT * FROM RECYCLEBIN

What happens when a table is dropped?
• Dropped table is moved to the recycle bin.
• The table and its associated objects are given system-generated names. This is necessary to avoid name conflicts that may arise if multiple tables have the same name.
• This could happen when a user drops a table, re-creates it with the same name, and drops it again.
• The renaming convention is as follows:
     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:
• The recycle bin is ‘enabled’ by default. The initialization parameter RECYCLEBIN can be used to explicitly enable or disable the recycle bin by providing ON/OFF values.

To enable the recycle bin:
   ALTER SESSION SET RECYCLEBIN=ON; -- specific for that session
   ALTER SYSTEM SET RECYCLEBIN=ON;-- for the entire database
To disable the recycle bin:
   ALTER SESSION SET RECYCLEBIN=OFF; -- specific for that session
   ALTER SYSTEM SET RECYCLEBIN=OFF;-- for the entire database

Viewing and Querying objects from the Recycle Bin:

•Say we have dropped our ‘TEST1234’ table, we can get to know the name of the table in recycle bin by using

SELECT object_name AS recycle_name,original_name,type FROM RECYCLEBIN
WHERE original_name= ‘TEST1234’;

•We can directly view the contents of the dropped table by using the ‘system generated name’

SELECT * FROM "BIN$DMA758TvVdLgUyvulAqPqA==$0";

(Note the use of quotes due to the special characters in the recycle bin name)

Restoring an object from the Recycle Bin:

•We can either use the recycle_name to restore back the dropped tables,

FLASHBACK TABLE " BIN$DMA758TvVdLgUyvulAqPqA==$0"  TO BEFORE DROP;

•Or the Original name as well
FLASHBACK TABLE TEST1234 TO BEFORE DROP RENAME TO TEST1234_bkp;

Pre-requisites:
•Recycle bin should have been enabled for that session/system.
•Be granted with the FLASHBACK TABLE or FLASHBACK ANY TABLE privilege (Generally, they are enabled by default)

Limitations:
•While Oracle permits queries against objects stored in the recycle bin, we cannot use DML or DDL statements on objects in the recycle bin.
•When space pressure arises, the database selects objects for automatic purging from the recycle bin on a first-in, first-out basis. That is, the objects dropped first are the ones first selected for purging.