Oracle9i Database Reference Release 1 (9.0.1) Part Number A90190-02 |
|
Initialization Parameters, 3 of 220
This section describes several aspects of setting parameter values in the parameter file. The following topics are included:
The following rules govern the specification of parameters in the parameter file:
PROCESSES = 100 CPU_COUNT = 1 OPEN_CURSORS = 10
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 the setting for SEG3
and SEG4
will override the setting for SEG1
and SEG2
:
ROLLBACK_SEGMENTS = SEG1 SEG2 OPEN_CURSORS = 10 ROLLBACK_SEGMENTS = SEG3 SEG4
ROLLBACK_SEGMENTS = (SEG1, SEG2, \ SEG3, SEG4, SEG5)
IFILE
initialization parameter to call another parameter file, which must be in the same format as the original parameter file.
NLS_TERRITORY = "CZECH REPUBLIC"
See Also:
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.
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.
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.
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"
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.
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
.
|
|
|
|
|
|
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.
Initialization parameters fall into various functional groups. For example, parameters perform the following functions:
The variable parameters are of particular interest to database administrators, because these parameters are used primarily to improve database performance.
The Oracle database server has the following types of initialization 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.
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:
|
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.
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.
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 |
You should not specify the following two types of parameters in your parameter files:
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.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|