CODEWITHSUNDEEP
oracle-database
wolφi
wolφi

Reputation: 8361

How to disable Oracle's recycle bin?

At work, our DBAs have disabled the recycle bin. At home, while answering stackoverflow questions, I create a lot of tables, which are dropped very quicky.

I would like to disable the recyblebin. While researching, I found two methods: 

ALTER SESSION SET RECYCLEBIN = OFF;

and even

ALTER SYSTEM SET RECYCLEBIN = OFF DEFERRED;

However, I'm using Oracle's vagrant boxes, so how can I disable the recyclebin while installing the database, or in other words with init parameters?

Upvotes: 3

Views: 10695

Answers (2)

Popeye
Popeye

Reputation: 35920

Like any other initialization parameter, you can set the initial value of the recyclebin parameter in the text initialization file initSID.ora:

recyclebin=off

and also as suggested by @Kumar, you can set it afterward as:

alter system set recyclebin = OFF SCOPE = SPFILE SID='*'

Upvotes: 4

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

From the documentation:

Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin. The recycle bin is enabled by default.

You enable and disable the recycle bin by changing the recyclebin initialization parameter. This parameter is not dynamic, so a database restart is required when you change it with an ALTER SYSTEM statement.

To check the parameter value:

SQL> show parameter recyclebin;

NAME         TYPE        VALUE
------------ ----------- ----------
recyclebin   string      ON

To disable the recycle bin:

Session level:

ALTER SESSION SET recyclebin = OFF;

Database level:

ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE; -- Requires database restart

To remove all dropped objects from the recyclebin (current user):

PURGE RECYCLEBIN;

To remove all dropped objects from the recyclebin (system wide, available to SYSDBA only or, starting with version 12c, to users having the PURGE DBA_RECYCLEBIN system privilege):

PURGE DBA_RECYCLEBIN;

Upvotes: 3

Related Questions