Help on "Alter System"

[ly]

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!

[jmodic]

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.

[ly]

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!

[jmodic]

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