ARTICLES

Gee, I Wish I Hadn't Dropped That Table

By John Adolph Palinski

How many times have you dropped a development table and wished there was a Rollback command so the table could be restored?  In Oracle10g, the DBA or developer can now easily restore the dropped table.  This article explores the Flashback command and the Recyclebin that holds the dropped table.

Over the years most developers have dropped a table and wished they hadn't done so.  The dropped table may have been the wrong one, contained data that may take a great deal of time to recompile, or possibly you forgot the table's column names and wanted to do a Describe on the table before dropping it.  Regardless, once the Drop Table DDL command was issued, the table was gone.  In Oracle9i it is was possible but somewhat difficult to restore the dropped table.  Fortunately, Oracle has made it very easy to restore dropped tables in Oracle10g.

When a table is dropped in Oracle10g using the common "Drop Table table_name" command, Oracle does not remove the table from the tablespace.  The table is renamed with a system generated name.  If there are any associated indexes and triggers, they are also renamed with a system generated name.  Executing the "Select tname from tab" statement displays a reference to the dropped table.  The dropped table will be displayed using its new system generated name.  The dropped table can be differentiated from normal tables by the different looking name.  This is shown in Listing 1 .  The "Select tname from tab" statement is issued.  Two tables are displayed: Salesman_commissions and Test.  A Drop Table command is then issued dropping the Test table.  The "Select tname from tab" statement is then reissued.  Notice that the Test table is now called "BIN$G/UPwHtSR3KNd87PpvXVgw==$0"

Listing 1 - Dropping the Test table and displaying the Recyclebin

SQL> select tname from tab;

TNAME
------------------------------
SALESMAN_DEPENDENTS
TEST

SQL> drop table test;

Table dropped.

SQL> select tname from tab;

TNAME
------------------------------
SALESMAN_DEPENDENTS
BIN$G/UPwHtSR3KNd87PpvXVgw==$0

SQL>

The dropped table "BIN$G/UPwHtSR3KNd87PpvXVgw==$0" is in the Recyclebin.  It will remain in the Recyclebin until the tablespace runs out of free space or the Purge command (shown later) is executed.  The Recyclebin is actually a logical structure that catalogs dropped objects.  The dropped table still remains in the tablespace.

The Show Recyclebin command is used to display the contents of the Recyclebin.  This command shows the object's original name, the system generated name, and drop time.  The command is useful for identifying the dropped objects.  The Flashback command is used to restore the dropped object.  Both of these commands are illustrated in Listing 2.  The Show Recyclebin command displays the Recyclebin contents displaying the dropped Test table.  The Flashback command restores the Test table as is evident by the "Select tname from tab" command.

Listing 2 - Showing the contents of the Recyclebin and restoring the Test table

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$G/UPwHtSR3KNd87PpvXVgw==$0 TABLE        2004-08-30:19:15:53

SQL> flashback table test to before drop;

Flashback complete.

SQL> select tname from tab;

TNAME
------------------------------
SALESMAN_DEPENDENTS
TEST

SQL>

It is possible to drop the table without placing it into the Recyclebin.  Simply add the keyword Purge to the Drop table command as in the following:

Drop table test purge;

Objects can also be removed from the tablespace using the Purge command.  The following statement removes all objects from the Recyclebin:

Purge recyclebin;

Specific objects can be removed from the recycle bin by referencing the original object name or the system generated name as in the following:

Purge table test;

or

Purge table BIN$G/UPwHtSR3KNd87PpvXVgw==$0;

It is also possible to Purge tablespaces as in the following command:

Purge tablespace userdata;

Rather than purging the entire tablespace, you can purge a particular user in a tablespace.  The following is a sample command:

Purge tablespace userdata user huskers;

The dropped table can be restored with a different name using the Rename To keywords as in the following:

Flashback Table table test to before drop rename to test2;

or

Flashback Table table BIN$G/UPwHtSR3KNd87PpvXVgw==$0 to before drop rename to test2;

The Flashback Table restores the target table with its original name.  However, the associated objects such as indexes and triggers are restored with the Recyclebin names.  This is illustrated in Listing 3.  The Listing begins with a script to create an index called Test_ind on the Test table.  The Test table is then dropped.  The next listing statement displays the rows from the User_indexes data dictionary view.  Notice that the dropped index is displayed with the Recyclebin name.  You might also notice that the index's table name is the Recyclebin name.  The Test table is then restored using the Flashback Table command.  A final Select statement is executed against the User_indexes view.  Again notice that the Table_name value is restored to Test but the index remains the Recyclebin name. 

Listing 3 - Dropping the Test table and displaying the restored index name

SQL> create index test_ind on test (salesman_number);

Index created.

SQL> select table_name, index_name from user_indexes;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
TEST                           TEST_IND

SQL> drop table test;

Table dropped.

SQL> select table_name, index_name from user_indexes;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
BIN$O1A5fxMcTQSkGIqpVF/7qw==$0 BIN$CVGy9LkfQKq0V9znichlyw==$0

SQL> flashback table test to before drop;

Flashback complete.

SQL> select table_name, index_name from user_indexes;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
TEST                           BIN$CVGy9LkfQKq0V9znichlyw==$

It is even possible to query a dropped table.  Listing 4 illustrates a Select sttement referencing a Recyclebin table.  The Recyclebin table name is used in the From clause.  The dropped table did not have any records, thus no result set was returned.

Listing 4 - Executing a query against a Recyclebin table

SQL> select * from "BIN$G/UPwHtSR3KNd87PpvXVgw==$0";