If you’ve ever dreamed of going to a recycle bin like you can on Windows, and “undropping” an Oracle database table… Oracle has made your dream come true!
Introduced with Oracle 10g, Oracle’s recycle bin works a lot like what you’re used to in Windows. When this feature is enabled (the default setting), dropped tables don’t actually get deleted. Instead, they “land” in the recycle bin and can be restored.
The recycle bin is actually a data dictionary table containing data about dropped objects. What actually happens when recycling is enabled is that Oracle renames each dropped table and associated objects (indexes, LOB segments, triggers, etc.) with a system-generated name that starts with “BIN$.” The table data is still available and you can query it like any other table.
To “undrop” a dropped table that’s “in the recycle bin,” you perform an operation known as a “flashback drop.” The command syntax is: FLASHBACK TABLE <name> TO BEFORE DROP. This command just renames the BIN$<name> table back to its original name. (You’ll find an excellent overview of Oracle 10g’s Flashback features here.)
The downside of the recycle bin is that dropped tables are really only renamed. Their table segments are still taking up space in your tablespace, which still counts against your user tablespace quotas. To recover the space associated with dropped tables you need to explicitly “purge” unwanted tables and associated objects from the recycle bin.
Fortunately, purging is pretty easy and flexible. If you have the SYSDBA privilege, you can purge everything from all the recycle bins with PURGE DBA_RECYCLEBIN. Or you can purge just the user recycle bin with PURGE RECYCLEBIN. You can even purge “recycled” objects by schema and user with PURGE TABLESPACE <tablespace> or PURGE USER <user>. Users have access in the recycle bin only to those objects that they themselves dropped.
It’s normal to end up with multiple versions of a table in the recycle bin. In these situations, Oracle always restores the newest version. To restore an earlier version, you can simply refer to it by its unique, BIN$-prepended name. Alternatively, you can use FLASHBACK multiple times until you restore the version you want.
The RECYCLEBIN initialization parameter turns the recycle bin feature on or off at the system or session level. Turning recycling off doesn’t prohibit you from restoring objects that were in the recycle bin before you disabled it.
If you want to keep the recycle feature enabled but bypass it when dropping specific tables, just add the PURGE clause to the DROP TABLE statement; e.g.:
SQL> DROP TABLE TABLE_XYZ PURGE;
If you’re not already using the recycle bin feature, why not turn it on and get it working for you today? There’s really nothing to lose—literally!
For expert advice on how to fine-tune use of Oracle’s recycle bin for your environment, schedule a free consultation with Buda Consulting.
For more information:
- Oracle online documentation on using flashback drop and managing the recycle bin
- Oracle online documentation on the PURGE statement
- Oracle online documentation on undoing a DROP TABLE operation
- A thorough overview of the recycle bin feature in the Underground Oracle FAQs blog
