Ever query all_source and come up with a strange object name that starts with BIN$ or RB$$? That is the recyclebin, holding on to tables and indexes you have deleted in the past until they are either released or aged out. If you are trying to make space by dropping objects, be sure to purge the recyclebin. If you’ve made a mistake or need to go back to that table to get something before it is finally dead and buried, you can still query it while it is in the recyclebin.

If you use the all_source view to check on the existence of certain columns or tables, you may have happened upon the recyclebin before. This is part of a feature called Flashback Drop, where a dropped table (along with accompanying triggers) or dropped indexes are moved to the recyclebin.

SQL> select name, type from all_source where lower(text) like '%phone%';

Query the recyclebin. This works for the current schema’s recyclebin.

SQL> select * from recyclebin;

This is also for the recyclebins available to the current user.

SQL> select * from user_recyclebin;

This is for the recyclebins available to the DBA user. So, all of them.

SQL> select * from dba_recyclebin;

The Database Setting

First off, is your database capable of moving objects to the recyclebin? This setting determines if the FLASHBACK DROP capability is also functioning. If on, dropped tables will be moved to the recyclebin. Otherwise, there is no recyclebin.

SQL> show parameter recyclebin

NAME             TYPE           VALUE
---------------  -------------  ----------------
recyclebin       string         on

Flashback a Table

Now that you know it’s there and working for you (true by default), you may want to know how you can use the recyclebin. Well, it’s a lot like any other recycle bin you have seen on a Windows machine. Things can be restored from it and the data is not completely lost.

Query from a table in the recyclebin by double-quoting the identifier.

SQL> select count(*) from "BIN$Wflyws9/TrK7SAckzOdAPg==$0";

 COUNT(*)
----------
 195
1 row selected.

Restore a table from a recyclebin as follows. Run the command as the  schema owner of the object. A table that has been restored is removed from the recyclebin.

SQL> flashback table tbl_locations to before drop rename to tbl_locations_bak;

Tbl_locations successfully completed.

SQL> select count(*) from tbl_locations_bak;

 COUNT(*)
----------
 195
1 row selected.

If that table is dropped, the new table name will be used as the original name in the recyclebin. If the trigger that accompanied that table wasn’t renamed from its recyclebin object_name, the original name in the recyclebin will be the previous object_name in the recyclebin.

SQL> select object_name, original_name, type 
2  from recyclebin where droptime='2015-06-29:14:08:05';

OBJECT_NAME                       ORIGINAL_NAME                   TYPE
------------------------------    ------------------------------- ----------
BIN$Yw1NRoD7R+uZtYw+GS8Vpg==$1    BIN$qLx9yguiSKqMpkBnYESJ+A==$0  TRIGGER
BIN$Nm4sETyRS06qjoTt0b1Bxg==$0    TBLKP_LOCATIONS                 TABLE

PURGE

Run a purge to get rid of these records in the recycle bin.

Note: A purged object cannot be recovered and the action cannot be rolled back.

You can purge on different levels. You can:

• PURGE TABLE [original name]; or PURGE TABLE [BIN$ name];
• PURGE INDEX [original name]; or PURGE INDEX [BIN$ name];
• PURGE RECYCLEBIN;
• PURGE DBA_RECYCLEBIN;
• PURGE TABLESPACE [tablespace name];

NOTE: With table and index purges, if you specify the name but not the recyclebin number and there is more than one object with that name, the purge will remove the oldest instance of that object.

Skipping the Recycle Bin

If you don’t want a table or index to go to the recycle bin, use the purge keyword in the drop dml.

SQL> drop table tbl_locations purge;

NOTE: Objects that are in the recycle bin are still in the same tablespace they were in before. Even though they are in the recyclebin, they are still taking up the same extents used before the drop. So, if you are trying to free up space in a tablespace by dropping a table, be sure to PURGE it, as well.