Unset values in SPFILE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Unset values in SPFILE

  1. #1
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    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
    I remember when this place was cool.

  3. #3
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    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

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    alter system reset PARAMETER scope=spfile sid='*'

    Dont use SID='*' if you are using RAC, use SID='SID_NAME'. It is however compulsory
    Last edited by davey23uk; 08-17-2004 at 11:19 AM.

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,014
    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.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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".
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    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
    I remember when this place was cool.

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width