Specifying Values in the Parameter File

This section describes several aspects of setting parameter values in the parameter file. The following topics are included:

• Rules Governing Parameter Files

• Using Special Characters in Parameter Values

• Changing Parameter Values

• Displaying Current Parameter Values

• Uses of Parameters

• Types of Parameters

• Parameters You Should Not Specify in the Parameter File

• When Parameters Are Set Incorrectly

Rules Governing Parameter Files

The following rules govern the specification of parameters in the parameter file:

• All parameters are optional. The server has a default value for each required parameter. This value may be operating system dependent, depending on the parameter.

• The parameter file should contain only parameters and comments. A pound sign (#) starts a comment. The rest of the line is ignored.

• You can specify parameters in any order.

• Case (upper or lower) in filenames is significant only if case is significant on the host operating system.

• To enter several parameters on one line, use spaces between parameter names and values, as in the following example:

  PROCESSES = 100 CPU_COUNT = 1 OPEN_CURSORS = 10
  
  

• Some parameters, such as ROLLBACK_SEGMENTS, accept multiple value entries. Enter multiple values enclosed in parentheses and separated by commas. For example:

  ROLLBACK_SEGMENTS = (SEG1, SEG2, SEG3, SEG4, SEG5)
  
  

  Alternatively, you can enter multiple values without parentheses and commas. For example:

  ROLLBACK_SEGMENTS = SEG1 SEG2 SEG3 SEG4 SEG5
  
  

  Either syntax is valid.

  If you enter values for one parameter in multiple entries, the entries must be on consecutive lines. If they are not, the first entry will not be processed properly. For example, in the following entry:

  ROLLBACK_SEGMENTS = SEG1 SEG2
  OPEN_CURSORS = 10
  ROLLBACK_SEGMENTS = SEG3 SEG4
  
  

  the setting for SEG3 and SEG4 will override the setting for SEG1 and SEG2.

• A backslash or escape character (\) indicates continuation of the parameter specification. If a backslash continues a line, the continued line must have no leading spaces. For example:

  ROLLBACK_SEGMENTS = (SEG1, SEG2, \
  SEG3, SEG4, SEG5)
  
  

• You can use the IFILE parameter to call another parameter file, which must be in the same format as the original parameter file. See "IFILE".

• Enclose in quotation marks any parameter values that contain spaces or tabs. You can use either single or double quotation marks unless otherwise indicated. For example:

  NLS_TERRITORY = "CZECH REPUBLIC"
  

  Suggestion: Listing parameters in alphabetical order in the parameter file can help you to find them and can help ensure that each parameter is specified only once.

• Enclose in double quotation marks any parameter value that contains a special character (described in the section that follows).

See your operating system specific Oracle documentation for more information on parameter files.

Using Special Characters in Parameter Values

If a parameter value contains a special character, then the special character must be preceded by an escape character or the entire parameter value must be enclosed in double quotation marks. For example:

DB_DOMAIN = "JAPAN.ACME#.COM"

or

DB_DOMAIN = JAPAN.ACME\#.COM

Table 1-1 lists the special characters you can use in parameter files.

Table 1-1 Special Characters in the Initialization Parameter File

Character	Description
#	Comment
(	Start list of values
)	End list of values
"	Start or end of quoted string
'	Start or end of quoted string
=	Separator of keyword and value(s)
,	Separator of elements
-	Precedes UNIX-style keywords
\	Escape character

If a special character must be treated literally in the initialization parameter file, it must either preceded by the escape character or the entire string that contains the special character must be enclosed in single or double quotation marks.

Using the Escape Character

As described in "Rules Governing Parameter Files", the escape character (\) can also signify a line continuation. If the escape character is followed by an alphanumeric character, then the escape character is treated as a normal character in the input. If it is not followed by an alphanumeric, then the escape character is treated either as an escape character or as a continuation character.

Using Quotes

Quotes can be nested in any of three ways. The first method is to double the quotation marks in the nested string. For example:

NLS_DATE_FORMAT = """Today is"" MM/DD/YYYY"

The second method is to alternate single and double quotation marks. For example:

NLS_DATE_FORMAT = '"Today is" MM/DD/YYYY'

The third method is to precede the inner quotation marks with an escape character. For example:

NLS_DATE_FORMAT = "\"Today is\" MM/DD/YYYY"

Changing Parameter Values

You change the value of a parameter by editing the parameter file. In most cases, the new value takes effect the next time you start an instance of the database. However, you can change the value of some parameters for the duration of the current session, as discussed in the section that follows.

Dynamic Parameters

Some initialization parameters are dynamic, that is, they can be modified using the ALTER SESSION or ALTER SYSTEM command while an instance is running.

Use this syntax for dynamically altering the initialization parameters:

ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value [DEFERRED]

Whenever a dynamic parameter is modified using the ALTER SYSTEM command, Oracle records in the alert log the command that modifies the parameter.

The ALTER SESSION command changes the value of the specified parameter for the duration of the session that invokes this command. The value of this parameter does not change for other sessions in the instance. The value of the initialization parameters listed in Table 1-2 can be changed with ALTER SESSION.

The ALTER SYSTEM command without the DEFERRED keyword modifies the global value of the parameter for all sessions in the instance, for the duration of the instance (until the database is shut down). The value of the initialization parameters listed in Table 1-3 can be changed with ALTER SYSTEM.

The ALTER SYSTEM...DEFERRED command does not modify the global value of the parameter for existing sessions, but the value will be modified for future sessions that connect to the database. The value of the initialization parameters listed in Table 1-4 can be changed with ALTER SYSTEM...DEFERRED.

Table 1-2 Initialization Parameters Alterable with ALTER SESSION

CURSOR_SHARING DB_BLOCK_CHECKING DB_FILE_MULTIBLOCK_READ_COUNT FAST_START_IO_TARGET GLOBAL_NAMES HASH_AREA_SIZE HASH_JOIN_ENABLED HASH_MULTIBLOCK_IO_COUNT LOG_ARCHIVE_DEST_n LOG_ARCHIVE_DEST_STATE_n LOG_ARCHIVE_MIN_SUCCEED_DEST MAX_DUMP_FILE_SIZE NLS_CALENDAR NLS_COMP NLS_CURRENCY NLS_DATE_FORMAT NLS_DATE_LANGUAGE NLS_DUAL_CURRENCY NLS_ISO_CURRENCY NLS_LANGUAGE NLS_NUMERIC_CHARACTERS NLS_SORT

NLS_TERRITORY OBJECT_CACHE_MAX_SIZE_PERCENT OBJECT_CACHE_OPTIMAL_SIZE OPTIMIZER_INDEX_CACHING OPTIMIZER_INDEX_COST_ADJ OPTIMIZER_MAX_PERMUTATIONS OPTIMIZER_MODE OPTIMIZER_PERCENT_PARALLEL PARALLEL_BROADCAST_ENABLED PARALLEL_INSTANCE_GROUP PARALLEL_MIN_PERCENT PARTITION_VIEW_ENABLED PLSQL_V2_COMPATIBILITY QUERY_REWRITE_ENABLED QUERY_REWRITE_INTEGRITY REMOTE_DEPENDENCIES_MODE SESSION_CACHED_CURSORS SORT_AREA_RETAINED_SIZE SORT_AREA_SIZE SORT_MULTIBLOCK_READ_COUNT STAR_TRANSFORMATION_ENABLED TIMED_STATISTICS

Table 1-3 Initialization Parameters Alterable with ALTER SYSTEM

AQ_TM_PROCESSES BACKGROUND_DUMP_DEST CONTROL_FILE_RECORD_KEEP_TIME CORE_DUMP_DEST CURSOR_SHARING DB_BLOCK_CHECKSUM DB_BLOCK_MAX_DIRTY_TARGET DB_FILE_MULTIBLOCK_READ_COUNT FAST_START_IO_TARGET FAST_START_PARALLEL_ROLLBACK FIXED_DATE GC_DEFER_TIME GLOBAL_NAMES HASH_MULTIBLOCK_IO_COUNT HS_AUTOREGISTER JOB_QUEUE_PROCESSES LICENSE_MAX_SESSIONS LICENSE_MAX_USERS LICENSE_SESSIONS_WARNING LOG_ARCHIVE_DEST LOG_ARCHIVE_DEST_n LOG_ARCHIVE_DEST_STATE_n LOG_ARCHIVE_DUPLEX_DEST

LOG_ARCHIVE_MAX_PROCESSES LOG_ARCHIVE_MIN_SUCCEED_DEST LOG_ARCHIVE_TRACE LOG_CHECKPOINT_INTERVAL LOG_CHECKPOINT_TIMEOUT MAX_DUMP_FILE_SIZE MTS_DISPATCHERS MTS_SERVERS OPTIMIZER_MAX_PERMUTATIONS PARALLEL_ADAPTIVE_MULTI_USER PARALLEL_INSTANCE_GROUP PARALLEL_THREADS_PER_CPU PLSQL_V2_COMPATIBILITY QUERY_REWRITE_ENABLED QUERY_REWRITE_INTEGRITY REMOTE_DEPENDENCIES_MODE RESOURCE_LIMIT RESOURCE_MANAGER_PLAN STANDBY_ARCHIVE_DEST TIMED_OS_STATISTICS TIMED_STATISTICS USER_DUMP_DEST

Table 1-4 Initialization Parameters Alterable with ALTER SYSTEM . . . DEFERRED

BACKUP_TAPE_IO_SLAVES DB_BLOCK_CHECKING DB_FILE_DIRECT_IO_COUNT MAX_DUMP_FILE_SIZE OBJECT_CACHE_MAX_SIZE_PERCENT OBJECT_CACHE_OPTIMAL_SIZE

PLSQL_V2_COMPATIBILITY SORT_AREA_RETAINED_SIZE SORT_AREA_SIZE SORT_MULTIBLOCK_READ_COUNT TRANSACTION_AUDITING

Displaying Current Parameter Values

To see the current settings for initialization parameters, use the following SQL*Plus statement:

SHOW PARAMETERS

This statement displays all parameters in alphabetical order, with their current values.

Enter the following text string to display all parameters having BLOCK in their name:

SHOW PARAMETERS BLOCK

You can use the SPOOL command to write the output to a file.

Uses of Parameters

Initialization parameters fall into various functional groups. For example, parameters perform the following functions:

• Set limits for the entire database

• Set user or process limits

• Name files or directories required by a database system

• Set limits on database resources

• Affect performance (these are called variable parameters)

  The variable parameters are of particular interest to database administrators, because these parameters are used primarily to improve database performance.

Types of Parameters

An Oracle server has the following types of initialization parameters:

• Derived Parameters

• Global Cache Parameters with the Prefix GC

• Operating System Dependent Parameters

• Variable Parameters (these can be dynamic parameters or any of the above)

• Heterogeneous Services Parameters

Derived Parameters

Some initialization parameters are called derived, meaning that their values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, the value you specify will override the calculated value.

For example, the default value of the SESSIONS parameter is derived from the value of the PROCESSES parameter. If the value of PROCESSES changes, the default value of SESSIONS changes as well, unless you override it with a specified value.

Global Cache Parameters with the Prefix GC

Initialization parameters with the prefix GC, such as GC_DEFER_TIME, apply to systems using the Oracle Parallel Server. The prefix GC stands for "global cache." The settings of these parameters determine how the Oracle Parallel Server coordinates multiple instances. The settings you choose have an effect on the use of specific operating system resources.

See Also: Oracle8i Parallel Server Concepts for more information about the Oracle Parallel Server. Your system release bulletins or other Oracle documentation on your operating system for information on Oracle Parallel Server parameters.

Operating System Dependent Parameters

The valid values or value ranges of some initialization parameters depend upon the host operating system. For example, the parameter DB_BLOCK_BUFFERS indicates the number of data buffers in main memory, and its maximum value depends on the operating system. The size of those buffers, set by DB_BLOCK_SIZE, has a system dependent default value.

See Also: The Oracle documentation for your operating system for more information on operating system dependent Oracle parameters and operating system parameters.

Variable Parameters

The variable initialization parameters offer the most potential for improving system performance. Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its eleventh cursor receives an error. Other variable parameters affect performance but do not impose absolute limits. For example, reducing the value of DB_BLOCK_BUFFERS does not prevent work even though it may slow down performance.

Increasing the values of variable parameters may improve your system's performance, but increasing most parameters also increases the system global area (SGA) size. A larger SGA can improve database performance up to a point. In virtual memory operating systems, an SGA that is too large can degrade performance if it is swapped in and out of memory. Operating system parameters that control virtual memory working areas should be set with the SGA size in mind. The operating system configuration can also limit the maximum size of the SGA.

Heterogeneous Services Parameters

A number of parameters are specific to Oracle Heterogeneous Services. These parameters must be set at gateways using the DBMS_HS package.

See Also: Oracle8i Distributed Database Systems for information about specifying these parameters.

Parameters You Should Not Specify in the Parameter File

Normally you should not specify two types of parameters in the parameter file:

• Parameters that you never alter except when instructed to do so by Oracle Corporation to resolve a problem

• Derived parameters, which normally do not need altering because their values are calculated automatically by the Oracle server

When Parameters Are Set Incorrectly

Some parameters have a minimum setting below which an Oracle instance will not start. For other parameters, setting the value too low or too high may cause Oracle to perform badly, but it will still run. Also, Oracle may convert some values outside the acceptable range to usable levels.

If a parameter value is too low or too high or you have reached the maximum for some resource, Oracle returns an error. Frequently, you can wait a short while and retry the operation when the system is not as busy. If a message occurs repeatedly, you should shut down the instance, adjust the relevant parameter, and restart the instance.