Emptying the Oracle Recycle Bin in Oracle 10g Release 2

Empty Oracle Recycle Bin

A new feature in Oracle 10g Release 2 was the Reycycle Bin. The recycle bin in the Oracle database is where all dropped objects reside. Underneath the covers the object are taking up the same space when they were created.   

 "DROP TABLE mytable", it doesn't really drop it. It instead renames it to e.g.: BIN$67815682942.

Dropped objects are not deleted they are just rename with a prefix of BIN$$. One can get access to the data in a dropeed table or even use a Flashback Query if you have this feature enabled on your version of Oracle.

To completely remove a table from the system and not keep in recycle been use PURGE TABLE command. So if table is called mytable.

The syntax would be:

1PURGE TABLE mytable;

Other ways to purge the recycle bin are:

1PURGE TABLE mytable; (purges mytable from system and recycle bin)
1PURGE INDEX myindex; (purges myindex from system and recycle bin ) PURGE recyclebin;

Purge all object in recyclebin PURGE dba_recyclebin; (purge all objects / only sysdba can do this command) To drop and purge a table in one command it would be: DROP TABLE mytable PURGE; So thats It.

Clean up your recycle bin today.

Posts in this Series