Unset values in SPFILE

[jodie]

Hi!
This may seem pretty basic, so I apologize!

Once I set a parameter in the 9i spfile, how do I "unset" it back to it's default value? I had SORT_AREA_SIZE set, but I want to use PGA_AGGREGATE_TARGET. I set the PGA parameter, and set SORT_AREA_SIZE back to what it says is the default value. But ISDEFAULT in v$parameter still says FALSE.

Any ideas?

Thanks!
Jodie

[Mr.Hanky]

Can't you just re-edit it? Perhaps it is still showing as not default cause you did not recycle the instance. In any case who cares what is says in V$Parameter, as long as you know it is correct.

MH

[jodie]

MH -
I did recycle the db.
I don't really care what it says, except that I want to pull out all of the NON-default values to set when I create the production db (this is a test instance).

So more than anything, I was just curious.

Jodie

[davey23uk]

alter system reset PARAMETER scope=spfile sid='*'

Dont use SID='*' if you are using RAC, use SID='SID_NAME'. It is however compulsory

[gandolf989]

Originally posted by jodie
MH -
I did recycle the db.
I don't really care what it says, except that I want to pull out all of the NON-default values to set when I create the production db (this is a test instance).

So more than anything, I was just curious.

Jodie

v$parameter does have a column called isdefault. You can query based on that column and see which parameters are not default.

[marist89]

Originally posted by gandolf989
v$parameter does have a column called isdefault. You can query based on that column and see which parameters are not default.

I think that's the whole point. She's trying to pull v$parameter.isdefault=Y but setting it to the default value still shows isdefault=N because it was "set".

[Mr.Hanky]

Just out of curiosity, what does it say under ISMODIFIED?

I guess the real question is how and when does V$PARAMETER get refreshed. Or, once it is modified, will it ever show default again if the value is reset to the same number.

MH

[marist89]

http://download-west.oracle.com/docs...te.htm#1014422

[jodie]

Originally posted by davey23uk
alter system reset PARAMETER scope=spfile sid='*'

That worked Dave. Wonder why you need to set sid though since it's not RAC.

Originally posted by Mr.Hanky
Just out of curiosity, what does it say under ISMODIFIED?

I guess the real question is how and when does V$PARAMETER get refreshed. Or, once it is modified, will it ever show default again if the value is reset to the same number.

ISMODIFIED says FALSE for ALL my parameters. Once I did the reset as Dave suggested, and bounced the db, then ISDEFAULT was set back to TRUE.

Originally posted by Mr.Hanky
http://download-west.oracle.com/doc...ate.htm#1014422

This talks about deleting string parameters. Using '' doesn't work with SORT_AREA_SIZE since it's expecting a number (I tried )

Thanks for everyone's help!

Jodie