Home » RDBMS Server » Server Administration » Disable recyclebin (oracle10g)
Disable recyclebin [message #401563] Tue, 05 May 2009 10:00 Go to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Hello, Here is the oracle version i am using.
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production


I am trying to disable recyclebin at session level. But it is not working... Am i missing anything here...
scott@orcl> create table flashdrop as 
  2  select * from user_tables 
  3  where rownum < 6;

Table created.

scott@orcl> show recyclebin
scott@orcl> drop table flashdrop;

Table dropped.

scott@orcl> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHDROP        BIN$rcmdDvJ9RNir/0FA7jcZfw==$0 TABLE        2009-05-05:10:56:01
scott@orcl> alter session set recyclebin=OFF;
alter session set recyclebin=OFF
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION 
Re: Disable recyclebin [message #401566 is a reply to message #401563] Tue, 05 May 2009 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
decent post.

I believe the recyclebin needs to be empty before it can be disabled.
Re: Disable recyclebin [message #401569 is a reply to message #401563] Tue, 05 May 2009 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In 10.1 it is a hidden parameter so "_recyclebin".
In addition, verify in your documentation that it can be enable/disable at session level.

Regards
Michel
Re: Disable recyclebin [message #401584 is a reply to message #401569] Tue, 05 May 2009 11:53 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
Thanks for the reply. Looks like, it works oracle 10g R2.
My version is 10gR1. Anyway, thank you...
Re: Disable recyclebin [message #401712 is a reply to message #401563] Wed, 06 May 2009 04:16 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Disable the recyclebin at the session level, with

ALTER SESSION SET RECYCLEBIN=OFF

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",
  2   droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME TYPE  UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT
------------------------------ ------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc56ACrRPgQPeM/qQoRw==$0 TST           TABLE YES YES 2006-09-01:16:34:12      233031       233031

SQL> alter session set recyclebin=off ;

Session altered.

SQL> create table tst (col varchar2(10), row_chng_dt date);

Table created.

SQL> insert into tst values ('Version5', sysdate);

1 row created.

SQL> drop table tst ;

Table dropped.

SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR",
  2   droptime, base_object, purge_object
  3  from recyclebin
  4  order by droptime;

OBJECT_NAME                    ORIGINAL_NAME   TYPE UND PUR DROPTIME            BASE_OBJECT PURGE_OBJECT
------------------------------ -------------- ----- --- --- ------------------- ----------- ------------
BIN$HGnc56ACrRPgQPeM/qQoRw==$0 TST            TABLE YES YES 2006-09-01:16:34:12      233031       233031


[Updated on: Wed, 06 May 2009 04:17]

Report message to a moderator

Re: Disable recyclebin [message #401719 is a reply to message #401712] Wed, 06 May 2009 04:31 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ora_2007 wrote on Wed, 06 May 2009 11:16
Disable the recyclebin at the session level, with

ALTER SESSION SET RECYCLEBIN=OFF...


Didn't you read what OP first posted?
Quote:
scott@orcl> alter session set recyclebin=OFF;
alter session set recyclebin=OFF
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION 


Please carefully read the posts, original question and answers before posting irrelevant stuff.

Regards
Michel


[Updated on: Wed, 06 May 2009 04:32]

Report message to a moderator

Previous Topic: Not able to start the services CMADMIN.EXE and CMAN.EXE
Next Topic: Copying objects from one schema to other
Goto Forum:
  


Current Time: Tue Dec 03 07:50:55 CST 2024