Turn Off Recyclebin In Oracle 10g/11g

Turn Off Recyclebin In Oracle 10g/11g

One of the coolest feature in Oracle starting from 10g is recyclebin but sometimes it is annoying to scroll down and see a long list objects dropped, especially in development environment where you drop more tables than you create.

SQL> SHOW PARAMETER RECYCLEBIN

NAME                                 TYPE        VALUE
———————————— ———– ——————————
recyclebin                           string      on

To turn the feature ON or OFF here what you have to do:

In Oracle 10g database:

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

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

In Oracle 11g database:

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

SQL> ALTER SYSTEM SET recyclebin = ON DEFERRED;
System altered.

NOTE: DEFERRED is used because it takes effect in new sessions in the database.

You will face ORA-02096 in 11g if skipped DEFERRED i.e

SQL> ALTER SYSTEM SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option
To enable the recycle bin for a session:

SQL> ALTER SESSION SET recyclebin = ON;
To disable the recycle bin for a session:

SQL> ALTER SESSION SET recyclebin = OFF;