Turn Off Recycle Bin Option Oracle

Karan Balkar
Tech, Oracle, Tips and tricks

Hello everyone!

Starting from Oracle version 10g, a new feature known as RecycleBin was introduced to recover dropped tables. Dropped tables go "into" the recyclebin, and can be restored (undropped) from the recyclebin.

By default the RECYCLEBIN option is ON. Hence you can recover the tables dropped, by using the FLASHBACK table command as follows!

Pre-requisites: Oracle database 10g Express Edition with *SQLPlus

Open *SQLPlus and run the following commands! 

DROP TABLE YOURTABLENAME; 

FLASHBACK TABLE YOURTABLENAME TO BEFORE DROP;

Check parameter RECYCLEBIN using the below command

SHOW PARAMETER RECYCLEBIN;

Now, in order to disable the RECYCLEBIN feature we need to run the following command as database administrator. In case you encounter the insufficient privileges error, I would suggest you refer this link.

SQL> ALTER SYSTEM SET recyclebin = OFF;
System altered.

That's it then for this Oracle database tip. We have now turned off the RECYCLEBIN option. Hope this helps! Stay tuned for more tips and tricks!

Some more information on RECYCLEBIN