Help on "Alter System"
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Help on "Alter System"

  1. #1
    Join Date
    Sep 2002
    Posts
    30
    Can anyone help on the following question:

    "Alter System set para = value", will this change persistence even shutdown and restart the database, or it only applies to the current instance?

    And will it change the initial parameter's value in the PFILE or SPFILE?

    Thanks very much!

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    The pfile can never be modified by RDBMS, so the changes will never be written in it automaticaly. Whether the changed values of parameters will persist even after you bounce the database depends on wether the instance started with spfile or not.

    If the database was started without spfile then no, the changes made with ALTER SYSTEM SET .... will be lost after reboot. If it was started using spfile, then unless you specify ".... SCOPE = MEMORY" at the end of your ALTER SYSTEM command, the changes will be stored in spfile and will take effect when you restart your database.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2002
    Posts
    30
    Thanks Jmodic, I found the description about this issue from http://technet.oracle.com/docs/produ...te.htm#1012672

    But I still have some questions according to your reply. Where can I check which file "PFILE" or "SPFILE" is used when I use "STARTUP" to startup the database?

    Do you mean for "alter system" by default is "scope=memory" ?

    Thanks a lot!

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You can check whether the instance was started using spfile by the following query:

    SELECT value FROM v$parameter WHERE name = 'spfile';

    It will return the name of the spfile that was used during startup. If the result is NULL the spfile was not used during startup.

    Or alternatively, you can query:

    SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;

    Any non-zero result indicates the usage of spfile, a 0 as a result indicates the instance was started without spfile.

    And no, the default for "ALTER SYSTEM SET ...." is "SCOPE=BOTH".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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