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;
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;
No comments:
Post a Comment