You are here

Using Oracle's recycle bin

One of the many new features that Oracle 10g introduced is the recyclebin. When enabled, this feature works a little bit like the familiar Windows recycle bin or Mac Trash. Dropped tables go "into" the recyclebin, and can be restored from the recyclebin. OraFAQ has already published an article covering the basics; 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$.

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:

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.

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:

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:

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:

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:

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

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:

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.

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:

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 http://toolkit.rdbms-insight.com/.

You are simply great. The article is very useful and explained yhe topic very well. This feature has been understood simply in just one reading. You give very good examples, which helped to understand the feature.

Thanks a lot for this useful information (and for sure, in a very simple manner).

The article is very useful and explained the topic very well. This feature has been understood simply in just one reading. You give very good examples, which helped to understand the feature.
I was looking for this BIN tables which contained in this table ALL_TAB_PARTITIONS in sys schema. Which failed the table partition creation automatically.
Thanks a lot for this useful information (and for sure, in a very simple manner).

My database have lots of indexes, sequences and dependencies between tables. My application connects database using JDBC API.
I deleted some tables and those went in recycle bin.
But the real problem is these deleted tables are still referenced by my application.
In my application, I am caching all tables meta data. But I surprised even after deletion of those tables why they are available in the metadata.

is there any way when I select metadata, filter only the active tables ?

Hi Natalka,
thanks for posting this informative article. I am looking for an option to purge (drop) certain partitions of a table for good (without those going into the recycle bin). I was wondering if an option exists to do precisely that.

Since the bin objects are still there, they will appear in selects from user_tables, user_triggers, etc.
So, if you do schema manipulation/verification scripts, you should filter them out by hand in your queries.

I had eg. a script that verified sync of max value of table columns to the sequence that was used (via trigger) to simulate autoincrement. If a difference was found, the script would update the sequence nextval.
It first scanned user tables for triggers with conforming syntax to the "autoincrement standard", then looked up the relative sequence name in the trigger body via a regexp. (This was thought to be more error proof than determining the sequence name via a base prefix added to the table name)

Now, the name of the sequence is part of the trigger body, so when moved to the recycled bin the trigger body is not altered and it still refers to the live objects.

In the end my script found deleted triggers on deleted tables, and updated the sequence current values of real sequences to reflect the max id present in the deleted tables. And without any kind of error anywhere - the logs indicated the good sequences being updated - it took a while to debug!

In 10g Recyclebin is one of the features from where the Recyclebin takes the space is it from the space allocated i.e from the SGA or the space from the Hard Disk. When a tablespace is dropped will it be in the Recyclebin

It is a very nice article.
I have dropped one table which is associated with bitmap join indexes and bitmap indexes.
so how to restore that bitmap join indexes and bitmap indexes.
Kindly help me for tha same....

We have a before drop trigger at database level to prevent dropping the objects. Recently we had a issue where the application was trying to create a table and insert some clob rows into it. It happened internally try to purge the recycle bin objects to make some space and oracle did not report any ORA- error since it was this trigger preventing it. we had hard time trying to find out the root cause.

Can you think of any options where the db level before triggers internally does not apply to recycle bin. I tried to exclude the BIN$ objects in the triggers but it looks like there are some objects without BIN$ in the recyclebin which is same as original name.