So You Want to Use Oracle's SPFILE - Page 2

May 30, 2003

How to change a parameter setting

The process to change a parameter in the SPFILE is much the same as you have done in the past though the ALTER SYSTEM command structure. There are a couple of options that add some functionality to make a parameter change either current, for future use or for resetting a parameter. Here is the structure of the command for setting a parameter.

Setting a Parameter

ALTERE SYSTEM SET parameter_name=parameter_value
                [COMMENT='text'] 
                [DEFFERRED] 
                [SCOPE={MEMORY,SPFILE,BOTH}] 
                [SID={'sid','*'}]

ALTER SYSTEM OPTIONS

If you are not using an SPFILE, and try to issue the ALTER SYSTEM command, you will get the following error.

SQL> alter system set timed_statistics=true scope=spfile;
alter system set timed_statistics=true scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

Resetting a Parameter to its' default value

ALTERE SYSTEM RESET
ALTERE SYSTEM RESET parameter_name
                [SCOPE={MEMORY,SPFILE,BOTH}] 
                [SID={'sid','*'}]

ALTER SYSTEM RESET OPTIONS

What Oracle documentation says

Be forewarned, Oracle documentation says that in order to delete or restore a parameter to its default value you should use the following command for string values.

ALTER SYSTEM SET parameter='';

In addition, for numeric and Boolean values you are to set the parameter specifically to its original default value. This may work, since you are setting the value in the SPFILE, but it will still make an entry in the SPFILE. This does nothing for you. What you really want is to remove the entry from the SPFILE. Follow along with the following example and you will learn of the ONLY method that I have found that works.

Examples of resetting and setting a parameter

The example I always like to use involves putting the parameter TIMED_STATISTICS under the control of Oracle's new STATISTIC_LEVEL parameter. This is a good example because most of us will have this value set in our parameter file and in order to be completely under the control of the STATISTIC_LEVEL parameter you must remove it from the parameter file. In addition, since the instance only remembers the setting of this parameter through the parameter file (until next startup) you need to set it properly for future sessions that will connect. Here is the sequence of events to do this.

1. Remove the entry from the SPFILE

       alter system reset timed_statistics scope=spfile sid='*';

2. Set the parameter for future session connects

       alter system set timed_statistics=true scope=memory;

Setting a parameter that is a list of strings is not that difficult. Here is an example of setting the CONTROL_FILES parameter.

alter system set control_files='/u01/app/oracle/oradata/saigon/control01.ctl',
                               '/u01/app/oracle/oradata/saigon/control02.ctl',
                               '/u01/app/oracle/oradata/saigon/control03.ctl' scope=spfile

Conclusion

Once I figured the ins' and outs' of Oracle's new SPFILE, I truly felt that I was able to take control of my database with much more certainty. I was now able to explore the vastly improved dynamic tuning capabilities that Oracle has to offer. I would encourage all DBAs to experiment and eventually switch over to the new SPFILE. It makes a world of difference in your ability to manage parameter values.

» See All Articles by Columnist James Koopmann