THE RECYCLE BIN in Oracle 10g

Oracle has introduced "Recycle Bin" Oracle 10g where all dropped objects are stored. If you drop a table in Oracle 10g then any associated objects to this table such as indexes, constraints and other dependant objects are simply renamed with a prefix of BIN$$. Underneath the covers, the objects are occupying the same space as when they were created.

Example:

If table TEST1 was created in the USERS tablespace, the dropped table TEST1 remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$.


SQL> create table test1 ( a number);

Table created.

SQL> drop table test1;

Table dropped.

SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST1 BIN$Ik84R6rKEj7gRAgAIMR0GQ==$0 TABLE 2006-11-15:14:34:13
SQL>



You can continue to access the data in a dropped table. Each user has the same rights and privileges on Recycle Bin objects before it was dropped. You can view your dropped tables by querying the new RECYCLEBIN view.

How to clean recyclebin?

You can user purge recycle bin command to delete all the objects from recycle bin.


SQL> PURGE RECYCLEBIN;

Recyclebin purged.

SQL> SHOW RECYCLEBIN


What if I want to delete objects permanently?

In order to completely remove table from the DB and to release the space, you can use new PURGE command.

SQL> purge table test1;
Table purged.

In this case, table test1 will be deleted from the database permanently.

Space/Quota Issue?

Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides to permanently remove them using the new PURGE command. The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature. Objects in the Recycle Bin will be automatically purged by the space reclamation process if

1. A user creates a new table or adds data that causes his/her quota to be exceeded.
2. The tablespace needs to extend its file size to accommodate create/insert operations.

Can I disable Recycle Bin?

Yes, you can disable it at your session level by using following command.
SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;

The dropped objects, when recyclebin was ON will remain in the recyclebin even if we set the recyclebin parameter to OFF.