6.2. Using Flashback Drop

Flashback Drop is the process of saving a copy of the dropped database object and dependent objects in the Recycle Bin so that these objects can be recovered if necessary. The dropped database object is not removed from the database until the Recycle Bin is emptied. This provides a mechanism for a user to recover an accidental drop of a table. In previous releases of Oracle, you most likely needed to perform an incomplete recovery, import, or use Log Miner to recover dropped database objects. Flashback Drop is substantially faster than most other recovery mechanisms previously available. In addition, a Flashback Drop does not impact other users in the database to restore a dropped table, whereas incomplete recovery has database-wide impacts because there may be multiple database objects involved in a tablespace or datafile. Let's look at an example of the Flashback Drop capability that is performed by using the FLASHBACK TABLE table_name TO BEFORE DROP command.

SQL> flashback table t1
2 to before drop;

Flashback complete.
SQL>

Many times, a user can drop a table by mistake or drop the wrong table. The Flashback Drop is designed to temporarily store the dropped object and dependent objects for a period of time, which can be seen in the Recycle Bin. Users can then recover these objects if necessary from the Recycle Bin. Figure 6.1 illustrates how this activity can occur.

Figure 6.1. An overview of Flashback Drop

In order to fully comprehend Flashback Drop, you need to be aware of how the Recycle Bin works in detail. You should be aware of how the contents are stored in the Recycle Bin, including the naming conventions and versioning. You should understand space usage and limitations. In addition, you should be able to query and purge objects in the Recycle Bin. You will learn about all this in detail in the next section.

6.2.1. Understanding the Recycle Bin

The Recycle Bin is a logical storage container for all dropped tables and their dependent objects. As mentioned previously, when a table is dropped, the Recycle Bin stores the table and the table's dependent objects in the Recycle Bin so that these objects may be recovered later. The dependent objects that are stored in the Recycle Bin are indexes, constraints, triggers, nested tables, large binary object (LOB) segments, and LOB index segments. In the next few sections, we will focus on working with the Recycle Bin; the topics will include how objects are stored in the Recycle Bin, the naming conventions of objects, and querying objects. We will also discuss and demonstrate space pressure, purging objects in Recycle Bin, and space utilization. Let's look at these topics in more detail.

6.2.1.1. Working with the Recycle Bin

The Recycle Bin is enabled by default with Oracle 10g. The Recycle Bin receives database objects when the DROP TABLE command is executed. When this command is executed, tables and their dependent objects such as indexes are placed in the Recycle Bin. You need to use the Flashback Drop capability to recover these objects. The following example illustrates how this process works first with dropping the table T1:

SQL> select * from t1;

EMPLOYEE    SALARY
------------------
SCOTT        10000
SMITH        20000
JONES        15000

SQL> drop table T1;

Table Dropped

After the table is dropped, you can see the table and dependent objects in the Recycle Bin. These objects have a unique naming convention to support dropped objects of the same name by different users. It is conceivable that another user could drop a table with same name, so Oracle takes this into account. The naming convention consists of a globalUID, which is a unique, 24-character long identifier, and a version number assigned by the database. The formatting is displayed like BIN$globalUID$version. The Recycle Bin name for the dropped object is always 30 characters in length.

There is a special command to view the Recycle Bin contents: SHOW RECYCLEBIN. The Recycle Bin can also be queried with the dynamic views USER_RECYCLEBIN and DBA_RECYCLEBIN. Let's view the Recycle Bin with the new SHOW RECYCLEBIN command and perform a standard database query on the RECYCLEBIN view within SQL*Plus:

SQL> show recyclebin;

ORIG NAME RECYCLEBIN NAME                OBJ TYPE DROP TIME
--------- ----------------------------- -------- -------------
T1        BIN$0ZVR8eDEQbK4s8G2Csf2kg==$0 TABLE    2004-10-25:20:51:34

SQL>

Next, let's look at the view RECYCLEBIN to query the contents of the Recycle Bin with a standard SQL statement:

SQL> select object_name as recycle_name, original_name, object_name
  2 from recyclebin;

RECYCLE_NAME                   ORIG_NAME OBJECT_NAME
------------------------------ --------- ------------------------------
BIN$0ZVR8eDEQbK4s8G2Csf2kg==$0 T1        BIN$0ZVR8eDEQbK4s8G2Csf2kg==$0

The data within the dropped tables, which are accessed through the Recycle Bin, can be queried just like any other database object. In order to query the object in the Recycle Bin, you must have the privileges that were needed to perform queries on the object before the object was dropped and placed in the Recycle Bin. You also need the FLASHBACK privilege. Let's query an object in the Recycle Bin by using the OBJECT_NAME in the Recycle Bin for table T1:

SQL> select * from "BIN$0ZVR8eDEQbK4s8G2Csf2kg==$0";

EMPLOYEE                 SALARY
-------------------- ----------
SCOTT                    10000
SMITH                    20000
JONES                    15000

SQL>

Using Flashback Drop to recover the table T1 is a fairly straightforward process. The Flashback Drop simply undoes the table drop command and removes the object from the Recycle Bin. This Flashback Drop is performed by specifying the Recycle Bin OBJECT_NAME in the RECYCLEBIN view of the dropped table with the FLASHBACK TABLE table_name TO BEFORE DROP command. We added the RENAME TO table_name option to change the original table name from T1 to T2. You can also see that the object is no longer in the Recycle Bin by performing a SHOW RECYCLEBIN command. When a Flashback Drop is performed, the object is removed from the Recycle Bin.

Let's walk through using a Flashback Drop to recover the dropped table T1 from the first example and rename the table to T2:

SQL> flashback table "BIN$0ZVR8eDEQbK4s8G2Csf2kg==$0" to before drop
  2 rename to t2;

Flashback complete.

SQL> select * from t2;

EMPLOYEE                 SALARY
-------------------- ----------
SCOTT                     10000
SMITH                     20000
JONES                     15000

SQL> show recyclebin;
SQL>

As you can see, using the Recycle Bin and working with Flashback Drop is a fairly straightforward process. Tables and their dependent objects are stored in the Recycle Bin automatically in Oracle 10g once they are dropped. These objects can be recovered with the Flashback Drop operation.

The next section discusses and performs activities with the Recycle Bin in more detail. These Recycle Bin activities impact day-to-day operations such as space usage and maintenance operations.

6.2.1.2. Recycle Bin and Space Utilization

The Recycle Bin requires greater space utilization in the database, because dropped objects are still being stored after they have been dropped. This means that the original space allocation for the dropped tables and the dependent objects is maintained for extended periods of time in the original tablespace. The amount of space consumed by the dropped objects still counts against your tablespace quota.

There is a method to drop the objects from the Recycle Bin and to deallocate the space associated with the object all in one action. Of course, this means that you will not be able to rebuild this object if it has been removed from Recycle Bin. To permanently remove the object from the Recycle Bin and deallocate the space, you use the PURGE TABLE original_table_name command. This command provides the same functionality as the DROP TABLE command in Oracle releases prior to Oracle 10g.

You can also purge the object from the Recycle Bin with the PURGE TABLE recycle_bin_object_name command. The following command purges the table T1 after it has been placed in the Recycle Bin:

SQL> purge table "BIN$0ZVR8eDEQbK4s8G2Csf2kg==$0"

Table purged.

There is also a command that purges all the objects from a specified tablespace in the Recycle Bin. This command is the PURGE TABLESPACE command. The PURGE TABLESPACE command purges all dropped tables and dependent objects from the tablespace. Dependent objects such as LOBs, nested tables, and partitions will be purged from the specified tablespace, as well as the base table stored in a different tablespace because the dependent objects are dependent on the base table. The PURGE TABLESPACE tablespace USER user command removes only the tablespace contents of the specified username from the Recycle Bin. The following example purges the tablespace USERS with the user TEST from the Recycle Bin:

SQL> purge tablespace users user test;

Tablespace purged.

The command DROP USER user CASCADE drops the specified user and all of the objects owned by that user. The objects owned by the user are not placed in the Recycle Bin. Also, objects that are in the Recycle Bin and belong to the user are dropped. This DROP USER command bypasses the Recycle Bin and removes the objects immediately.

The contents of the Recycle Bin can be purged if desired. There are two commands that perform this capability:

• The PURGE RECYCLEBIN command is used to purge your own Recycle Bin. This command removes all objects from your Recycle Bin and deallocates all space associated with those objects.

• The PURGE DBA_RECYCLEBIN command removes all objects from all users' Recycle Bins. This effectively removes the Recycle Bin completely. You must have the SYSDBA system privilege to issue this command.

Let's see examples of these two commands and how they purge the Recycle Bin:

SQL> purge recyclebin;

Recyclebin purged.

SQL> connect / as sysdba;

Connected.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Extents for the dropped database object are not deallocated until you purge the object or the tablespace runs out of free space.

The Recycle Bin is a logical storage container of the dropped objects based on existing allocated space in tablespaces. This means there is no preallocated space set aside for the Recycle Bin. This makes the Recycle Bin space dependent on the space available in the existing tablespaces. Therefore, the minimum time an object is stored in the Recycle Bin cannot be guaranteed.

The dropped objects in the Recycle Bin are kept in the Recycle Bin until new extents cannot be allocated in the tablespace to which the dropped objects belong. This situation is referred to as space pressure. User space quotas can also force the space pressure situation. Even though there is free space in the tablespace, the user's quota limits the space that can be utilized.

When space pressure arises, Oracle automatically reclaims space by overwriting objects in the Recycle Bin. Oracle selects certain objects for purging based on a first-in, first-out (FIFO) basis, so the first objects dropped are selected for purging. The purging of objects is done only as needed to meet space pressure, so the database purges as few as objects as possible to meet the space pressure needs. This minimalist approach of overwriting objects reduces performance impacts on transactions that encounter space pressure and maximizes the length of time a object can be available in the Recycle Bin.

Tablespaces that are configured with AUTO EXTEND storage requirements are purged from the Recycle Bin before the datafiles are extended for the tablespace.

As you can see, the Recycle Bin is a valuable new feature in Oracle 10g. The Recycle Bin reduces the need to perform incomplete recovery. It also reduces some of the workload on you because users can Flashback Drop their own objects. In previous versions, you needed to perform incomplete recovery or use the Import utility to rebuild a dropped table. This was a time-consuming process for you and other IT administration staff.

6.2.2. Limitations on Flashback Drop and the Recycle Bin

There are some limitations to the Flashback Drop that impact how you can use this technology, and there are certain objects that are excluded from the protection of the Recycle Bin. Be aware of the following limitations:

• Recycle Bin functionality is available only for non-system, locally managed tablespaces. If the table is a non-system, locally managed tablespace and some of the dependent objects are in a dictionary-managed tablespace, these dependent objects would be protected.

• There is no guaranteed timeframe for how long an object will be stored in the Recycle Bin. The time is determined by system activity that impacts space utilization.

• Data Manipulation Language (DML) or Data Definition Language (DDL) statements cannot be used on objects in the Recycle Bin.

• The Recycle Bin name of a table is required to query the table, not the original name.

• All dependent objects are retrieved when you perform a Flashback Drop, just as the objects are all added to the Recycle Bin when the base table is dropped. The only exception is when space pressure removes some dependent objects.

• Virtual Private Database (VPD) and fine-grained auditing (FGA) policies defined on tables are not protected for security reasons.

• Partitioned index-organized tables are not protected by the Recycle Bin.

Referential constraints are not protected by the Recycle Bin. Referential constraints must be replaced after the table has been rebuilt with the Flashback Drop.

As you can see, these limitations should be clearly understood. If you understand these limitations, you can more effectively utilize the Flashback Drop procedures and the Recycle Bin. As long as you also know the restrictions of the Flashback Drop technology, other alternatives can be made to support your database.