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 may 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 may now 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 

V$PARAMETER

Displays information about the initialization parameters that are currently in effect for the session. A new session inherits parameter values from the instance-wide values displayed by the V$SYSTEM_PARAMETER view.

Columns
   ___________________________
 
   NUM
   NAME
   TYPE
   VALUE
   ISDEFAULT
   ISSES_MODIFIABLE
   ISSYS_MODIFIABLE
   ISMODIFIED
   ISADJUSTED
   DESCRIPTION
   UPDATE_COMMENT

The issys_modifiable column shows the type of parameter:
FALSE = Static parameter that cannot change its value in the lifetime of the instance.
IMMEDIATE = dynamic, can change the active instance as well as future database restarts.
DEFERRED = dynamic, changes only affect subsequent sessions.

V$PARAMETER2

Displays information about the initialization parameters that are currently in effect for the session, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values displayed in the V$SYSTEM_PARAMETER2 view.

Columns
   ___________________________
 
   NUM
   NAME
   TYPE
   VALUE
   ISDEFAULT
   ISSES_MODIFIABLE
   ISSYS_MODIFIABLE
   ISMODIFIED
   ISADJUSTED
   DESCRIPTION
   ORDINAL
   UPDATE_COMMENT

The issys_modifiable column shows the type of parameter:
FALSE = Static parameter that cannot change its value in the lifetime of the instance.
IMMEDIATE = dynamic, can change the active instance as well as future database restarts.
DEFERRED = dynamic, changes only affect subsequent sessions.

V$NLS_PARAMETERS

National Language Support in effect for the current session.

Columns
   ___________________________
 
   PARAMETER
   VALUE 

V$SPPARAMETER

Parameter values (read at startup from the spfile)
Columns ___________________________ SID NAME VALUE ISSPECIFIED ORDINAL UPDATE_COMMENT

ALTER SYSTEM

Modify system settings.

Syntax:

   ALTER SYSTEM ARCHIVE LOG archive_log_clause
   ALTER SYSTEM CHECKPOINT [GLOBAL | LOCAL]
   ALTER SYSTEM CHECK DATAFILES [GLOBAL | LOCAL]
   ALTER SYSTEM FLUSH SHARED_POOL
   ALTER SYSTEM {ENABLE | DISABLE} DISTRIBUTED RECOVERY
   ALTER SYSTEM {ENABLE | DISABLE} RESTRICTED SESSION
   ALTER SYSTEM RESUME
   ALTER SYSTEM SUSPEND
   ALTER SYSTEM SHUTDOWN [IMMEDIATE] dispatcher_name
   ALTER SYSTEM SWITCH LOGFILE
   ALTER SYSTEM KILL SESSION 'int1, int2' [POST TRANSACTION] [IMMEDIATE]
   ALTER SYSTEM DISCONNECT SESSION 'int1, int2' [IMMEDIATE]
   ALTER SYSTEM SET parameter = value [COMMENT 'text'] [DEFERRED] [Scope_options]
   ALTER SYSTEM RESET parameter(s) [scope_options]
   ALTER SYSTEM QUIESCE RESTRICTED
   ALTER SYSTEM UNQUIESCE

SET Parameters:
   Full list of Static and dynamic parameters.

   Scope_options:
   SCOPE = {MEMORY|SPFILE|BOTH} [SID = 'sid' [,SID =…]]

   DEFERRED - Set parameter values for future connecting sessions.
              Currently active sessions are not affected and they retain the old parameter value.
              This is required for parameters that have the ISSSYS_MODIFIABLE =DEFERRED (in V$PARAMETER) 
              This is optional for parameters that have the ISSSYS_MODIFIABLE =IMMEDIATE (in V$PARAMETER)
              For static parameters, DEFERRED cannot be specified.

   COMMENT text is visible in the UPDATE_COMMENT column of V$SpPARAMETER

   The SID clause is for RAC, where it's possible to change a parameter value for one instance only.
   SID= '*' will affect all instances on the cluster
   If the instance is started with an SpFILE the default = * (all instances)
   If the instance is started with a PFILE then default SID = the current instance.

Quiesce
Putting the database into a quiesced state will prevent inactive user sessions from becoming active, Oracle then waits for existing transactions to finish. In shared server mode, Oracle will also block user logins.
This allows SYS/SYSTEM to perform some maintenance tasks without a full shutdown.
Notes
All Byte values can also be specified in K or M or G
e.g. you can enter 8388608 or 8192 K or 8M
All directory paths follow standard notation i.e UNIX 'quotes' or Windows "double quotes"
"An expert is someone who knows some of the worst mistakes that can be made in his subject and how to avoid them." - Werner Heisenberg  

ALTER SYSTEM

Modify system settings.

Syntax:

   ALTER SYSTEM ARCHIVE LOG archive_log_clause
   ALTER SYSTEM CHECKPOINT [GLOBAL | LOCAL]
   ALTER SYSTEM CHECK DATAFILES [GLOBAL | LOCAL]
   ALTER SYSTEM FLUSH SHARED_POOL
   ALTER SYSTEM {ENABLE | DISABLE} DISTRIBUTED RECOVERY
   ALTER SYSTEM {ENABLE | DISABLE} RESTRICTED SESSION
   ALTER SYSTEM RESUME
   ALTER SYSTEM SUSPEND
   ALTER SYSTEM SHUTDOWN [IMMEDIATE] dispatcher_name
   ALTER SYSTEM SWITCH LOGFILE
   ALTER SYSTEM KILL SESSION 'int1, int2' [POST TRANSACTION] [IMMEDIATE]
   ALTER SYSTEM DISCONNECT SESSION 'int1, int2' [IMMEDIATE]
   ALTER SYSTEM SET parameter = value [COMMENT 'text'] [DEFERRED] [Scope_options]
   ALTER SYSTEM RESET parameter(s) [scope_options]
   ALTER SYSTEM QUIESCE RESTRICTED
   ALTER SYSTEM UNQUIESCE

SET Parameters:
   Full list of Static and dynamic parameters.

   Scope_options:
   SCOPE = {MEMORY|SPFILE|BOTH} [SID = 'sid' [,SID =…]]

   DEFERRED - Set parameter values for future connecting sessions.
              Currently active sessions are not affected and they retain the old parameter value.
              This is required for parameters that have the ISSSYS_MODIFIABLE =DEFERRED (in V$PARAMETER) 
              This is optional for parameters that have the ISSSYS_MODIFIABLE =IMMEDIATE (in V$PARAMETER)
              For static parameters, DEFERRED cannot be specified.

   COMMENT text is visible in the UPDATE_COMMENT column of V$SpPARAMETER

   The SID clause is for RAC, where it's possible to change a parameter value for one instance only.
   SID= '*' will affect all instances on the cluster
   If the instance is started with an SpFILE the default = * (all instances)
   If the instance is started with a PFILE then default SID = the current instance.

Quiesce
Putting the database into a quiesced state will prevent inactive user sessions from becoming active, Oracle then waits for existing transactions to finish. In shared server mode, Oracle will also block user logins.
This allows SYS/SYSTEM to perform some maintenance tasks without a full shutdown.
Notes
All Byte values can also be specified in K or M or G
e.g. you can enter 8388608 or 8192 K or 8M
All directory paths follow standard notation i.e UNIX 'quotes' or Windows "double quotes"
"An expert is someone who knows some of the worst mistakes that can be made in his subject and how to avoid them." - Werner Heisenberg