Using Oracle's recycle bin

; in this article, I'll cover some of the more subtle aspects of the recyclebin.

First, a quick review of the basics. There are two recyclebin views: USER_RECYCLEBIN and DBA_RECYCLEBIN. For convenience, the synonym RECYCLEBIN points to your USER_RECYCLEBIN. The recyclebin is enabled by default in 10g, but you can turn it on or off with the RECYCLEBIN initialization parameter, at the system or session level.

When the recyclebin is enabled, any tables that you drop do not actually get deleted. Instead, when you drop a table, Oracle just renames the table and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.

For example, consider this simple table:

SQL> create table tst (col varchar2(10), row_chng_dt date);

 

Table created.

 

SQL> insert into tst values ('Version1', sysdate);

 

1 row created.

 

SQL> select * from tst ;

 

COL        ROW_CHNG

---------- --------

Version1   16:10:03

If the RECYCLEBIN initialization parameter is set to ON (the default in 10g), then dropping this table will place it in the recyclebin:

SQL> drop table tst;

 

Table dropped.

 

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime

  2  from recyclebin

SQL> /

 

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME

------------------------------ ------------- ----- --- -------------------

BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:10:12

All that happened to the table when we dropped it was that it got renamed. The table data is still there and can be queried just like a normal table:

SQL> alter session set nls_date_format='HH24:MI:SS' ;

 

Session altered.

 

SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

 

COL        ROW_CHNG

---------- --------

Version1   16:10:03

Since the table data is still there, it's very easy to "undrop" the table. This operation is known as a "flashback drop". The command is FLASHBACK TABLE... TO BEFORE DROP, and it simply renames the BIN$... table to its original name:

SQL> flashback table tst to before drop;

 

Flashback complete.

 

SQL> select * from tst ;

 

COL        ROW_CHNG

---------- --------

Version1   16:10:03

 

SQL> select * from recyclebin ;

 

no rows selected

It's important to know that after you've dropped a table, it has only been renamed; the table segments are still sitting there in your tablespace, unchanged, taking up space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime

  2  from recyclebin

SQL> /

 

OBJECT_NAME                    ORIGINAL_NAME TYPE                      UND PUR DROPTIME

------------------------------ ------------- ------------------------- --- --- -------------------

BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE                     YES YES 2006-09-01:16:10:12

 

SQL> purge table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" ;

 

Table purged.

 

SQL> select * from recyclebin ;

 

no rows selected

You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER .

Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.

Just as you can wind up with several versions of a file with the same name in the Windows recycle bin, you can wind up with several versions of a table in the Oracle recyclebin. For example, if we create and drop the TST table twice, we'll have two versions in the recyclebin:

SQL> create table tst (col varchar2(10), row_chng_dt date);

 

Table created.

 

SQL> insert into tst values ('Version1', sysdate);

 

1 row created.

 

SQL> drop table tst;

 

Table dropped.

 

SQL> create table tst (col varchar2(10), row_chng_dt date);

 

Table created.

 

SQL> insert into tst values ('Version2', sysdate);

 

1 row created.

 

SQL> drop table tst;

 

Table dropped.

 

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime

  2  from recyclebin;

 

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME

------------------------------ ------------- ----- --- --- -------------------

BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:10:12

BIN$HGnc55/8rRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:19:53

Query the two dropped tables to verify that they are different:

SQL> select * from "BIN$HGnc55/7rRPgQPeM/qQoRw==$0";

 

COL        ROW_CHNG

---------- --------

Version1   16:10:03

 

SQL> select * from "BIN$HGnc55/8rRPgQPeM/qQoRw==$0" ;

 

COL        ROW_CHNG

---------- --------

Version2   16:19:45

If we issue a FLASHBACK DROP command for TST, which version will Oracle restore?

SQL> flashback table tst to before drop;

 

Flashback complete.

 

SQL> select * from tst;

 

COL        ROW_CHNG

---------- --------

Version2   16:19:45

Oracle always restores the most recent version of the dropped object. To restore the earlier version of the table, instead of the later one, we can either keep flashing back until we hit the version we want, or we can simply refer to the correct version of the table by using its new BIN$... name. For example, dropping TST once more gives us two versions in the recyclebin again:

SQL> drop table tst;

 

Table dropped.

 

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime

  2  from recyclebin;

 

OBJECT_NAME                    ORIGINAL_NAME TYPE   UND PUR DROPTIME

------------------------------ ------------- ------ --- --- -------------------

BIN$HGnc55/7rRPgQPeM/qQoRw==$0 TST           TABLE  YES YES 2006-09-01:16:10:12

BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST           TABLE  YES YES 2006-09-01:16:21:00

To flashback to the first version, refer to the BIN$... name of the first version of TST:

SQL> flashback table "BIN$HGnc55/7rRPgQPeM/qQoRw==$0" to before drop;

 

Flashback complete.

 

SQL> select * from tst;

 

COL        ROW_CHNG

---------- --------

Version1   16:10:03

The second version is still hanging out in the recyclebin:

SQL> select object_name, original_name, operation, can_undrop as "UND", can_purge as "PUR", droptime

  2  from recyclebin;

 

OBJECT_NAME                    ORIGINAL_NAME  OPERATION UND PUR DROPTIME

------------------------------ -------------- --------- --- --- -------------------

BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST            DROP      YES YES 2006-09-01:16:21:00

In a modern relational database, few tables stand alone. Most will have indexes, constraints, and/or triggers. Dropping a table also drops these dependent objects. When you drop a table with the recyclebin enabled, the table and its dependent objects get renamed, but still have the same structure as before. The triggers and indexes get modified to point to the new BIN$ table name. (Any stored procedures that referenced the original object, though, are invalidated.) For example:

SQL> truncate table tst;

 

Table truncated.

 

SQL> insert into tst values ('Version3', sysdate);

 

1 row created.

 

SQL> create index ind_tst_col on tst(col);

 

Index created.

 

SQL> select * from tst;

 

COL        ROW_CHNG

---------- --------

Version3   16:26:10

 

SQL> drop table tst ;

 

Table dropped.

 

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime

  2  from recyclebin

  3  order by droptime;

 

OBJECT_NAME                    ORIGINAL_NAME  TYPE   UND PUR DROPTIME

------------------------------ -------------- ------ --- --- -------------------

BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST            TABLE  YES YES 2006-09-01:16:21:00

BIN$HGnc55//rRPgQPeM/qQoRw==$0 TST            TABLE  YES YES 2006-09-01:16:27:36

BIN$HGnc55/+rRPgQPeM/qQoRw==$0 IND_TST_COL    INDEX  NO  YES 2006-09-01:16:27:36

The RECYCLEBIN views have a few other columns that make the relationship between TST and IND_TST_COL clear:

SQL> select object_name, original_name, type, can_undrop as "UND",

  2  can_purge as "PUR", droptime, base_object, purge_object

  3  from recyclebin

  4  order by droptime;

 

OBJECT_NAME                    ORIGINAL_NAME   TYPE  UND PUR DROPTIME            BASE_OBJECT   PURGE_OBJECT

------------------------------ --------------- ----- --- --- ------------------- -----------   ------------

BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST             TABLE  YES YES 2006-09-01:16:21:00 233032        233032

BIN$HGnc55//rRPgQPeM/qQoRw==$0 TST             TABLE  YES YES 2006-09-01:16:27:36 233031        233031

BIN$HGnc55/+rRPgQPeM/qQoRw==$0 IND_TST_COL     INDEX  NO  YES 2006-09-01:16:27:36 233031        233434

The PURGE_OBJECT column is the object number of the object itself; eg. the object number of IND_TST_COL is 233434. Note the value of the BASE_OBJECT column for IND_TST_COL: 233031, the object number of the associated version of the TST table.

If we FLASHBACK DROP the TST table, its index will be restored - but Oracle will not rename it to its original name. It will retain its BIN$.. name:

SQL> flashback table tst to before drop;

 

Flashback complete.

 

SQL> select * from tst ;

 

COL        ROW_CHNG

---------- --------

Version3   16:26:10

 

SQL> select index_name from user_indexes where table_name='TST' ;

 

INDEX_NAME

------------------------------

BIN$HGnc55/+rRPgQPeM/qQoRw==$0

I'm not sure why Oracle bothers storing the index's original name, since it doesn't seem to be used for anything. If we now drop this copy of the TST table, Oracle doesn't "remember" that the original name of the index "BIN$HGnc55/+rRPgQPeM/qQoRw==$0"was IND_TST_COL - the ORIGINAL_NAME column in RECYCLEBIN holds the ugly string "BIN$HGnc55/+rRPgQPeM/qQoRw==$0" :

SQL> drop table tst;

 

Table dropped.

 

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",

  2  droptime, base_object, purge_object

  3  from recyclebin

  4  order by droptime;

 

OBJECT_NAME                    ORIGINAL_NAME   TYPE  UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT

------------------------------ --------------- ----- --- --- ------------------- ----------- ------------

BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST             TABLE YES YES 2006-09-01:16:21:00      233032       233032

BIN$HGnc56ABrRPgQPeM/qQoRw==$0 TST             TABLE YES YES 2006-09-01:16:31:43      233031       233031

BIN$HGnc56AArRPgQPeM/qQoRw==$1 BIN$HGnc55/+rRP INDEX NO  YES 2006-09-01:16:31:43      233031       233434

                               gQPeM/qQoRw==$0

Note the values in the CAN_UNDROP and CAN_PURGE columns for the index (displayed as "UND" and "PUR" above). An index cannot be undropped without the table - so CAN_UNDROP is set to NO. It can, however, be purged without purging the table:

SQL> purge index "BIN$HGnc56AArRPgQPeM/qQoRw==$1" ;

 

Index purged.

 

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",

  2  droptime, base_object, purge_object

  3  from recyclebin

  4  order by droptime;

 

OBJECT_NAME                    ORIGINAL_NAME  TYPE  UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT

------------------------------ -------------- ----- --- --- ------------------- ----------- ------------

BIN$HGnc55/9rRPgQPeM/qQoRw==$0 TST            TABLE YES YES 2006-09-01:16:21:00      233032       233032

BIN$HGnc56ABrRPgQPeM/qQoRw==$0 TST            TABLE YES YES 2006-09-01:16:31:43      233031       233031

Now, if we restore the table, it will be restored without the index:

SQL> flashback table tst to before drop;

 

Flashback complete.

 

SQL> select * from tst ;

 

COL        ROW_CHNG

---------- --------

Version3   16:26:10

 

SQL> select index_name from user_indexes where table_name='TST' ;

 

no rows selected

If you drop a table with associated LOB segments, they are handled in a similar way, except that they cannot be independently purged: CAN_UNDROP and CAN_PURGE are set to NO, and they are purged if you purge the table from the recyclebin, restored with the table if you restore it.

A few types of dependent objects are not handled like the simple index above.

• Bitmap join indexes are not put in the recyclebin when their base table is DROPped, and not retrieved when the table is restored with FLASHBACK DROP.
• The same goes for materialized view logs; when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
• Referential integrity constraints that reference another table are lost when the table is put in the recyclebin and then restored.

If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first. If you FLASHBACK DROP a table whose associated indexes have already been purged, it will be restored without the indexes.

In Windows, you can choose to permanently delete a file instead of sending it to the recycle bin. Similarly, you can choose to drop a table permanently, bypassing the Oracle recyclebin, by using the PURGE clause in your DROP TABLE statement.

SQL> purge recyclebin;

 

Recyclebin purged.

 

SQL> select * from recyclebin;

 

no rows selected

 

SQL> create table my_new_table (dummy varchar2(1));

 

Table created.

 

SQL> drop table my_new_table purge;

 

Table dropped.

 

SQL> select * from recyclebin;

 

no rows selected

If you disable the recyclebin at the session level, with ALTER SESSION SET RECYCLEBIN=OFF, it has the same effect as putting PURGE at the end of all your drop statements. Note, however, that you can still use FLASHBACK DROP to restore objects that were put in the recyclebin before you set RECYCLEBIN=OFF. For example:

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",

  2   droptime, base_object, purge_object

  3  from recyclebin

  4  order by droptime;

 

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT

------------------------------ ------------- ----- --- --- ------------------- ----------- ------------

BIN$HGnc56ACrRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:34:12      233031       233031

 

SQL> alter session set recyclebin=off ;

 

Session altered.

 

SQL> create table tst (col varchar2(10), row_chng_dt date);

 

Table created.

 

SQL> insert into tst values ('Version5', sysdate);

 

1 row created.

 

SQL> drop table tst ;

 

Table dropped.

 

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",

  2   droptime, base_object, purge_object

  3  from recyclebin

  4  order by droptime;

 

OBJECT_NAME                    ORIGINAL_NAME   TYPE UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT

------------------------------ -------------- ----- --- --- ------------------- ----------- ------------

BIN$HGnc56ACrRPgQPeM/qQoRw==$0 TST            TABLE YES YES 2006-09-01:16:34:12      233031       233031

 

SQL> flashback table tst to before drop;

 

Flashback complete.

 

SQL> select * from tst ;

 

COL        ROW_CHNG

---------- --------

Version3   16:26:10

This article has explored some of the subtler ramifications of the recyclebin. To sum up:

-The recyclebin may contain several versions of a dropped object. Oracle restores them in LIFO order; you can restore older versions by repeatedly restoring until you get the version you want, or by using the correct version's BIN$... name directly.
- Oracle drops most dependent objects along with the table, and restores them when the table is restored with FLASHBACK DROP, but does not restore their names. You can purge dependent objects separately to restore the table without them.
- Even after turning RECYCLEBIN OFF, you can FLASHBACK DROP objects that were already in the RECYCLEBIN.

Natalka Roshak is a senior Oracle and Sybase database administrator, analyst, and architect. She is based in Kingston, Ontario, and consults across North America. More of her scripts and tips can be found in her online DBA toolkit at .