Oracle9i Database Reference
Release 1 (9.0.1)

Part Number A90190-02
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

Initialization Parameters, 3 of 220


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

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

Using Special Characters in Parameter Values

If a parameter value contains a special character, then the special character must be preceded by a backslash or the entire parameter value must be enclosed in double quotation marks. For example, you can specify special characters using either of the following:

DB_DOMAIN = "JAPAN.ACME#.COM"

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  Name  Description 

# 

Number sign 

Comment 

( 

Left parenthesis 

Start list of values 

) 

Right parenthesis 

End list of values 

" 

Double quotation mark 

Start or end of quoted string 

' 

Single quotation mark 

Start or end of quoted string 

= 

Equal sign 

Separator of keyword and values 

, 

Comma 

Separator of elements 

- 

Minus sign 

Precedes UNIX-style keywords 

\ 

Backslash 

Escape character 

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

Using the Escape Character

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

Using Quotation Marks

Quotation marks 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 a backslash. For example:

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

Changing Parameter Values

You change the value of a parameter by editing the initialization 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 described in the following section.

Dynamic Parameters

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

Use the following syntax to dynamically alter 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 statement, Oracle records the command that modifies the parameter in the alert log.

The ALTER SESSION statement changes the value of the specified parameter for the duration of the session that invokes this statement. 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 statement 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 statement 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_CREATE_FILE_DEST 

DB_CREATE_ONLINE_LOG_DEST_n 

DB_FILE_MULTIBLOCK_READ_COUNT 

GLOBAL_NAMES 

HASH_AREA_SIZE 

HASH_JOIN_ENABLED 

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_LENGTH_SEMANTICS 

NLS_NCHAR_CONV_EXCP 

NLS_NUMERIC_CHARACTERS 

NLS_SORT 

NLS_TERRITORY 

NLS_TIMESTAMP_FORMAT 

NLS_TIMESTAMP_TZ_FORMAT 

OBJECT_CACHE_MAX_SIZE_PERCENT 

OBJECT_CACHE_OPTIMAL_SIZE 

OPTIMIZER_INDEX_CACHING 

OPTIMIZER_INDEX_COST_ADJ 

OPTIMIZER_MAX_PERMUTATIONS 

OPTIMIZER_MODE 

ORACLE_TRACE_ENABLE 

PARALLEL_BROADCAST_ENABLED 

PARALLEL_INSTANCE_GROUP 

PARALLEL_MIN_PERCENT 

PARTITION_VIEW_ENABLED 

PLSQL_COMPILER_FLAGS 

PLSQL_V2_COMPATIBILITY 

QUERY_REWRITE_ENABLED 

QUERY_REWRITE_INTEGRITY 

REMOTE_DEPENDENCIES_MODE 

SESSION_CACHED_CURSORS 

SORT_AREA_RETAINED_SIZE 

SORT_AREA_SIZE 

STAR_TRANSFORMATION_ENABLED 

TIMED_OS_STATISTICS 

TIMED_STATISTICS 

TRACEFILE_IDENTIFIER 

UNDO_SUPPRESS_ERRORS 

WORKAREA_SIZE_POLICY 

Table 1-3 Initialization Parameters Alterable with ALTER SYSTEM

AQ_TM_PROCESSES 

ARCHIVE_LAG_TARGET 

BACKGROUND_DUMP_DEST 

CONTROL_FILE_RECORD_KEEP_TIME 

CORE_DUMP_DEST 

CURSOR_SHARING 

DB_nK_CACHE_SIZE 

DB_BLOCK_CHECKING 

DB_BLOCK_CHECKSUM 

DB_CACHE_ADVICE 

DB_CACHE_SIZE 

DB_CREATE_FILE_DEST 

DB_CREATE_ONLINE_LOG_DEST_n 

DB_FILE_MULTIBLOCK_READ_COUNT 

DB_KEEP_CACHE_SIZE 

DB_RECYCLE_CACHE_SIZE 

DISPATCHERS 

DRS_START 

FAL_CLIENT 

FAL_SERVER 

FAST_START_IO_TARGET 

FAST_START_MTTR_TARGET 

FAST_START_PARALLEL_ROLLBACK 

FIXED_DATE 

GLOBAL_NAMES 

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 

LOG_CHECKPOINTS_TO_ALERT 

MAX_DUMP_FILE_SIZE 

MTS_DISPATCHERS 

MTS_SERVERS 

NLS_LENGTH_SEMANTICS 

NLS_NCHAR_CONV_EXCP 

OPEN_CURSORS 

ORACLE_TRACE_ENABLE 

PARALLEL_ADAPTIVE_MULTI_USER 

PARALLEL_INSTANCE_GROUP 

PARALLEL_THREADS_PER_CPU 

PGA_AGGREGATE_TARGET 

PLSQL_COMPILER_FLAGS 

PLSQL_NATIVE_C_COMPILER 

PLSQL_NATIVE_LIBRARY_DIR 

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT 

PLSQL_NATIVE_LINKER 

PLSQL_NATIVE_MAKE_FILE_NAME 

PLSQL_NATIVE_MAKE_UTILITY 

PLSQL_V2_COMPATIBILITY 

QUERY_REWRITE_ENABLED 

QUERY_REWRITE_INTEGRITY 

REMOTE_DEPENDENCIES_MODE 

RESOURCE_LIMIT 

RESOURCE_MANAGER_PLAN 

SERVICE_NAMES 

SHARED_POOL_SIZE 

SHARED_SERVERS 

STANDBY_ARCHIVE_DEST 

STANDBY_FILE_MANAGEMENT 

STANDBY_PRESERVES_NAMES 

TIMED_OS_STATISTICS 

TIMED_STATISTICS 

TRACE_ENABLED 

UNDO_RETENTION 

UNDO_SUPPRESS_ERRORS 

UNDO_TABLESPACE 

USER_DUMP_DEST 

WORKAREA_SIZE_POLICY 

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

BACKUP_TAPE_IO_SLAVES 

OBJECT_CACHE_MAX_SIZE_PERCENT 

OBJECT_CACHE_OPTIMAL_SIZE 

SORT_AREA_RETAINED_SIZE 

SORT_AREA_SIZE 

TRANSACTION_AUDITING 

Displaying Current Parameter Values

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

SQL> 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 names:

SQL> 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:

Types of Parameters

The Oracle database server has the following types of initialization parameters:

Derived Parameters

Some initialization parameters are 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_FILES_TO_LOCKS, apply to systems using Oracle9i Real Application Clusters. The prefix GC stands for global cache. The settings of these parameters determine how Oracle9i Real Application Clusters coordinates multiple instances. The settings you choose have an effect on the use of specific operating system resources.

See Also:

  • Oracle9i Real Application Clusters Concepts for more information about Oracle9i Real Application Clusters

  • Your system release bulletins or other operating system-specific Oracle documentation for information on Oracle9i Real Application Clusters 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:

Oracle9i Heterogeneous Connectivity Administrator's Guide for information about specifying Heterogeneous Services parameters 

Parameters You Should Not Specify in the Parameter File

You should not specify the following two types of parameters in your parameter files:

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.


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback