Server Parameter Files (SPFILE) allow you to change parameters on the fly and keep them set on the next startup. In the past when you changed a parameter with ALTEM SYSTEM command it reverted to the stored value (in init.ora file) on startup.

Although you can open the binary server parameter file with a text editor and view its text, do not manually edit it. Doing so will corrupt the file. You will not be able to start you instance, and if the instance is running, it could crash.

At system startup, the default behavior of the STARTUP command is to read a server parameter file. If you choose to use the traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command.

STARTUP PFILE = /opt/oracle/prod/9.0.1/dbs/initDIA2.ora

Creating a SPFILE

The server parameter file must initially be created from a traditional init.ora file. It must be created prior to its use in the STARTUP command. You must have the SYSDBA or the SYSOPER system privilege to execute this statement.

The following example creates a server parameter file from initialization parameter file /opt/oracle/prod/9.0.1/dbs/initDIA2.ora. In this example no SPFILE name is specified, so the file is created in a platform-specific default location and is named spfile$ORACLE_SID.ora.

sqlplus /nolog

SQL> connect sys/manager as sysdba;
Connected to an idle instance.

SQL> create spfile from pfile='/opt/oracle/product/9.0.1/dbs/initDIA2.ora';
File created.

The server parameter file is always created on the machine running the database server. If a server parameter file of the same name already exists on the server, it is overwritten with the new information.

Oracle recommends that you allow the database server to default the name and location of the server parameter file.

When the server parameter file is created from the initialization parameter file, comments specified on the same lines as a parameter setting in the initialization parameter file are maintained in the server parameter file. All other comments are ignored.

The CREATE SPFILE statement can be executed before or after instance startup. However, if the instance has been started using a server parameter file, an error is raised if you attempt to recreate the same server parameter file that is currently being used by the instance.

Using ALTER SYSTEM to Change Initialization Parameter Values

The ALTER SYSTEM statement allows you to set, change, or delete (restore to default value) initialization parameter values. When the ALTER SYSTEM statement is used to alter a parameter setting in a traditional initialization parameter file, the change affects only the current instance, since there is no mechanism for automatically updating initialization parameters on disk. They must be manually updated in order to be passed to a future instance. Using a server parameter file overcomes this limitation.

Setting or Changing Initialization Parameter Values

Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. Additionally, the SCOPE clause specifies the scope of a change:

SCOPE = MEMORY - Used for dynamic parameters, the effect is immediate but not permanent (will revert on startup).

SCOPE = SPFILE - Used for dynamic and static parameters, the change is effective at the next startup and is persistent.

SCOPE = BOTH - Used for dynamic parameters, the effect is immediate and persistent.

For static parameters the error "ORA-02095: specified initialization parameter cannot be modified" will be obtained if no SCOPE clause is supplied or it is set to MEMORY or BOTH. It is also possible to obtain this error when single quotes are required around the value.

It is an error to specify SCOPE=SPFILE or SCOPE=BOTH if the server is not using a server parameter file. The default is SCOPE=BOTH if a server parameter file was used to start up the instance, and MEMORY if a traditional initialization parameter file was used to start up the instance.

For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.

A COMMENT clause allows a comment string to be associated with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.

The following statement changes the maximum number of job queue processes allowed for the instance. It also specifies a comment, and explicitly states that the change is to be made only in memory (that is, it is not persistent across instance shutdown and startup).

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=50
COMMENT='Temporary change by M.Zahn'
SCOPE=MEMORY;

System altered.

Exporting the Server Parameter File

You can export a server parameter file to create a traditional text initialization parameter file. Reasons for doing this include:

• Creating backups of the server parameter file

• For diagnostic purposes, listing all of the parameter values currently used by an instance. This is analogous to the SQL*Plus SHOW PARAMETERS command or selecting from the V$PARAMETER or V$PARAMETER2 views.

• Modifying of the server parameter file by first exporting it, editing the output file, and then recreating it.

The exported file can also be used to start up an instance using the PFILE option.

The CREATE PFILE statement is used to export a server parameter file. You must have the SYSDBA or the SYSOPER system privilege to execute this statement. The exported file is created on the database server machine. It contains any comments associated with the parameter in the same line as the parameter setting.

The following example creates a text initialization parameter file from a server parameter file where the names of the files are specified:

CREATE PFILE='/export/home/oracle/config/9.0.1/initDIA2.ora'
FROM SPFILE='/export/home/oracle/config/9.0.1/spfileDIA2.ora';

Viewing Parameters Settings

You have several options for viewing parameter settings.

• SHOW PARAMETERS

This SQL*Plus command displays the currently in
use parameter values.

• CREATE PFILE

This SQL statement creates a text initialization parameter file from the binary server parameter file.

• V$PARAMETER

This view displays the currently in effect parameter values.

• V$PARAMETER2

This view displays the currently in effect parameter values. It is easier to distinguish list parameter values in this view because each list parameter value appears as a row.

• V$SPPARAMETER

This view displays the current contents of the server parameter file. The view returns NULL values if a server parameter file is not being used by the instance.