Until now Oracle has been using a text file called the pfile (parameter file) to store the database initialization parameters. The pfile is read at instance startup time to get specific instance characteristics. Any changes made the pfile would only take effect when the database is restarted. However, parameters that were dynamically alterable could be changed using the appropriate ALTER SYSTEM or ALTER SESSION statement, which would take effect immediately.

As of Oracle9i, new feature called the spfile (server parameter file). The spfile is a binary file that contains the same information as the old pfile. The spfile is a server-side initialization parameter file; parameters stored in this file are persistent across database startups. This makes all the changes made to the instance using the ALTER SYSTEM statement persistent. Oracle requires that you start an instance for the first time using the pfile and then create the spfile.

Creating the spfile

By default a Oracle uses the pfile. The spfile must be created from the pfile at the SQL prompt. Oracle starts using the spfile only after you restart the database using the spfile. The spfile is created using the CREATE SPFILE statement; this requires SYSDBA or SYSOPER privileges.

This is the simplest form of the CREATE SPFILE statement. It creates a spfile in the default directory, ${ORACLE_HOME}/dbs/, from the pfile located in the default directory, ${ORACLE_HOME}/dbs/. The file is named spfile${ORACLE_SID}.ora. If a spfile already exists in the destination directory, it is over written, however if it's in use by the instance, then it returns the following error:

SQL> create spfile from pfile;

create spfile from pfile

*

ERROR at line 1:

ORA-32002: cannot create SPFILE already being used by the instance

New default STARTUP behaviour

Oracle9i searches for the following the pfile/spfile in the following order:

1. spfile${ORACLE_SID}.ora file in ${ORACLE_HOME}/dbs/
2. spfile.ora in ${ORACLE_HOME}/dbs/
3. init${ORACLESID}.ora in ${ORACLE_HOME}/dbs/

Starting the database with pfile/spfile

By default Oracle looks for the spfile in ${ORACLE_HMOE}/dbs/. To override this use the PFILE option of the STARTUP command:

A new initialization parameter called the SPFILE

This is similar to the IFILE parameter which is used as a pointer to a non-default pfile. The SPFILE parameter is used to specify the path of spfile located in a non-default location.

Modifying the Contents of spfile

Modifying the contents of spfile can be done in two ways:

• by using the ALTER SYSTEM statement
• by exporting and importing back the spfile

The parameters you change using the ALTER SYSTEM statement get stored in the spfile unlike the pfile, which has to be manually edited.

The ALTER SYSTEM statement has a new option called the SCOPE, which can have three values which are explained below.

Specifying SCOPE=MEMORY

To make a change only for the life of this instance (without making it permanent in the spfile):

SQL> SHOW PARAMETER timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     FALSE


SQL> ALTER SYSTEM SET timed_statistics=TRUE scope=memory;
System altered.

SQL> SHOW PARAMETER timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     TRUE

Specifying SCOPE=SPFILE

To make a change the spfile without affecting the current instance, you use the SCOPE=SPFILE option of the ALTER SYSTEM statement:

SQL> SHOW PARAMETER timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     FALSE


SQL> ALTER SYSTEM SET timed_statistics=TRUE scope=spfile;
System altered

SQL> SHOW PARAMETER timed_statistics
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     FALSE

Specifying SCOPE=BOTH

To make changes to the spfile and the current instance, you use the SCOPE=BOTH option of the ALTER SYSTEM statement:

SQL> SHOW PARAMETER timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     FALSE

SQL> ALTER SYSTEM SET timed_statistics=TRUE scope=both;
System altered.

SQL> SHOW PARAMETER timed_statistics

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
timed_statistics                     boolean     TRUE

v$spparameter view

The contents of the SPFILE can be obtained from the V$SPPARAMETER view:

SQL> ALTER SYSTEM SET timed_statistics=FALSE SCOPE=SPFILE;
System altered.

SQL> SELECT name,value FROM v$parameter WHERE name='timed_statistics';

NAME                 VALUE
-------------------- ---------------------
timed_statistics     TRUE

SQL> SELECT name,value FROM v$spparameter WHERE name='timed_statistics';

NAME                 VALUE
-------------------- ---------------------
timed_statistics     FALSE

Is my database using spfile?

Am I using spfile or pfile ?

SQL> SELECT name,value FROM v$parameter WHERE name = 'spfile';

NAME       VALUE
---------- --------------------------------------------------
spfile     /fsys1/oracle/product/9.2.0/spfileTEST.ora

Exporting the spfile

Exporting the spfile to a text file is possible using the CREATE PFILE command.