Unset values in SPFILE

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

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

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

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

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

Quote:

---

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.

Quote:

---

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".

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

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

Quote:

---

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.

Quote:

---

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.

Quote:

---

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