Working With Oracle’s Recycle Bin

One of the greatest things that Oracle added to its database, in version 10g, was the recycle bin.

What I like about the recycle bin is the piece of mind it gives the DBA. If enabled, it gives an extra option for recovering tables that were dropped.

If someone drops a table by accident (it can happen, and most often it happens in the development environment), then recovering that table is very fast. You no longer have to go back to previous rman backups or exports to recover the table. You can just recover it from the recycle bin.

Today’s blog will explain what the recycle bin is and how it can benefit you!

1. What Is the Recycle Bin?

Introduced in 10g, the recycle bin is a data dictionary table that contains information about dropped objects. When you drop a table, the table and its associated objects, such as indexes, constraints (with the exception of foreign keys), triggers, nested tables, LOB segments, clusters,
all go into the recycle bin.

In reality the objects are not moved from one place to the other. The objects stay in the same tablespace, using up quota in the tablespace. The objects get only renamed behind the scene.

Each user has its own recycle bin, as the user can only see the objects that he/she has dropped. In order to see what is in your own recycle bin, run the query:

select * from recyclebin;
show recyclebin;

In order to see ALL the objects that were dropped, you must query DBA_RECYCLEBIN view, as in:

select * from dba_recyclebin;
show dba_recyclebin;

2. When will objects not be placed into the recycle bin?

If the recycle bin is not enabled, then objects will not be placed there.

If you drop a table with the purge clause, the table will bypass the recycle bin.

If you only drop an index, a trigger, a constraint, these individual objects will not be placed into the recycle bin. You must drop the table in order for the dependent objects to be placed into the recycle bin.

If you drop a tablespace including its contents, the tables in that tablespace are not placed into the recycle bin. Also the objects from the dropped tablespace are purged from the recycle bin.

If you drop a user, the objects belonging to the user are not placed in the recycle bin. Objects in the recycle bin belonging to the user are purged or deleted from the recycle bin.

3. Recycle Bin Naming Convention

It is easy to distinguish objects’ names in the recycle bin, as their names starts with BIN$.

The naming convention is BIN$unique_id$version.

The unique_id is a 26 character globally unique identifier, which uniquely identifies the object. The version is a version number assigned to the database.

This naming convention makes it possible to drop/recreate/drop tables with the same name, multiple times.

4. Changing The Status of The Recycle Bin

By default the recycle bin is enabled. You can verify the status of the recycle bin, by checking the recycle bin parameter:

show parameter recyclebin

This parameter is not dynamic, meaning you need to bounce the database for the change to take effect:

To disable the recycle bin:

alter system set recyclebin=OFF scope=spfile;
shutdown immediate;
startup;

To enable the recycle bin:

alter system set recyclebin=ON  scope=spfile;
shutdown immediate;
startup;

5. Content Of The Recycle Bin

If you want to check what is in the recycle bin, there are a few ways:

show recyclebin;
-- this will show the object that you own, that are in the recycle bin.

select * from dba_recyclebin;
-- this shows everything that is in the recycle bin.

6. How To Purge Objects In The Recycle Bin

If you want to avoid the recycle bin when dropping a table, just use the PURGE clause, as in:

drop table hr.departments purge;

The dropped table will not be placed into the recycle bin, as a result you will not be able to recover the table from the recycle bin.

Why would you want to do this? Maybe for a schema refresh, when you drop the existing objects, prior to an import, you do not want to keep the objects in the recycle bin, so you use the purge clause. Maybe you are dropping a large table 100GB in size, and you want to free up the space in the tablespace. These are all good reasons to drop tables with the purge option.

In order to purge all the objects from the recycle bin use:

purge dba_recyclebin;

To only purge the objects that you own:

purge recyclebin;

To only purge one table, you can use the table’s old name or the table’s recycle bin name:

purge table hr.departments;
purge table "BIN$....";

The reason we have a recycle bin is to be able to recover the objects placed there. Join me next week, for examples on recovering objects from the recycle bin!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!