Issues With Oracle's Recyclebin

Since 10g Oracle introduced a new feature known as the Database Recyclebin. As an Operative System, recyclebin brings the ability to restore tables (files in OS) that are dropped (deleted).

How Does Oracle's Recyclebin Work?

If you drop a table in Oracle and the recyclebin is enabled then the table won't be deleted it will be renamed to a special name in the recyclebin object:

First, clean the recyclebin so you can see the new items in there:

PURGE recyclebin;
Drop the table:

DROP TABLE test;
To see it in the recyclebin type:

SELECT * FROM recyclebin;
You will see an output with all the objects in table test that were renamed so they can be restored.

To avoid the recyclebin storage you can do two things:

1. Purge your table when you DROP it:

DROP TABLE test PURGE;
2. Disable the recyclebin during your oracle session:

ALTER SESSION SET recyclebin = OFF;

Issues With Recyclebin

At the time I'm writing this article, Oracle 11g is the latest Oracle version, the current version is:

Oracle Database 11g Release 1 (11.1.0.6.0)

We are getting slowdowns with 11g and not with 10g, a 100+ columns table drop operation when the recyclebin is on is from 8~10 seconds. That very same operation with PURGE(or when the recyclebin is set OFF) does not take even a second to finish.

For more information about Oracle's recyclebin visit: http://www.orafaq.com/node/968