-- Do not forget about the Database recycle bin --

Hello Everybody

Just a quick post reminder to keep in mind that starting on Oracle 10g when you drop objects they still recide in your recycle bin. So if you wonder why after dropping all those tables you do not see the space back, you got your answer. This is specially important if you have some DW like environment where you drop and recreate tables every day part of your ETL.

Every schema in the database has its own recyclebin, if you want to know what is the content of the entire database recycle bin you can do this query.

If you only want to query your schema recycle bin then you execute the following.

In order to be able to purge an object from the recycle bin you need to either own the object or you need to have the "drop any" system privilege assigned to you. 

If you want to purge objects in the recyclebin for the entire database, you need to have the sysdba privilege, be connected as sysdba and execute the following.

SQL> purge dba_recyclebin;

If you want to purge your own recycle bin you do the following.

SQL> purge recyclebin;

If you want to purge objects in the recycle bin stored in an specific tablespace you execute the following:

SQL> purge tablespace ;

If you want to be more specific and purge objects in a specific tablespace for a specific user you execute the following.

SQL> purge tablespace user ;

Last but no least if you want to purge the recycle bin for a table you own and you just dropped you execute the following.

SQL> purge table ;

If that table has indexes associated you execute the following.

SQL> purge index ;

Enjoy.