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

Comments

  1. Pingback: How to use oracle recyclebin | ηαвєєℓкнαη.¢σм

  2. Thank you for sharing superb informations. Your website is so cool. I am impressed by the details that you have on this site. It reveals how nicely you understand this subject. Bookmarked this web page, will come back for more articles. You, my friend, ROCK! I found just the info I already searched everywhere and simply couldn’t come across. What a perfect web-site.

Leave a Comment