Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 30, 2003

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

By James Koopmann

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   Put a comment on the parameter change and store it in the SPFILE
DEFERRED   Changes the value for the parameter for sessions connecting after the statement is issued
SCOPE   Specifies when the change will take effect
  MEMORY The change takes effect immediately but will not be available after the next startup
  SPFILE The change is made in the server parameter file (SPFILE) only and will be set on the next startup
  BOTH MEMORY + SPFILE and will be available after next startup, this is the default
SID   Specify the SID of the instance where you want this value to apply
  '* ' Apply to ALL instances, this is the default

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 parameter_name


SCOPE   Specifies when the change will take effect
  MEMORY Invalid Option even though documented by Oracle
  SPFILE The change is made in the server parameter file (SPFILE) only and will be set on the next startup
  BOTH Invalid Option even though documented by Oracle
SID   Specify the SID of the instance where you want this value to apply
  '* ' Apply to ALL instances, there is no default

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='*';
  1. 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/control03.ctl' scope=spfile


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

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM