Oracle Recycle Bin

What is a Recycle Bin?

Like Windows and MAC, Oracle also has a  recycle bin that stores all the dropped objects. You can empty it from the bin or retrieve the objects from recycle bin. Have you ever had a “Thank God, there is a recycle bin” moment? This recycle bin has saved me from being in a mess. I am sure you had been in this situations before and that isn't a fun moment to be in.

Let's  dive into the topic, when you send PURGE command along with drop SQL statement, the dropped objects will not be in recycle bin, it is delete from your server OS. When you drop a table, the object  does not get dropped from the database. It actually gets renamed. Oracle renames the table and its associated objects(indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.

You should very well  know when to use DROP and  PURGE statement. It is always nice to have a backup copy of an object which you are going to drop. If you forget to make a backup, then you can always get the dropped objects from a recycle bin. Unless you are 100%  sure, do not use the PURGE statement with a drop statement. The requester  might come back to you later in few days or weeks asking you to restore the table and this happens a lot.

There are two recycle bin views; recyclebin same as user_recyclebin, and dba_recyblebin. Let's do the demo on drop, purge and restore from Recycle bin. Are you ready?

Check if there are dropped/deleted objects on Recycle Bin. This is for DBA only!

SELECT *
FROM DBA_RECYCLEBIN;

Check to see if you have any dropped objects. Displays your dropped objects only.

SELECT *
FROM RECYCLEBIN; 

Lets drop a table, check in recycle bin and recover it from recycle bin.

-- Create test table
CREATE TABLE test_table 
             ( 
                 id   NUMBER, 
                 desc VARCHAR2(100) 
             ); 

--Drop a table
DROP TABLE test_table; 

-- Retreive the table from Recyclebin
FLASHBACK TABLE test_table TO before DROP;

Now drop the table with purge statement.

--Drop a table
DROP TABLE test_table PURGE; 

Remember: When you retrieved the table from recycle bin, all the constraints name will get changed to something funky characters. This will not impact the application that’s using it but it will throw your company’s naming standard on constraints. You will need to clean the constraints name.

Best Practices Around Dropping an Object.

1. Rename the object that you are asked to drop.
2. Make an export dump or a backup copy of  a table structure, data, privileges etc.. to your schema.
3. Wait for few weeks or till no one screams at you.
4. Drop the table from a database.
5. Again wait few more weeks, if no one screams, purge it from recycle bin If you are on Oracle.

I  follow these simple practices while I get a request to drop objects and I recommend you all to follow these as well. Some might call me crazy but I am sure you will thank me later as why I asked you to follow these best practices.

How do you purge objects from recycle bin? This is a place holder for dropped objects occupying a lot of disk space?

--As Schema Owner
PURGE recyclebin;
--AS DBA
PURGE dba_recyclebin;

The first statement dropped objects that belongs to a schema user who is logged in to a database. The second statement drops everything from recycle bin regardless of schema owner. Select the statement best fit your need. Once you drop, you can't get back.

Hope, you find this topic helpful and as always, questions, comments and suggestions are welcome.