Reverses the effects of a DROP TABLE statement. Flashback Drop is built around a mechanism called the Recycle Bin, which Oracle uses to manage dropped database objects until the space they occupied is needed to store new data.
Flashback Drop is faster than other recovery mechanisms, such as point-in-time recovery, and does not lead to any loss of recent transactions or downtime.
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.
Limitations on Flashback Drop
- The recycle bin functionality is only available for non-system, locally managed tablespaces.
- There is no fixed amount of space allocated to the recycle bin, and no guarantee as to how long dropped objects remain in the recycle bin.
- You can perform Flashback Query on tables in the recycle bin, but only by using the recycle bin name. You cannot use the original name of the table.
- Due to security concerns, tables which have Fine-Grained Auditing (FGA) and Virtual Private Database (VPD) policies defined over them are not protected by the recycle bin.
- Partitioned index-organized tables are not protected by the recycle bin.
The recycle bin does not preserve referential constraints on a table. If a table had referential
- Constraints before it was dropped , then re-create any referential constraints after you retrieve the table from the recycle bin.
Privileges for Flashback Drop
Any user with drop privileges over the object can drop the object, placing it in the recycle bin.
FLASHBACK TABLE... TO BEFORE DROP
Privileges are tied to the privileges for DROP. That is, any user who can drop an object can perform Flashback Drop to retrieve the dropped object from the recycle bin.
Privileges are tied to the DROP privileges. Any user having DROP TABLE or DROP ANY TABLE privileges can purge the objects from the recycle bin.
SELECT for objects in the Recycle Bin
Users must have SELECT and FLASHBACK privileges over an object in the recycle bin to be able to query the object in the 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.
Flashback Drop Operations
Following operations can be performed on flashback drop.
- Enabling and Disabling Recycle Bin
- Viewing Objects in Recycle Bin
- Purging Objects from Recycle Bin
- Recover Table using Flashback Drop
- Flashback Drop on Tables in Recycle Bin
1- Enabling and Disabling Recycle Bin
The recycle bin is enabled by default. The initialization parameter RECYCLEBIN can be used to explicitly enable or disable the recycle bin.
You can also use an ALTER SYSTEM statement to change the value of the RECYCLEBIN parameter for the entire database. For example:
ALTER SYSTEM SET RECYCLEBIN=OFF|ON;
To specify recycle bin behavior for your own database session
ALTER SESSION SET RECYCLEBIN=OFF;
Objects you drop during this session are no longer placed in the recycle bin. However, other users continue to be protected by the recycle bin.
Note: Objects already in the recycle bin are not affected by enabling or disabling the recycle bin using ALTER SYSTEM or ALTER SESSION.
2- Viewing Objects in Recycle Bin
To view the contents of the recycle bin
Lets users see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN
Lets administrators see all dropped objects in the recycle bin
This example uses the views to determine the original names of dropped objects:
SELECT object_name AS recycle_name, original_name, type
3- Purging Objects from Recycle Bin
The PURGE command is used to permanently purge objects from the recycle bin. Once purged, objects can no longer be retrieved from the bin using Flashback Drop.
#purges individual table and all of its dependent objects from the recycle
PURGE TABLE EMP;
#Purge specific table
PURGE TABLE "BIN$KSD8DB9L345KLA==$0";
#If you have created and dropped multiple tables with the same orignal name,
CREATE TABLE EMP; # version 1 of the table
DROP TABLE EMP; # version 1 dropped
CREATE TABLE EMP; # version 2 of the table
DROP TABLE EMP; # version 2 dropped
CREATE TABLE EMP; # version 3 of the table
DROP TABLE EMP; # version 3 dropped
#If you execute PURGE TABLE EMP several times, the effect is as described
PURGE TABLE EMP; # version 1 of the table is purged
PURGE TABLE EMP; # version 2 of the table is purged
PURGE TABLE EMP; # version 3 of the table is purged
#To purge just an index , while keeping the base table:
PURGE INDEX "BIN$GTE72KJ22H9==$0";
# To purge all dropped tables and other dependent objects from a specific
PURGE TABLESPACE hr;
#To purge only objects from a tablespace belonging to a specific user
PURGE TABLESPACE hr USER scott;
# purges the contents of the recycle bin for the currently logged-in user.
#To purge all objects from the recycle bin DBA role is requied
4- Recover Table using Flashback Drop
For example, this statement places the EMPLOYEE_DEMO table, along with any indexes, constraints, or other dependent objects listed previously, in the recycle bin:
SQL> DROP TABLE EMPLOYEE_DEMO;
The table and its dependent objects will remain in the recycle bin until they are purged from the recycle bin. You can explicitly purge a table or other object from the recycle bin with the SQL*Plus PURGE statement
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 employee_demo PURGE;
Recycle bin objects are not counted as used space. If you query the space views to obtain the amount of free space in the database, objects in the recycle bin are counted as free space.
Dropped objects still appear in the views USER_TABLES, ALL_TABLES, DBA_TABLES, USER_INDEX, ALL_INDEX and DBA_INDEX. A new column, DROPPED, is set to YES for these objects. You can use the DROPPED column in queries against these views to view only objects that are not dropped.
To view only objects in the recycle bin, use the USER_RECYCLEBIN and DBA_RECYCLEBIN views
5- Flashback Drop on Tables in the Recycle Bin
The following example restores the BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 table, changes its name back to hr.int_admin_emp, and purges its entry from the recycle bin:
#restore form recycle bin with it original name
FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;
FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;
# Restore with new name
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP
RENAME TO hr.int2_admin_emp;
#Multiple Objects With the Same Original Name
CREATE TABLE EMP ( ...COLUMNS ); # EMP version 1
DROP TABLE EMP;
CREATE TABLE EMP ( ...COLUMNS ); # EMP version 2
DROP TABLE EMP;
CREATE TABLE EMP ( ...COLUMNS ); # EMP version 3
DROP TABLE EMP;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_3;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_2;
FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO EMP_VERSION_1;