At work, our DBAs have disabled the recycle bin. At home, while answering stackoverflow questions, I create a lot of tables, which are dropped very quicky.
I would like to disable the recyblebin. While researching, I found two methods:
ALTER SESSION SET RECYCLEBIN = OFF;
and even
ALTER SYSTEM SET RECYCLEBIN = OFF DEFERRED;
However, I'm using Oracle's vagrant boxes, so how can I disable the recyclebin while installing the database, or in other words with init parameters?
Like any other initialization parameter, you can set the initial value of the recyclebin parameter in the text initialization file initSID.ora:
recyclebin=off
and also as suggested by @Kumar, you can set it afterward as:
alter system set recyclebin = OFF SCOPE = SPFILE SID='*'
From the documentation:
Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default.
You enable and disable the recycle bin by changing the recyclebin initialization parameter. This parameter is not dynamic, so a database restart is required when you change it with an ALTER SYSTEM statement.
To check the parameter value:
SQL> show parameter recyclebin;
NAME TYPE VALUE
------------ ----------- ----------
recyclebin string ON
To disable the recycle bin:
Session level:
ALTER SESSION SET recyclebin = OFF;
Database level:
ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE; -- Requires database restart
To remove all dropped objects from the recyclebin (current user):
PURGE RECYCLEBIN;
To remove all dropped objects from the recyclebin (system wide, available to SYSDBA only or, starting with version 12c, to users having the PURGE DBA_RECYCLEBIN system privilege):
PURGE DBA_RECYCLEBIN;