Oracle introduced since 10g a new feature the Database Recyclebin, which as a 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 a 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
Tags: Tuning