Managing the Recycle Bin

what happens when the dropped objects take up all of that space?

The answer is simple: that situation does not even arise. When a tablespace is completely filled up with recycle bin data such that the datafiles have to extend to make room for more data, the tablespace is said to be under "space pressure." In that scenario, objects are automatically purged from the recycle bin in a first-in-first-out manner. The dependent objects (such as indexes) are removed before a table is removed.


Similarly, space pressure can occur with user quotas as defined for a particular tablespace. The tablespace may have enough free space, but the user may be running out of his or her allotted portion of it. In such situations, Oracle automatically purges objects belonging to that user in that tablespace.

If you want to purge the specific table named EMP from the recycle bin after its drop, you could issue

PURGE TABLE EMP;

or using its recycle bin name:

PURGE TABLE "BIN$09LuipandafgMALLLLLghg==$0";

This command will remove table TEST and all dependent objects such as indexes, constraints, and so on from the recycle bin, saving some space. If, however, you want to permanently drop an index from the recycle bin, you can do so using:

purge index inx_emp_01;


which will remove the index only, leaving the copy of the table in the recycle bin.

Sometimes it might be useful to purge at a higher level. For instance, you may want to purge all the objects in recycle bin in a tablespace MYTBSP. You would issue:

PURGE TABLESPACE MYTBSP;


You may want to purge only the recycle bin for a particular user in that tablespace. This approach could come handy in data warehouse-type environments where users create and drop many transient tables. You could modify the command above to limit the purge to a specific user only:

PURGE TABLESPACE MYTBSP USER DIP;


A user such as DIP would clear his own recycle bin with

PURGE RECYCLEBIN;


You as a DBA can purge all the objects in any tablespace using

PURGE DBA_RECYCLEBIN;


As you can see, the recycle bin can be managed in a variety of different ways to meet your specific needs.