How-to: Server Parameters

Oracle Server parameters allow you to modify many aspects of the Oracle server (full list).

To see the current value of any parameter:

 select name,value
 from   v$parameter
 where  name='Some_Parameter';

Changing a Parameter value

Use the ALTER SYSTEM command to set parameters:

ALTER SYSTEM set parameter = value SCOPE = MEMORY;
ALTER SYSTEM set parameter = value SCOPE = SPfile;
ALTER SYSTEM set parameter = value SCOPE = BOTH;

-- MEMORY - This affects the database now; but will not remain after a restart.
-- SPfile - This does not change the instance immediately, but will modify the SPfile takes effect after a restart.
-- BOTH - change both the current instance and the spfile.

Some parameters can be modified immediately with ALTER SYSTEM, some can only be modified for a single session with ALTER SESSION. Static parameters must be modified with scope=SPfile

Restoring the DEFAULT Parameter values

For string parameters, setting to an empty string will restore the default.
ALTER SYSTEM SET parameter = '' scope=SPfile;

For any parameter the RESET option will restore the default.
ALTER SYSTEM RESET parameter scope=SPfile sid='*' ;

note
When resetting a parameter, you must specify sid=mySid or sid='*' even for non-RAC instances.

Managing parameter files

In Oracle9i and above, the SPfile is stored in a binary format on the server. You no longer need to have a local copy to start the database remotely. This also means that changes made via ALTER SYSTEM will persist across server restarts - without any need to remember to edit a separate init.ora file.

A Pfile (old style text init.ORA) can be created with the command:

CREATE Pfile = 'pfilename' FROM SPfile = 'SPfilename';

An SPfile (binary) can be created with the command:

CREATE SPfile = 'SPfilename' FROM Pfile ='pfilename';

To create an SPfile, the database need not be started, the old Pfile format is largely for backwards compatibility with older releases.

If the system has both a Pfile and an SPfile, then Oracle will use the SPfile.

If the system has both a generic init.ora and an SID-specific parameter file, Oracle will use the SID-specific file.

SID specific:
spfileSID.ora or initSID.ora

Generic:
spfile.ora or init.ora

The location for all parameter files is:
ORACLE_HOME\database (Windows)
ORACLE_HOME/dbs (Unix/Linux)

e.g.
C:\oracle\ora11\database\spfileMYDB.ora
C:\oracle\ora11\database\initMYDB.ora

To use an SPfile in a non standard location you can cheat by creating an init.ora containing just one line:
SPfile='C:\adminfiles\SPfilelive.ora'
For this to work - make sure you don't also have an SPfile in the default location.

To startup a database with an SPfile:
SQL> startup

To startup a database with a Pfile, use the Pfile= option :
SQL> startup pfile=filename

Examples

ALTER SYSTEM SET log_archive_format = "live%S.arc" SCOPE = SPfile;

ALTER SYSTEM SET shared_pool_size=64M scope=both

SELECT name, value from v$parameter where name = 'SPfile';

show parameter SPfile;

ALTER SYSTEM SET OPTIMIZER_MODE = "all_rows" SCOPE = SPfile;

'Internal' parameters (starting with _ ) should not be modified unless advised by Oracle Support.

The default value for many of these parameters does vary across Operating System platforms.

"Happiness, wealth, and success are by-products of goal setting; they cannot be the goal themselves" ~ Denis Waitley, The Joy of Working

Related
V$PARAMETER
V$SpPARAMETER
ALTER SYSTEM
Oracle Memory Architecture (Powerpoint)