数据库运维指南：解析索引表在Oracle 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:

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

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:

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:

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

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

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:

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

The second version is still hanging out in the recyclebin:

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 RECYCLEBIN views have a few other columns that make the relationship between TST and IND_TST_COL clear:

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:

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

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 .