OraFAQ Forum: Server Administration » Recyclebin

Home » RDBMS Server » Server Administration » Recyclebin (Oracle 10 on Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Recyclebin [message #487559]

Tue, 28 December 2010 18:06

International_DBA
Messages: 5
Registered: January 2010
Location: England

Junior Member

I started to write a blog [spam]. I cannot post a link to it yet as I have only just started using the forum. I found a problem straight away. It seemed to me that if I did not put a recyclebin entry in the parameter file, the value of the parameter was "on" and isdefault was true (which I understand is correct). However, if I put a recyclebin=on entry in the parameter file then isdefault was false (which I understand is incorrect). Does anybody know why this should be the case? The blog entry shows the SQL I used.

Thank you

Andrew

[Updated on: Thu, 30 December 2010 00:21] by Moderator

Report message to a moderator

Re: Recyclebin [message #487561 is a reply to message #487559]

Tue, 28 December 2010 18:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Recyclebin [message #487562 is a reply to message #487561]

Tue, 28 December 2010 18:16

International_DBA
Messages: 5
Registered: January 2010
Location: England

Junior Member

Sorry, but the posting tool stopped me adding a link to my blog but I have put it as my homepage so hopefully people will be able to see it there. If not here is the blog so far. In the meantime I will read the posting guidelines:

Before Oracle 10g, if you dropped an object, it would be completely removed from the
database. In Oracle 10g, the recycle bin was introduced. It is controlled by the recyclebin
initialisation parameter, which has a default of ON:

SQL> col value format a5
SQL> select value, isdefault from v$parameter
2 where name = 'recyclebin'
3 /

VALUE ISDEFAULT
----- ---------
on TRUE

SQL>

I ran the query above after opening the database using a parameter file without a recyclebin
entry. If you add a recyclebin=on entry to the parameter and bounce the database, the
query runs as follows:

SQL> col value format a5
SQL> select value, isdefault from v$parameter
2 where name = 'recyclebin'
3 /

VALUE ISDEFAULT
----- ---------
on FALSE

SQL>

i.e. the ISDEFAULT column shows FALSE instead of TRUE. I do not know what causes this.

Report message to a moderator

Re: Recyclebin [message #487563 is a reply to message #487562]

Tue, 28 December 2010 18:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Too bad you decided to not follow the posting guidelines.
10g is *NOT* a version designator; it is strictly a Marketing label.

Post complete results of Post complete results of
SELECT * from v$version;

Some versions of Oracle a more buggy than others.
By the way, V10 is no longer fully supported & you are encourage to test same against any & all supported versions; then post results.

Some versions of Oracle are mo

Report message to a moderator

Re: Recyclebin [message #487565 is a reply to message #487563]

Tue, 28 December 2010 18:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> connect dbadmin/admindb
Connected.
SQL> select value, isdefault from v$parameter  where name = 'recyclebin';

VALUE
--------------------------------------------------------------------------------
ISDEFAULT
---------
on
TRUE


SQL> alter session set recyclebin=on;

Session altered.

SQL> select value, isdefault from v$parameter  where name = 'recyclebin';

VALUE
--------------------------------------------------------------------------------
ISDEFAULT
---------
ON
TRUE


SQL> alter session set recyclebin=off;

Session altered.

SQL> select value, isdefault from v$parameter  where name = 'recyclebin';

VALUE
--------------------------------------------------------------------------------
ISDEFAULT
---------
OFF
TRUE


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

I am puzzled why a parameter that requires a DB restart to take effect,
can also be changed at the session level.

Rhetorical question - why would anyone add a parameter to the initialization file and specify the default value for that parameter?

Report message to a moderator

Re: Recyclebin [message #487566 is a reply to message #487562]

Tue, 28 December 2010 18:58

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>i.e. the ISDEFAULT column shows FALSE instead of TRUE. I do not know what causes this.
Quote:

ISDEFAULT Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2012.htm#REFRN30176

Report message to a moderator

Re: Recyclebin [message #487572 is a reply to message #487566]

Tue, 28 December 2010 20:52

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

...Even if you put the 'ON' as a parameter value also it will display as false!
Any changes made to the parameter will change the "isdefault" value

Sriram

Report message to a moderator

Re: Recyclebin [message #487587 is a reply to message #487565]

Wed, 29 December 2010 00:54

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

How 11g / 11.2.0.1.0 is 10? If some of your errors are so obvious I will not read your blog and I advise everyone to not read it.

Regards
Michel

Report message to a moderator

Re: Recyclebin [message #487588 is a reply to message #487572]

Wed, 29 December 2010 00:55

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

ramoradba wrote on Wed, 29 December 2010 03:52

...Even if you put the 'ON' as a parameter value also it will display as false!
Any changes made to the parameter will change the "isdefault" value

Sriram

No, it is set to FALSE if it is modified in parameter file not in memory (see documentation link by Mahesh Rajendran).

Regards
Michel

Report message to a moderator

Re: Recyclebin [message #487592 is a reply to message #487588]

Wed, 29 December 2010 01:03

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Thats what I said ! May be I should type like this "Even if you put the 'ON' as a parameter value in "Parameter file" also it will display as false!"
Quote:

Any changes made to the parameter will change the "isdefault" value

while editing the net got disconnected I didn`t seen it again just now observed.

Sriram

Report message to a moderator

Re: Recyclebin [message #487593 is a reply to message #487587]

Wed, 29 December 2010 01:13

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Michel Cadot wrote on Wed, 29 December 2010 12:24

Quote:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

How 11g / 11.2.0.1.0 is 10? If some of your errors are so obvious I will not read your blog and I advise everyone to not read it.

Regards
Michel

Version 11g not posted/specified by OP it is by Blackswan!
@ OP You should be in a position to answer any kind of questions on the topic/content what you posted.

Goodluck
Sriram

Report message to a moderator

Re: Recyclebin [message #487599 is a reply to message #487593]

Wed, 29 December 2010 01:29

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In the title OP said: "Oracle 10 on Windows".

Regards
Michel

Report message to a moderator

Re: Recyclebin [message #487603 is a reply to message #487599]

Wed, 29 December 2010 01:42

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Definitely its a Communication gap Wink

Wink

why you posted this ?Quote:

How 11g / 11.2.0.1.0 is 10?

and whom you are asking?Blackswan or OP ?
sriram

Report message to a moderator

Re: Recyclebin [message #487605 is a reply to message #487603]

Wed, 29 December 2010 01:52

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Definitely its a Communication gap

Definitively! Embarassed

Embarassed

I thought OP gave precisions when it was BlackSwan, my window is to small!

Regards
Michel

Report message to a moderator

Re: Recyclebin [message #487679 is a reply to message #487559]

Wed, 29 December 2010 18:22

International_DBA
Messages: 5
Registered: January 2010
Location: England

Junior Member

Thanks for all your feedback so far.

Thanks also to the people who seem to have looked at the blog. Since last night I have now had viewings from India as well, which is great.

I will try to answer your points 1 by 1.

The underlying OS is Windows XP Professional Service Pack 3.

It is running on a PC which I assembled myself.

The output from v$version is as follows:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>

I'm not too bothered about whether this version of Oracle is supported as I am doing this strictly for self education purposes (more to follow).

Report message to a moderator

Re: Recyclebin [message #487680 is a reply to message #487679]

Wed, 29 December 2010 18:30

International_DBA
Messages: 5
Registered: January 2010
Location: England

Junior Member

I have just clicked on the link provided by Mahesh Rajendran.
This bit seems to answer my question:

ISDEFAULT VARCHAR2(9) Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)

(more to follow - hopefully tomorrow)

Report message to a moderator

Previous Topic:	ORA-01079
Next Topic:	Allocating extent to a clob column of a table!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 08 14:07:13 CDT 2024