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
SQL>

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.

SQL>

In Oracle 11g database:

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

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

SQL>

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;