10g
On Database level
11:15:22 DB2 @ sev1 > show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
11:15:45 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = OFF ;
System altered.
11:17:45 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = ON ;
System altered.
11g
We need to specify DEFFERED to turn off recyclebin on 11g databases, else you will see the below error. The change will take place from the new sessions
11:15:26 DB2 @ sev1 > 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
Fix :
11:15:45 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = OFF DEFERRED;
System altered.
11:15:58 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = ON DEFERRED;
System altered.
Turning off recyclebin on session level
11:18:23 DB2 @ sev1 > ALTER SESSION SET recyclebin = OFF ;
11:19:17 DB2 @ sev1 > ALTER SESSION SET recyclebin = ON ;
