7.4 Oracle Flashback Drop: Undo a DROP TABLE Operation

Oracle Flashback Drop reverses the effects of a DROP TABLE operation. It can be used to recover after the accidental drop of a table. Flashback Drop is substantially faster than other recovery mechanisms that can be used in this situation, 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.

To understand how to use Oracle Flashback Drop, you must also understand how the recycle bin works, and how to access and manage its contents.

This section covers the following topics:

What is the Recycle Bin?
How Tables and Other Objects Are Placed in the Recycle Bin
Naming Convention for Objects in the Recycle Bin
Viewing and Querying Objects in the Recycle Bin
Recycle Bin Capacity and Space Pressure
Purging Objects from the Recycle Bin

7.4.1 What is 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.

7.4.2 How Tables and Other Objects Are Placed in the Recycle Bin

Tables are placed in the recycle bin along with their dependent objects whenever a DROP TABLE statement is executed. 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;
Table Dropped

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, as described in "Purging Objects from the Recycle Bin". 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;

Even if you do not purge objects from the recycle bin, the database purges objects from the recycle bin to meet tablespace space constraints. See "Recycle Bin Capacity and Space Pressure" for more details.

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, described later in this chapter.

7.4.3 Naming Convention for Objects in the Recycle Bin

When a table and its dependent objects are moved to the recycle bin, they are assigned unique names, to avoid name conflicts that may arise in the following circumstances:

A user drops a table, creates another with the same name, then drops the second table.
Two users have tables with the same name, and both users drop their tables.

The assigned names are globally unique and are used to identify the objects while they are in the recycle bin. Object names are formed as follows:

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.

Note that the globalUID used in the recycle bin name is not readily correlated with any externally visible piece of information about the object or the database.

7.4.4 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.

To enable the recycle bin , set the value of RECYCLEBIN to ON.To disable the recycle bin, set the value of RECYCLEBIN to OFF.

If you use a parameter file (PFILE) with your database, you can specify the value of RECYCLEBIN in the parameter file, as in this example:

# turn off recycle bin
RECYCLEBIN=OFF

This value applies to all database sessions.

To specify recycle bin behavior for your own database session, you can use an ALTER SESSION statement in SQL*Plus to change the value of the RECYCLEBIN parameter for your sessions. For example, this command disables the recycle bin for your database session:

ALTER SESSION SET RECYCLEBIN=OFF;

Objects you drop during this session are no longer placed in the recycle bin, until you re-enable the recycle bin using ALTER SESSION SET RECYCLEBIN=ON. However, other users continue to be protected by the recycle bin, and in future sessions the value reverts to the current default for the entire database.

You can also use an ALTER SYSTEM statement in SQL*Plus to change the value of the RECYCLEBIN parameter for the entire database. For example:

ALTER SYSTEM SET RECYCLEBIN=OFF;

This disables the recycle bin for all sessions, unless a user specifically enables the recycle bin for their session using ALTER SESSION SET RECYCLEBIN=ON.

Note:

Objects already in the recycle bin are not affected by enabling or disabling the recycle bin using ALTER SYSTEM or ALTER SESSION.

7.4.5 Viewing and Querying Objects in the Recycle Bin

To view the contents of the recycle bin, use the SQL*Plus command SHOW RECYCLEBIN.

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                   TYPE         DROP TIME
---------------- --------------------------------- ------------ -------------------
EMPLOYEE_DEMO    BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0  TABLE        2005-04-11:17:08:54

The ORIGINAL NAME column shows the orignal name of the object, while the RECYCLEBIN NAME column shows the name of the object as it exists in the recycle bin. Use the RECYCLEBIN NAME when issuing queries against tables in the recycle bin.

The database also provices two views for obtaining information about objects in the recycle bin:

View	Description
`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

This example uses the views to determine the original names of dropped objects:

SQL> SELECT object_name as recycle_name, original_name, type 
     FROM recyclebin;

RECYCLE_NAME                      ORIGINAL_NAME          TYPE
--------------------------------  ---------------------  ----------
BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0  EMPLOYEE_DEMO          TABLE
BIN$JKS983293M1dsab4gsz/I249==$0  I_EMP_DEMO             INDEX
BIN$NR72JJN38KM1dsaM4gI348as==$0  LOB_EMP_DEMO           LOB
BIN$JKJ399SLKnaslkJSLK330SIK==$0  LOB_I_EMP_DEMO         LOB INDEX

You can query objects that are in the recycle bin, just as you can query other objects, if these three conditions are met:

You must have the FLASHBACK privilege.
You must have the privileges that were required to perform queries against the object before it was placed in the recycle bin.
You must use the recycle bin name of the object in your query, rather than the object's original name.

This example shows the required syntax:

SQL> SELECT * FROM "BIN$KSD8DB9L345KLA==$0";

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

You can also use Oracle Flashback Query on tables in the recycle bin (again, assuming that you have the privileges described previously).

7.4.6 Recycle Bin Capacity and Space Pressure

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

The rules that govern how long an object is retained in the recycle bin and how and when space is reclaimed are explained in this section.

7.4.6.1 Understanding Space Pressure

Dropped objects are kept in the recycle bin until such time as no new extents can be allocated in the tablespace to which the objects belong without growing the tablespace. This condition is referred to as space pressure. Space pressure can also arise due to user quotas defined for a particular tablespace. A tablespace may have free space, but the user may have exhausted his or her quota on it.

Oracle never automatically reclaims space or overwrites objects in the recycle bin unless forced to do so in response to space pressure.

7.4.6.2 How the Database Responds to Space Pressure

When space pressure arises, the database selects objects for automatic purging from the recycle bin. Objects are selected for purging on a first-in, first-out basis, that is, the first objects dropped are the first selected for purging.

Actual purging of objects is done only as needed to meet ongoing space pressure, that is, the databases purges the minimum possible number of objects selected for purging to meet immediate needs for space. This policy serves two purposes:

It minimizes the performance penalty on transactions that encounter space pressure, by not performing more purge operations than are required;
It maximizes the length of time objects remain in the recycle bin, by leaving them there until space is needed.

Dependent objects such as indexes on a table are selected for purging before the associated table (or other required segment).

If space pressure is due to an individual user's quota on a tablespace being exhausted, the recycle bin purges objects belonging to the tablespace which count against that user's space quotas.

For AUTO EXTEND-able tablespaces, objects are purged from the recycle bin to reclaim space before datafiles are extended.

7.4.6.3 Recycle Bin Objects and Segments

The recycle bin operates at the object level, in terms of tables, indexes, and so on. An object may have multiple segments associated with it, such as partitioned tables, partitioned indexes, lob segments, nested tables, and so on. Because the database reclaims only the segments needed to immediately satisfy space pressure, it can happen that some but not all segments of an object are reclaimed. When this happens, any segments of the object not reclaimed immediately are marked as temporary segments. These temporary segments are the first candidates to be reclaimed the next time space pressure arises.

In such a case, the partially-reclaimed object can no longer be removed from the recycle bin with Flashback Drop. (For example, if one partition of a partitioned table is reclaimed, the table can no longer be the object of a Flashback Drop.)

7.4.7 Performing Flashback Drop on Tables in the Recycle Bin

Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name. This can be obtained from either the DBA_RECYCLEBIN or USER_RECYCLEBIN view as shown in "Viewing and Querying Objects in the Recycle Bin". To use the FLASHBACK TABLE ... TO BEFORE DROP statement, you need the same privileges you need to drop the table.

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:

FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;

Note the use of quotes, due to the possibility of special characters appearing in the recycle bin object names.You can also use the table's original name in the Flashback Drop operation:

FLASHBACK TABLE HR.INT_ADMIN_EMP TO BEFORE DROP;

You can assign a new name to the restored table by specifying the RENAME TO clause. For example:

FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP 
     RENAME TO hr.int2_admin_emp;

7.4.7.1 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:

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;

In such a case, each table EMP 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 this example:

FLASHBACK TABLE EMP TO BEFORE DROP;

The most recently dropped table with that original name is retrieved from the recycle bin, with its original name. You can retrieve it and assign it a new name using a RENAME TO clause. The following example shows the retrieval from the recycle bin of all three dropped EMP tables from the previous example, with each assigned a new name:

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;

Because using the original name in FLASHBACK TABLE... TO BEFORE DROP refers to the most recently dropped table with that name, the last table dropped is the first one to be retrieved.

You can also retrieve any table you want from the recycle bin, regardless of any collisions among original names, by using the table's unique recycle bin name.

7.4.8 Purging Objects from the 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.

There are a number of forms of the PURGE statement, depending on exactly which objects you want to purge from the recycle bin.

7.4.8.1 PURGE TABLE: Purging a Table and Dependent Objects

The PURGE TABLE command purges an individual table and all of its dependent objects from the recycle bin. This example shows the syntax, using the table's original name:

PURGE TABLE EMP;

You can also use the recycle bin name of an object with PURGE TABLE:

PURGE TABLE "BIN$KSD8DB9L345KLA==$0";

If you have created and dropped multiple tables with the same orignal name, then when you use the PURGE TABLE statement the first table dropped will be the one to be purged.

Note:

The behavior in this case is the opposite of the behavior of FLASHBACK TABLE... TO BEFORE DROP, where using the original name of the table retrieves the most recently dropped version from the recycle bin.

For example, consider the following series of CREATE TABLE and DROP TABLE statements:

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

There are now three EMP tables in the recycle bin. If you execute PURGE TABLE EMP several times, the effect is as described here:

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

7.4.8.2 PURGE INDEX: Freeing Space in the Recycle Bin

You can use PURGE INDEX to purge just an index for a table, while keeping the base table in the recycle bin. The syntax for purging an index is as follows:

PURGE INDEX "BIN$GTE72KJ22H9==$0";

By purging indexes from the recycle bin, you can reduce the chance of space pressure, so that dropped tables can remain in the recycle bin longer. If you retrieve a table from the recycle bin using Flashback Drop, you can rebuild the indexes after you retrieve the table.

7.4.8.3 PURGE TABLESPACE: Purging All Dropped Objects from a Tablespace

You can use the PURGE TABLESPACE command to purge all dropped tables and other dependent objects from a specific tablespace. The syntax is as follows:

PURGE TABLESPACE hr;

You can also purge only objects from a tablespace belonging to a specific user, using the following form of the command:

PURGE TABLESPACE hr USER scott;

7.4.8.4 PURGE RECYCLEBIN: Purging All Objects in a User's Recycle Bin

The PURGE RECYCLEBIN command purges the contents of the recycle bin for the currently logged-in user.

PURGE RECYCLEBIN;

It purges all tables and their dependent objects for this user, along with any other indexes owned by this user but not on tables owned by the user.

7.4.8.5 PURGE DBA_RECYCLEBIN: Purging All Recycle Bin Objects

If you have the SYSDBA privilege, then you can purge all objects from the recycle bin, regardless of which user owns the objects, using this command:

PURGE DBA_RECYCLEBIN;

7.4.8.6 Dropping a Tablespace, Cluster, User or Type and the Recycle Bin

When a tablespace is dropped including its contents, the objects in the tablespace are dropped immediately, and not placed in the recycle bin. Any objects in the recycle bin from the dropped tablespace are purged from the recycle bin.

If all objects from a tablespace have been placed in the recycle bin, then dropping the tablespace causes the objects to be purged, even if you do not use the INCLUDING CONTENTS clause with DROP TABLESPACE.

When a user is dropped, any objects belonging to the user that are not in the recycle bin are dropped immediately, not placed in the recycle bin. Any objects in the recycle bin that belonged to the user are purged from the recycle bin.

When you drop a cluster, all tables in the cluster are purged. When you drop a user-defined data type, all objects directly or indirectly dependent upon that type are purged.

7.4.9 Privileges and Security for Flashback Drop

This section summariezes the system privileges required for the operations related to Flashback Drop and the recycle bin.

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.
PURGE

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. Any users who had the SELECT privilege over an object before it was dropped continue to have the SELECT privilege over the object in the recycle bin.Users must have FLASHBACK privilege to query any object in the recycle bin, because these are objects from a past state of the database.

7.4.10 Limitations and Restrictions on Flashback Drop

The recycle bin functionality is only available for non-system, locally managed tablespaces. If a table is in a non-system, locally managed tablespace, but one or more of its dependent segments (objects) is in a dictionary-managed tablespace, then these objects are protected by the recycle bin.
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. Depending upon system activity, a dropped object may remain in the recycle bin for seconds, or for months.
While Oracle permits queries against objects stored in the recycle bin, you cannot use DML or DDL statements on objects 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.
A table and all of its dependent objects (indexes, LOB segments, nested tables, triggers, constraints and so on) go into the recycle bin together, when you drop the table. Likewise, when you perform Flashback Drop, the objects are generally all retrieved together.

It is possible, however, that some dependent objects such as indexes may have been reclaimed due to space pressure. In such cases, the reclaimed dependent objects are not retrieved from the recycle bin.
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 (though other constraints will be preserved if possible). If a table had referential constraints before it was dropped (that is, placed in the recycle bin), then re-create any referential constraints after you retrieve the table from the recycle bin with Flashback Drop.