Using SPFILE

What is oracle Server Parameter File?
A server parameter file is a binary file that cannot be browsed or edited using a text editor. At system startup, the default behavior of the STARTUP command is to read a server parameter file to obtain initialization parameter settings. If you choose to use the traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command.

Viewing Parameters Settings
You have several options for viewing parameter settings.

Method Description

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.

Creating a Server Parameter File
Create a server parameter file using the CREATE SPFILE statement. This statement reads the initialization parameter file to create a server parameter file. The database does not have to be started to issue a CREATE SPFILE statement.

The following example creates a server parameter file from initialization parameter file /u01/oracle/dbs/init.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. The server parameter file is always created on the machine running the database server.

CREATE SPFILE FROM PFILE='/u01/app/oracle/product/9.0.1/dbs/init.ora';

How to change values in SPFILE
The SPFILE initialization parameter contains the name of the current server parameter file. The SQL*Plus command SHOW PARAMETERS SPFILE displays the name of the server parameter file that is currently in use.

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 as described in the following table:

SCOPE Clause Description

SCOPE = SPFILE The change is applied in the server parameter file only.

For dynamic parameters, the change is effective at the next startup and is persistent.

For static parameters, the behavior is the same as for dynamic parameters.

SCOPE = MEMORY The change is applied in memory only.

For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.

For static parameters, this specification is not allowed.

SCOPE = BOTH The change is applied in both the server parameter file and memory.

For dynamic parameters, the effect is immediate and persistent.

For static parameters, this specification is not allowed.

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.

Deleting Initialization Parameter Values

For initialization parameters whose values are string values you can restore a parameter to its default value (effectively deleting it), by using the following syntax:
ALTER SYSTEM SET parameter = '';
For numeric and boolean value parameters, you must specifically set the parameter back to its original default value.

How to create a text file from SPFILE
For various reasons it is required to have the SPFILE values in a text file like

Creating backups of the server parameter file
For diagnostic purposes, listing all of the parameter values currently used by an instance
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 following example creates a text initialization parameter file from the server parameter file
CREATE PFILE FROM SPFILE;
Because no names were specified for the files, a platform-specific name is used for the initialization parameter file, and it is created from the platform-specific default server parameter file.

How to check if an instance was started using a PFILE or an SPFILE

When an instance has been started using the 'classic' PFILE, this view only contains NULL values.
SQL> select count(*) from v$spparameter where value is not null;

If the result of this query is zero, the instance was started using a PFILE.
If the result is a non-zero value, the instance was started using an SPFILE.


Your Name:
Email:
Subject:
Comments:

Method	Description
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.

SCOPE Clause	Description
SCOPE = SPFILE	The change is applied in the server parameter file only. For dynamic parameters, the change is effective at the next startup and is persistent. For static parameters, the behavior is the same as for dynamic parameters.
SCOPE = MEMORY	The change is applied in memory only. For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated. For static parameters, this specification is not allowed.
SCOPE = BOTH	The change is applied in both the server parameter file and memory. For dynamic parameters, the effect is immediate and persistent. For static parameters, this specification is not allowed.

Using SPFILE

See Also