How to Disable RecycleBin in Oracle 10g R1 & R2

On 10gR1, in case we want to disable the behavior of recycling, there is an underscore parameter "_recyclebin" which defaults to TRUE. We can disable recyclebin by setting it to FALSE.

From SYSDBA user:

SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin TRUE TRUE

From BH user:

SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT original_name FROM user_recyclebin;

ORIGINAL_NAME
--------------
T1


From SYSDBA user:

SQL> ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE = BOTH;
System altered.

SQL> SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm like '%recycle%'
ORDER BY a.ksppinm
/
Parameter Value Default?
---------------------------- ---------------------------------------- --------
_recyclebin FALSE TRUE

From BH user:
SQL> CREATE TABLE t1(a NUMBER);
Table created.

SQL> DROP TABLE t1;
Table dropped.

SQL> SELECT original_name
FROM user_recyclebin;
no rows selected

There is no need to PURGE.

PS: Please avoid to user oracle hidden parameter. But before using hidden parameter please discuss with oracle support people.

In oracle 10g R2

On 10gR2 recyclebin is a initialization parameter and bydefault its ON. We can disable recyclebin by using the following commands:

SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;

The dropped objects, when recyclebin was ON will remain in the recyclebin even if we set the recyclebin parameter to OFF.