Oracle7 Server Reference Manual
Specifying Values in the Parameter File
This section discusses several aspects of setting parameter values in the parameter file. The following topics are included:
- changing parameter value names
- displaying current parameter values
- parameters you should not specify in the parameter file
- when parameters are set incorrectly
Rules
The following rules govern the specification of parameters in the parameter file:
- All parameters are optional.
- Only parameters and comments should appear in the parameter file.
- A pound sign (#) starts a comment; the rest of the line is ignored.
- The Server has a default value for each parameter. This value may be operating system dependent, depending on the parameter.
- Parameters can be specified in any order.
- Case (upper or lower) in filenames is only significant 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:
PROCESSES = 100 SAVEPOINTS = 5 OPEN_CURSORS = 10
- Some parameters, such as ROLLBACK_SEGMENTS, accept multiple value entries. Multiple values must be enclosed in parentheses and separated by commas, as in the following:
ROLLBACK_SEGMENTS = (SEG1, SEG2, SEG3, SEG4, SEG5)
- A backslash (\) indicates continuation of the parameter specification. If a backslash continues a line, the continued line must have no preceding spaces. For example:
ROLLBACK_SEGMENTS = (SEG1, SEG2, \
- The keyword IFILE can be used to call another parameter file, which must be in the same format as the original parameter file. Up to three levels of nesting are allowed.
- Enclose parameter values that contain special characters, for example, filenames, in double quotes.
Suggestion: It is advisable to list parameters in alphabetical order in the parameter file. That makes it easier to find them and helps ensure that each parameter is specified only once. If a parameter is specified more than once, the last value encountered is the one used.
See your operating system specific Oracle documentation for more information on parameter files.
Changing Parameter Values
To change a parameter's value, edit the parameter file. The next time the instance starts, it uses the new parameter values in the updated parameter file. Note that the change does not take effect until the instance is shut down and restarted.
Displaying Current Parameter Values
To see the current settings for initialization parameters, use the following SQL command:
SHOW PARAMETERS
This displays all parameters in alphabetical order, with their current values.
Enter the following text string to see a display for all parameters having BLOCK in their name.:
SVRMGR> SHOW PARAMETERS BLOCK
If you display all the parameters, you may wish to use the SPOOL command to write the output to a file.
Uses of Parameters
Initialization parameters can be grouped by function in several different ways. For example, there are parameters that perform the following functions:
- 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 set of variable parameters are of particular interest to database administrators because these parameters are used primarily for improving database performance.
Types of Parameters
The Oracle7 Server has the following types of initialization parameters:
- global constant parameters
- operating system dependent parameters
Derived Parameters
Some initialization parameters are noted as derived. This means 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 overrides the calculated value.
Dynamic Parameters
Some initialization parameters can be modified using the ALTER SYSTEM or the ALTER SESSION commands while an instance is running. These dynamic initialization parameters include the following:
Note that these three parameters can only be changed at the session level using the ALTER SESSION command, not at the system level.
The syntax for dynamically altering the initialization parameters is as follows:
ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value
The ALTER SESSION command changes the value of the parameter until the database is shut down.
The ALTER SYSTEM command modifies the global value of the parameter and survives database shutdown and startup. The ALTER SYSTEM command does not always change the parameter value for the current session. Use the ALTER SESSION command to change the parameter value for the current session.
Global Constants Parameters with Prefix GC
Initialization parameters with the prefix GC, such as GC_DB_LOCKS, apply to systems using the Oracle7 Parallel Server. The prefix GC stands for Global Constants. The settings of these parameters determine how the Oracle7 Parallel Server coordinates multiple instances. The settings you choose have an effect on the use of certain operating system resources.
Additional Information: For more information about the Parallel Server, see the Oracle7 Parallel Server Concepts & Administration manual.
See your system release bulletins or other operating system specific Oracle documentation for platform specific information on Parallel Server parameters.
Operating System Dependent Parameters
For some initialization parameters, the valid values or ranges depend upon the host operating system. This is denoted in the default, or range, column as operating system-dependent. 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 your operating system specific Oracle documentation 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 11th 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.
Parameters You Should Not Specify in the Parameter File
The following types of parameters may never have to be specified in the parameter file:
- parameters that you never alter except when instructed to do so by Oracle Corporation to resolve a problem
- derived parameters that normally do not need altering because their values are automatically calculated by Oracle7 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 still runs.
You may see messages indicating that a parameter value is too low or too high, or that you have reached the maximum for some resource. 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.