1
Initialization Parameters

This chapter contains detailed descriptions of the database initialization parameters.

The following topics are included in this chapter:

• Initialization Parameter File
• Specifying Values in the Parameter File
• Reading the Parameter Descriptions
• Parameter Descriptions

Initialization Parameter File

The initialization parameter file is a text file that contains a list of parameters and a value for each parameter. The file should be written in the client's default character set. Specify values in the parameter file which reflect your installation.

The following are sample entries in a parameter file:

PROCESSES = 100 
OPEN_LINKS = 12 
GLOBAL_NAMES = TRUE

The name of the parameter file varies depending on the operating system. For example, it can be in mixed case or lowercase, or it can have a logical name or a variation on the name INIT.ORA. As the database administrator, you can choose a different filename for your parameter file. There is also an INITDW.ORA file which contains suggested parameter settings for data warehouses and data marts.

See your Oracle operating system-specific documentation for the default locations and filenames for these parameter files. The INIT.ORA file is what the Oracle Server reads for its parameter information upon startup.

Sample parameter files are provided on the Oracle Server distribution medium for each operating system. A distributed sample file is sufficient for initial use, but you will want to make changes in the file to tune the database system for best performance. Any changes will take effect the next time you completely shut down the instance and then restart it.

Database administrators can use initialization parameters to do the following:

• optimize performance by adjusting memory structures, for example, the number of database buffers in memory
• set some database-wide defaults, for example, how much space is initially allocated for a context area when it is created
• set database limits, for example, the maximum number of database users
• specify names of files

Many initialization parameters can be fine-tuned to improve database performance. Other parameters should never be altered or only be altered under the supervision of Oracle Corporation Worldwide Support staff.

Note: If you are using Trusted Oracle, see your Trusted Oracle information for more details.

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
• Using Special Characters in 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

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. You can enter multiple values enclosed in parentheses and separated by commas. For example:

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

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

  ROLLBACK_SEGMENTS = SEG1 SEG2 SEG3 SEG4 SEG5
  
  

  Either syntax is valid.

• A backslash or escape character (\) 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, \
  SEG3, SEG4, SEG5)
  
  

• The keyword IFILE can be used to call another parameter file, which must be in the same format as the original parameter file.
• If a parameter is specified consecutively "n" number of times, it is associated with a list of "n" values. If a parameter is specified "n" number of times, but not all consecutively, then the last consecutive list of values is used.
• Enclose parameter values which contain whitespace in double quotes. For example:

  NLS_TERRITORY = "CZECH REPUBLIC"
  

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.

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 either the special character must be preceded by an escape character or the entire parameter value must be contained in double quotes. For example:

DB_DOMAIN = "JAPAN.ACME#.COM"

or

DB_DOMAIN = JAPAN.ACME\#.COM

Table 1-1 lists the special characters.

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 of end of quoted string
=	Separator of keyword and value(s)
,	Separator of elements
-	Precedes UNIX-style keywords
\	Escape character

Wherever a special character must be treated literally in the initialization parameter file, it must be either prefaced by the escape character or the entire string that contains the special character must be surrounded by single or double quotes.

Using the Escape Character

As described in "Rules" on page 1-2, 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. One method is to double the quotes in the nested string. For example:

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

Another method is to alternate single and double quotes. For example:

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

The third method is to escape the inner quotes. For example:

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

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.

Dynamic Parameters

Some initialization parameters are dynamic, that is, they can be modified using the ALTER SESSION, ALTER SYSTEM, or ALTER SYSTEM DEFERRED commands 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
ALTER SYSTEM SET parameter_name = value DEFERRED

Whenever a dynamic parameter is modified using the ALTER SYSTEM, or ALTER SYSTEM DEFERRED command, then the command that modifies the parameter is also recorded in the alert log.

The ALTER SESSION command changes the value of the parameter specific to 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.

Table 1-2 Initialization Parameters Alterable with ALTER SESSION

ALLOW_PARTIAL_SN_RESULTS	B_TREE_BITMAP_PLANS
CLOSE_CACHED_OPEN_CURSORS	COMPLEX_VIEW_MERGING
DB_FILE_MULTIBLOCK_READ _COUNT	GLOBAL_NAMES
HASH_AREA_SIZE	HASH_JOIN_ENABLED
HASH_MULTIBLOCK_IO_COUNT	JOB_QUEUE_PROCESSES
MAX_DUMP_FILE_SIZE	NLS_CURRENCY
NLS_CALENDAR	NLS_DATE_FORMAT
NLS_DATE_LANGUAGE	NLS_ISO_CURRENCY
NLS_LANGUAGE	NLS_NUMERIC_CHARACTERS
NLS_SORT	NLS_TERRITORY
OBJECT_CACHE_MAX_SIZE _PERCENT	OBJECT_CACHE_OPTIMAL_SIZE
OPS_ADMIN_GROUP	OPTIMIZER_MODE
OPTIMIZER_PERCENT_PARALLEL	OPTIMIZER_SEARCH_LIMIT
PARALLEL_INSTANCE_GROUP	PARALLEL_MIN_PERCENT
PARTITION_VIEW_ENABLED	PLSQLV2_COMPATIBILITY
REMOTE_DEPENDENCIES_MODE	PUSH_JOIN_PREDICATE
PARALLEL_ADAPTIVE_MULTI_USER	SESSION_CACHED_CURSORS
SORT_AREA_RETAINED_SIZE	SORT_AREA_SIZE
SORT_DIRECT_WRITES	SORT_READ_FAC
SORT_WRITE_BUFFER_SIZE	SORT_WRITE_BUFFERS
SPIN_COUNT	STAR_TRANSFORMATION_ENABLED
TEXT_ENABLE	TIMED_STATISTICS

The ALTER SYSTEM command modifies the global value of the parameter until the database is shut down. 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. The value of the initialization parameters listed in Table 1-3 can be changed with ALTER SYSTEM.

Table 1-3 Initialization Parameters Alterable with ALTER SYSTEM

AQ_TM_PROCESSES	BACKGROUND_DUMP_DEST
CONTROL_FILE_RECORD _KEEP_TIME	CORE_DUMP_DEST
DB_BLOCK_CHECKPOINT _BATCH	DB_BLOCK_CHECKSUM
DB_BLOCK_MAX_DIRTY _TARGET	DB_FILE_MULTIBLOCK_READ_COUNT
FIXED_DATE	FREEZE_DB_FOR_FAST_INSTANCE _RECOVERY
GC_DEFER_TIME	GLOBAL_NAMES
HASH_MULTIBLOCK_IO _COUNT	JOB_QUEUE_PROCESSES
LICENSE_MAX_SESSIONS	LICENSE_MAX_USERS
LICENSE_SESSIONS _WARNING	LOG_ARCHIVE_DUPLEX_DEST
LOG_ARCHIVE_MIN _SUCCEED_DEST	LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_TIMEOUT	LOG_SMALL_ENTRY_MAX_SIZE
MAX_DUMP_FILE_SIZE	MTS_DISPATCHERS
MTS_SERVERS	OPS_ADMIN_GROUP
PARALLEL_INSTANCE _GROUP	PARALLEL_TRANSACTION _RESOURCE_TIMEOUT
PLSQL_V2_COMPATIBILITY	REMOTE_DEPENDENCIES_MODE
RESOURCE_LIMIT	SPIN_COUNT
TEXT_ENABLE	TIMED_OS_STATISTICS
TIMED_STATISTICS	USER_DUMP_DEST

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-4 Initialization Parameters Alterable with ALTER SYSTEM DEFERRED

ALLOW_PARTIAL_SN_RESULTS	BACKUP_DISK_IO_SLAVES
BACKUP_TAPE_IO_SLAVES	DB_FILE_DIRECT_IO_COUNT
OBJECT_CACHE_MAX_SIZE _PERCENT	OBJECT_CACHE_OPTIMAL_SIZE
SORT_AREA_RETAINED_SIZE	SORT_AREA_SIZE
SORT_DIRECT_WRITES	SORT_READ_FAC
SORT_WRITE_BUFFER_SIZE	SORT_WRITE_BUFFERS
TRANSACTION_AUDITING

Displaying Current Parameter Values

To see the current settings for initialization parameters, use the following server manager command:

SVRMGR> 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 might want 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 database-wide limits
• 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 Oracle Server has the following types of initialization parameters:

• Derived Parameters
• Global Cache Parameters with 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 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.

Global Cache Parameters with 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 certain operating system resources.

Additional Information: For more information about the Parallel Server, see Oracle8 Parallel Server Concepts and Administration.

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.

Heterogeneous Services Parameters

There are a number of initialization parameters specific to Heterogeneous Services which must be set using a package called DBMS_HS.

For information about specifying these parameters, see Oracle8 Distributed Database Systems.

Parameters You Should Not Specify in the Parameter File

The following types of parameters might 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 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 still runs. Also, Oracle may convert some values outside the acceptable range to usable levels.

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.

Reading the Parameter Descriptions

The parameter descriptions in this chapter follow the format shown below.

PARAMETER_NAME

Parameter type:	Whether the type is integer, boolean, string, and so on.
Parameter class:	Whether the parameter is dynamic or static. If dynamic, then it also describes whether it can be changed by an ALTER SYSTEM or ALTER SESSION statement.
Default value:	The value this parameter assumes if not explicitly specified.
Range of values:	The valid range of values that this parameter can assume, shown as a minimum and maximum value. Not applicable to all parameters.
Multiple instances:	How the values for this parameter must be specified for multiple instances in an Oracle Parallel Server. Not applicable to all parameters.
OK to change:	Notes on changing the parameter value. Not specified for all releases.

The remaining paragraphs provide a textual description of the parameter and the effects of different settings.

For more information, see references to chapters or books that contain more detailed information on this subject.

Parameter Descriptions

Descriptions of the individual initialization parameters follow in alphabetical order.

Most initialization parameter values are global (on a database-wide basis), not per user, unless otherwise specified.

For more information, see your system release bulletins or other operating system-specific Oracle documentation.

O7_DICTIONARY_ACCESSIBILITY

Parameter type:	boolean
Parameter class:	static
Default value:	TRUE
Range of values:	TRUE, FALSE

O7_DICTIONARY_ACCESSIBILITY is intended to be used for migration from Oracle7 to Oracle8. O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to TRUE, access to objects in SYS schema is allowed (Oracle7 behavior). If this parameter is set to FALSE, SYSTEM privileges that allow access to objects in other schema do not allow access to objects in dictionary schema.

For example, if O7_DICTIONARY_ACCESSIBILITY=FALSE, then the SELECT ANY TABLE statement will allow access to views or tables in any schema except SYS schema (for example, dictionaries could not be accessed). The system privilege, EXECUTE ANY PROCEDURE will ALLOW ACCESS on the procedures in any other schema except in SYS schema.

If you need to access objects in the SYS schema, then you must be granted explicit object privilege. Also, the following roles, which can be granted to the database administrator, also allow access to dictionary objects: SELECT_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, and DELETE_CATALOG_ROLE.

For more information on this parameter and the roles mentioned above, see the Oracle8 Administrator's Guide.

ALLOW_PARTIAL_SN_RESULTS

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SESSION, ALTER SYSTEM DEFERRED
Default value:	FALSE
Range of values:	TRUE, FALSE
Multiple instances:	should have the same value

ALLOW_PARTIAL_SN_RESULTS is a Parallel Server parameter. This parameter allows for partial results to be returned on queries to global performance tables (GV$) even if a slave could not be allocated on the instance.

If the value of MAX_PARALLEL_SERVERS equals 0, then a query on the global dynamic performance table (GV$) will revert to a sequential query on the local instance. If the value of MAX_PARALLEL_SERVERS is greater than 0 and a slave cannot be allocated on an instance in a GV$ query, then the value of ALLOW_PARTIAL_SN_RESULTS determines whether the query returns partial results or returns a failure.

If ALLOW_PARTIAL_SN_RESULTS is TRUE, then the query succeeds and returns results from all of the instances which were able to allocate a slave for the query. If ALLOW_PARTIAL_SN_RESULTS is FALSE, then the query fails and returns an error message.

ALWAYS_ANTI_JOIN

Parameter type:	string
Parameter class:	static
Default value:	NESTED_LOOPS
Range of values:	NESTED_LOOPS/MERGE/HASH

ALWAYS_ANTI_JOIN sets the type of antijoin that the Oracle Server uses. The system checks to verify that it is legal to perform an antijoin, and if it is, processes the subquery depending on the value of this parameter. When set to the value NESTED_LOOPS, the Oracle Server uses a nested loop antijoin algorithm. When set to the value MERGE, the Oracle Server uses the sort merge antijoin algorithm. When set to the value HASH, the Oracle Server uses the hash antijoin algorithm to evaluate the subquery.

ALWAYS_SEMI_JOIN

Parameter type:	string
Parameter class:	static
Default value:	standard
Range of values:	NESTED_LOOPS/MERGE/HASH_SJ

ALWAYS_SEMI_JOIN sets the type of semijoin that the Oracle Server uses. The system checks to verify that it is legal to perform a semijoin, and if it is, processes the subquery depending on the value of this parameter. When set to the value NESTED_LOOPS, the Oracle Server uses a nested loop semijoin algorithm. When set to the value MERGE, it uses the sort merge semijoin algorithm. When set to the value HASH_SJ, it uses the hash semijoin algorithm.

AQ_TM_PROCESSES

Parameter type:	integer
Parameter class:	static, scope= ALTER SYSTEM
Default value:	0
Range of values:	0-10

AQ_TM_PROCESSES specifies whether a queue monitor is created. If set to 1, then one queue monitor process is created to monitor the messages. If AQ_TM_PROCESSES is not specified or is set to 0, then the queue monitor is not created.

For more information about this parameter and Advanced Queuing, see the Oracle8 Application Developer's Guide.

ARCH_IO_SLAVES

Parameter type:	integer
Parameter class:	static
Default value:	0
Range of values:	0 - 15

ARCH_IO_SLAVES specifies the number of I/O slaves used by the ARCH process to archive redo logfiles. The ARCH process and its slaves always write to disk. By default the value is 0 and I/O slaves are not used.

This parameter is normally adjusted when an I/O bottleneck has been detected in the ARCH process. Typically, I/O bottlenecks in this process will occur on platforms that do not support asynchronous I/O or implement it inefficiently.

AUDIT_FILE_DEST

Parameter type:	string
Parameter class:	static
Default value:	$ORACLE_HOME/RDBMS/AUDIT

AUDIT_FILE_DEST specifies the directory where auditing files are stored.

AUDIT_TRAIL

Parameter type:	string
Parameter class:	static
Default value:	NONE
Range of values:	NONE (FALSE), DB (TRUE), OS

AUDIT_TRAIL enables or disables the writing of rows to the audit trail. Audited records are not written if the value is NONE or if the parameter is not present. The OS option enables system-wide auditing and causes audited records to be written to the operating system's audit trail. The DB option enables system-wide auditing and causes audited records to be written to the database audit trail (the SYS.AUD$ table).

The values TRUE and FALSE are also supported for backward compatibility. TRUE is equivalent to DB, and FALSE is equivalent to NONE.

The SQL AUDIT statements can set auditing options regardless of the setting of this parameter.

For more information, see the Oracle8 Administrator's Guide.

B_TREE_BITMAP_PLANS

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	FALSE
Range of values:	TRUE/FALSE

B_TREE_BITMAP_PLANS allows the cost-based optimizer to consider a bitmap access path even when a table only has regular B-tree indexes. This parameter may improve the performance of certain queries when using the cost-based optimizer.

BACKGROUND_CORE_DUMP

Parameter type:	string
Parameter class:	static
Default value:	FULL
Range of values:	FULL/PARTIAL

BACKGROUND_CORE_DUMP specifies whether the SGA is dumped as part of the generated core file. When BACKGROUND_CORE_DUMP=FULL, the SGA is dumped as part of the generated core file. If BACKGROUND_CORE_DUMP=PARTIAL, then the SGA is not dumped as part of the generated core file.

BACKGROUND_DUMP_DEST

Parameter type:	string
Parameter class:	static
Default value:	operating system-dependent
Range of values:	valid local pathname, directory, or disk

BACKGROUND_DUMP_DEST specifies the pathname for a directory where debugging trace files for the background processes (LGWR, DBWR, and so on) are written during Oracle operations.

An ALERT file in the directory specified by BACKGROUND_DUMP_DEST logs significant database events and messages. Anything that affects the database instance-wide or globally is recorded here. This file records all instance start ups and shut downs, messages to the operator console, and errors that cause trace files to be written. It also records every CREATE, ALTER, or DROP operation on a database, tablespace, or rollback segment.

The ALERT file is a normal text file. Its filename is operating system-dependent. For platforms that support multiple instances, it takes the form ALERT_sid.LOG. This file grows slowly, but without limit, so the database administrator might want to delete it periodically. The file can be deleted even when the database is running.

For more information, see the Oracle8 Administrator's Guide. See your operating system-specific Oracle documentation for the default value.

BACKUP_DISK_IO_SLAVES

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM DEFERRED
Default value:	0
Range of values:	0 - 15 although a value under 7 is recommended

BACKUP_DISK_IO_SLAVES specifies the number of I/O slaves used by the Recovery Manager to backup, copy, or restore. Note that every Recovery Manager channel can get the specified number of I/O slave processes. By default, the value is 0 and I/O slaves are not used.

Typically I/O slaves are used to "simulate" asynchronous I/O on platforms that either do not support asynchronous I/O or implement it inefficiently. However, I/O slaves can be used even when asynchronous I/O is being used. In that case the I/O slaves will use asynchronous I/O.

BACKUP_TAPE_IO_SLAVES

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SYSTEM DEFERRED
Default value:	FALSE
Range of values:	TRUE/FALSE

BACKUP_TAPE_IO_SLAVES specifies whether I/O slaves are used by the Recovery Manager to backup, copy, or restore data to tape. When BACKUP_TAPE_IO_SLAVES = TRUE, an I/O slave process is used to write to or read from a tape device. If this parameter is FALSE (the default), then I/O slaves are not used for backups; instead, the shadow process engaged in the backup will access the tape device.

Note, as a tape device can only be accessed by one process at any given time, this parameter is a boolean, that allows or disallows deployment of an I/O slave process to access a tape device.

Typically I/O slaves are used to "simulate" asynchronous I/O on platforms that either do not support asynchronous I/O or implement it inefficiently. However, I/O slaves can be used even when asynchronous I/O is being used. In that case the I/O slaves will use asynchronous I/O.

BITMAP_MERGE_AREA_SIZE

Parameter type:	integer
Parameter class:	static
Default value:	1 Mb
Range of values:	system-dependent value

BITMAP_MERGE_AREA_SIZE parameter specifies the amount of memory used to merge bitmaps retrieved from a range scan of the index. The default value is 1 Mb. A larger value should improve performance because the bitmap segments must be sorted before being merged into a single bitmap. This parameter is not dynamically alterable at the session level.

BLANK_TRIMMING

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

BLANK_TRIMMING specifies the data assignment semantics of character datatypes. A value of TRUE allows the data assignment of a source character string/variable to a destination character column/variable even though the source length is longer than the destination length. In this case, however, the additional length over the destination length is all blanks. This is in compliance with SQL92 Transitional Level and above semantics. A value of FALSE disallows the data assignment if the source length is longer than the destination length and reverts to SQL92 Entry Level semantics.

BUFFER_POOL_KEEP

Parameter type:	string
Parameter class:	static
Default value:	none

BUFFER_POOL_KEEP is used to improve buffer cache performance. It allows you to keep an object in the buffer cache.

For more information, see Oracle8 Tuning.

BUFFER_POOL_RECYCLE

Parameter type:	string
Parameter class:	static
Default value:	none

BUFFER_POOL_RECYCLE is used to improve buffer cache performance. It allows you to limit the size of an object in the buffer cache.

For more information, see Oracle8 Tuning.

CACHE_SIZE_THRESHOLD

Parameter type:	integer
Parameter class:	static
Default value:	0.1*DB_BLOCK_BUFFERS
OK to change:	yes
Multiple instances:	should have the same value

CACHE_SIZE_THRESHOLD specifies the maximum size of a cached partition of a table split among the caches of multiple instances. If the partition is larger than the value of this parameter, the table is not split among the instances' caches. The default value of this parameter is 1/10 the number of database blocks in the buffer cache. This parameter can also specify the maximum cached partition size for a single instance.

The CACHE_SIZE_THRESHOLD parameter is being denigrated as of 8.0.3.

For more information, see Oracle8 Parallel Server Concepts and Administration.

CLEANUP_ROLLBACK_ENTRIES

Parameter type:	integer
Parameter class:	static
Default value:	20

CLEANUP_ROLLBACK_ENTRIES specifies the number of undo records processed at one time when rolling back a transaction. Prevents long transactions from freezing out shorter transactions that also need to be rolled back. Normally, this parameter will not need modification.

For more information, see the Oracle8 Administrator's Guide.

CLOSE_CACHED_OPEN_CURSORS

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

CLOSE_CACHED_OPEN_CURSORS specifies whether cursors opened and cached in memory by PL/SQL are automatically closed at each COMMIT. A value of FALSE signifies that cursors opened by PL/SQL are held open so that subsequent executions need not open a new cursor. If PL/SQL cursors are reused frequently, setting the parameter to FALSE can cause subsequent executions to be faster.

A value of TRUE causes open cursors to be closed at each COMMIT or ROLLBACK. The cursor can then be reopened as needed. If cursors are rarely reused, setting the parameter to TRUE frees memory used by the cursor when the cursor is no longer in use.

COMMIT_POINT_STRENGTH

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent
Range of values:	0 - 255

COMMIT_POINT_STRENGTH specifies a value that determines the commit point site in a distributed transaction. The node in the transaction with the highest value for COMMIT_POINT_STRENGTH will be the commit point site. A database's commit point strength should be set relative to the amount of critical shared data in the database. For example, a database on a mainframe computer typically shares more data among users than one on a personal computer. Therefore, COMMIT_POINT_STRENGTH should be set to a higher value for the mainframe computer.

The commit point site stores information about the status of transactions. Other computers in a distributed transaction require this information, so it is desirable to have machines that are always available as commit point sites. Therefore, set COMMIT_POINT_STRENGTH to a higher value on your more available machines.

For more information about two-phase commit, see Oracle8 Concepts and Oracle8 Distributed Database Systems. See also your operating system-specific Oracle documentation for the default value.

COMPATIBLE

Parameter type:	string
Parameter class:	static
Default value:	8.0.0
Range of values:	default release to current release
Multiple instances:	must have the same value

COMPATIBLE allows you to use a new release, while at the same time guaranteeing backward compatibility with an earlier release. This is in case it becomes necessary to revert to the earlier release. This parameter specifies the release with which the Oracle Server must maintain compatibility. Some features of the current release may be restricted.

When using the standby database and feature, this parameter must have the same value on the primary and standby databases, and the value must be 7.3.0.0.0 or higher.

This parameter allows you to immediately take advantage of the maintenance improvements of a new release in your production systems without testing the new functionality in your environment.

The default value is the earliest release with which compatibility can be guaranteed.

For more information, see Oracle8 Migration. See also your operating system-specific Oracle documentation for the default value.

COMPATIBLE_NO_RECOVERY

Parameter type:	string
Parameter class:	static
Default value:	release dependent
Range of values:	default version to current version
Multiple instances:	must have the same value

COMPATIBLE_NO_RECOVERY functions like the COMPATIBLE parameter, except that the earlier version may not be usable on the current database if recovery is needed.

The default value is the earliest version with which compatibility can be guaranteed. In some cases, this version may be earlier than the version which can be specified with the COMPATIBLE parameter.

For more information, see Oracle8 Migration. See also your operating system-specific Oracle documentation for the default value.

COMPLEX_VIEW_MERGING

Parameter type:	boolean
Parameter class:	dynamic, ALTER SESSION
Default value:	OFF
Range of values:	TRUE/FALSE

COMPLEX_VIEW_MERGING, when set to TRUE, causes complex views or subqueries to be merged. When set to FALSE, this parameter causes complex views or subqueries to be evaluated before the referencing query.

CONTROL_FILE_RECORD_KEEP_TIME

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	7 (days)
Range of values:	0 - 365 (days)

Records in some sections in the control file are circularly reusable while records in other sections are never reused. CONTROL_FILE_RECORD_KEEP_TIME applies to reusable sections. It specifies the minimum age in days that a record must have before it can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If CONTROL_FILE_RECORD_KEEP_TIME is set to 0, then reusable sections never expand and records are reused as needed.

Table 1-5 lists the names of reusable sections.

Table 1-5 Names of Reusable Sections

ARCHIVED LOG	BACKUP CORRUPTION
BACKUP DATAFILE	BACKUP PIECE
BACKUP REDO LOG	BACKUP SET
COPY CORRUPTION	DATAFILE COPY
DELETED OBJECT	LOGHISTORY
OFFLINE RANGE

CONTROL_FILES

Parameter type:	string
Parameter class:	static
Default value:	operating system-dependent
Range of values:	1 - 8 filenames

CONTROL_FILES specifies one or more names of control files, separated by commas. Oracle Corporation recommends using multiple files on different devices or mirroring the file at the OS level.

For more information, see the Oracle8 Administrator's Guide.

CORE_DUMP_DEST

Parameter type:	string
Parameter class:	static
Default value:	$ORACLE_HOME/DBS/

CORE_DUMP_DEST specifies the directory where core files are dumped.

CPU_COUNT

Parameter type:	integer
Parameter class:	static
Default value:	automatically set by Oracle
Range of values:	0 - unlimited
OK to change:	no

CPU_COUNT specifies the number of CPUs available to Oracle. Oracle uses it to set the default value of the LOG_SIMULTANEOUS_COPIES parameter. On single-CPU computers, the value of CPU_COUNT is 0.

Warning: On most platforms Oracle automatically sets the value of CPU_COUNT to the number of CPUs available to your Oracle instance. Do not change the value of CPU_COUNT.

If there is heavy contention for latches, change the value of LOG_SIMULTANEOUS_COPIES to twice the number of CPUs you have. Do not change the value of CPU_COUNT.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for information about this parameter.

CREATE_BITMAP_AREA_SIZE

Parameter type:	integer
Parameter class:	static
Default value:	8 Mb
Range of values:	operating system-dependent

CREATE_BITMAP_AREA_SIZE specifies the amount of memory allocated for bitmap creation. The default value is 8 Mb. A larger value might lead to faster index creation. If cardinality is very small, you can set a small value for this parameter. For example, if cardinality is only 2 then the value can be on the order of kilobytes rather than megabytes. As a general rule, the higher the cardinality, the more memory is needed for optimal performance. This parameter is not dynamically alterable at the session level.

CURSOR_SPACE_FOR_TIME

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

If CURSOR_SPACE_FOR_TIME is set to TRUE, the database uses more space for cursors to save time. It affects both the shared SQL area and the client's private SQL area.

Shared SQL areas are kept pinned in the shared pool when this parameter's value is TRUE. As a result, shared SQL areas are not aged out of the pool as long as there is an open cursor that references them. Because each active cursor's SQL area is present in memory, execution is faster. Because the shared SQL areas never leave memory while they are in use, however, you should set this parameter to TRUE only when the shared pool is large enough to hold all open cursors simultaneously.

Setting this parameter to TRUE also retains the private SQL area allocated for each cursor between executes instead of discarding it after cursor execution. This saves cursor allocation and initialization time.

For more information, see Oracle8 Concepts.

DB_BLOCK_BUFFERS

Parameter type:	integer
Parameter class:	static
Default value:	50 buffers
Range of values:	4 - operating system-specific
OK to change:	yes
Multiple instances:	can have different values

DB_BLOCK_BUFFERS specifies the number of database buffers available in the buffer cache. It is one of the primary parameters which contribute to the total memory requirements of the SGA on the instance. The DB_BLOCK_BUFFERS parameter, together with the DB_BLOCK_SIZE parameter, determines the total size of the buffer cache. Effective use of the buffer cache can greatly reduce the I/O load on the database. Since DB_BLOCK_SIZE can be specified only when the database is first created, use DB_BLOCK_BUFFERS to control the size of the buffer cache.

This parameter affects the probability that a data block will be pinged when Parallel Server is enabled: the more buffers, the more chance of pings.

For more information, see Oracle8 Concepts. See also your operating system-specific Oracle documentation for the default value.

DB_BLOCK_CHECKPOINT_BATCH

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM IMMEDIATE
Default value:	8
Range of values:	0 - derived

DB_BLOCK_CHECKPOINT_BATCH specifies the number of buffers that will be added to each batch of buffers that DBWR writes in order to advance checkpoint processing.

Reducing DB_BLOCK_CHECKPOINT_BATCH prevents the I/O system from being flooded with checkpoint writes and allows other modified blocks to be written to disk. Setting it to a higher value allows checkpoints to complete more quickly.

In general, DB_BLOCK_CHECKPOINT_BATCH should be set to a value that allows the checkpoint to complete before the next log switch takes place. If a log switch takes place every 20 minutes, then this parameter should be set to a value that allows check pointing to complete within 20 minutes.

Setting DB_BLOCK_CHECKPOINT_BATCH to zero causes the default value to be used. If an overly large value is specified for this parameter, Oracle (silently) limits it to the number of blocks that can be written in a database writer write batch.

For more information, see Oracle8 Concepts.

DB_BLOCK_CHECKSUM

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	FALSE
Range of values:	TRUE/FALSE

If DB_BLOCK_CHECKSUM is set to TRUE, DBWR and the direct loader will calculate a checksum and store it in the cache header of every data block when writing it to disk. Checksums will be verified when a block is read only if this parameter is TRUE and the last write of the block stored a checksum.

Warning: Setting DB_BLOCK_CHECKSUM to TRUE can cause performance overhead.

For more information, see the Oracle8 Administrator's Guide.

DB_BLOCK_LRU_EXTENDED_STATISTICS

Parameter type:	integer
Parameter class:	static
Default value:	0
Range of values:	0 - dependent on system memory capacity

DB_BLOCK_LRU_EXTENDED_STATISTICS disables or enables compilation of statistics which measures the effects of increasing the number of buffers in the buffer cache in the SGA. When this facility is enabled, it keeps track of the number of disk accesses that would be saved if additional buffers were allocated. A value greater than zero specifies the additional number of buffers (over DB_BLOCK_BUFFERS) for which statistics are kept. This tuning tool should be turned off during normal operation.

When compiling statistics, set this parameter to the maximum size you want to use to evaluate the buffer cache. It should be set to zero otherwise. (Although you can set this value very high, it is not practical to set it to a size beyond your system's memory capacity.)

Setting this parameter can cause a large performance loss, so it should only be set when the system is lightly loaded.

For more information, see the Oracle8 Administrator's Guide.

DB_BLOCK_LRU_LATCHES

Parameter type:	integer
Parameter class:	static
Default value:	CPU_COUNT/2
Range of values:	1 - the number of CPUs

DB_BLOCK_LRU_LATCHES specifies the upper bound of the number of LRU latch sets. Set this parameter to a value equal to the desired number of LRU latch sets. Oracle decides whether to use this value or reduce it based on a number of internal checks. If the parameter is not set, Oracle calculates a value for the number of sets. The value calculated by Oracle is usually adequate. Increase this only if misses are higher than 3% in V$LATCH.

DB_BLOCK_LRU_STATISTICS

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

DB_BLOCK_LRU_STATISTICS disables or enables compilation of statistics in the V$CURRENT_BUCKET table, which measures the effect of fewer buffers in the SGA buffer cache.

Set this parameter to TRUE when you want to compile statistics for the V$CURRENT_BUCKET table; otherwise, leave it set to FALSE. This parameter is a tuning tool and should be set to FALSE during normal operation.

Setting this parameter can cause a large performance loss, so it should only be set when the system is lightly loaded.

For more information, see Oracle8 Administrator's Guide and Oracle8 Tuning.

DB_BLOCK_MAX_DIRTY_TARGET

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM set at runtime
Default value:	all the buffers in the cache
Range of values:	100 to all buffers in the cache, setting to 0 disables incremental checkpoint buffer writes
OK to change:	yes, at run-time
Multiple instances:	can have different values

DB_BLOCK_MAX_DIRTY_TARGET specifies the number of buffers that can be dirty (modified and different from what is on disk). If the number of dirty buffers in a buffer cache exceeds this value, DBWR will write out buffers in order to try and keep the number of dirty buffers below the specified value.

Note that this parameter does not impose a hard limit on the number of dirty buffers; in other words, DBWR attempts to keep the number of dirty buffers below this value, but will NOT stop (or slow) database activity if the number of dirty buffers exceeds this value occasionally.

This parameter can be used to influence the amount of time it takes to perform instance recovery since recovery is related to the number of buffers that were dirty at the time of the crash. The smaller the value of this parameter, the faster the instance recovery. Note that this improvement in recovery time is achieved at the expense of writing more buffers during normal processing. Hence, setting this parameter to a very small value might adversely affect performance if the workload modifies large numbers of buffers.

Setting this value to 0 disables writing of buffers for incremental checkpointing purposes; all other write activity continues as before (that is, it is unaffected by setting this parameter to 0).

DB_BLOCK_SIZE

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent
Range of values:	operating system-dependent (2048 - 32768)
OK to change:	Only at database creation
Multiple instances:	must have the same value

DB_BLOCK_SIZE specifies the size in bytes of Oracle database blocks. Typical values are 2048 and 4096. The value for DB_BLOCK_SIZE in effect at CREATE DATABASE time determines the size of the blocks; at all other times the value must be set to the original value.

This parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes. DSS (data warehouse) database environments tend to benefit from larger block size values.

For more information about block size, see Oracle8 Concepts. See also your operating system-specific Oracle documentation for the default value.

DB_DOMAIN

Parameter type:	string
Parameter class:	static
Default value:	WORLD
Range of values:	any legal string of name components, separated by periods and up to 128 characters long, including periods (see valid characters below) -this value cannot be NULL
Multiple instances:	must have the same value

DB_DOMAIN specifies the extension components of a global database name, consisting of valid identifiers, separated by periods. Specifying DB_DOMAIN as a unique string for every database is highly recommended.

For example, this parameter allows one department to create a database without worrying that it might have the same name as a database created by another department. If one sales department's DB_DOMAIN = "JAPAN.ACME.COM", then their "SALES" database (SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with DB_NAME = "SALES" but with DB_DOMAIN = "US.ACME.COM".

The following characters are valid in a database domain name:

• alphabetic characters
• numbers
• underscore (_)
• pound (#)

For more information, see the Oracle8 Administrator's Guide.

DB_FILES

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent
Range of values:	minimum value: either the value that was specified in the MAXDATAFILES clause the last time CREATE DATABASE or CREATE CONTROLFILE was executed, or the current actual number of datafiles in the data maximum value: operating system-dependent
Multiple instances:	must have the same value

DB_FILES specifies the maximum number of database files that can be opened for this database. The maximum valid value for DB_FILES is the maximum number of files, subject to operating system constraint, that will ever be specified for the database, including files to be added by ADD DATAFILE statement.

If you increase the value of DB_FILES, you must shut down and restart all instances accessing the database before the new value can take effect.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the default value.

DB_FILE_DIRECT_IO_COUNT

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	64
Range of values:	operating system-dependent

DB_FILE_DIRECT_IO_COUNT is used to specify the number of blocks to be used for IO operations done by backup, restore or direct path read and write functions. The IO buffer size is a product of DB_FILE_DIRECT_IO_COUNT and DB_BLOCK_SIZE. The IO buffer size cannot exceed max_IO_size for your platform.

Assigning a high value to this parameter results in greater use of PGA or SGA memory.

DB_FILE_MULTIBLOCK_READ_COUNT

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM, ALTER SESSION
Default value:	8
Range of values:	operating system-dependent

DB_FILE_MULTIBLOCK_READ_COUNT is used for multi-block I/O and specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on factors such as these:

• the size of the table
• the multi-block read count
• whether parallel query is being utilized for the operation

The default is 8. OLTP and batch environments typically have values for this parameter in the range of 4 to 16. DSS (data warehouse) database environments tend to get the most benefit from maximizing the value for this parameter.

The actual maximums vary by operating system; they are always less than the operating system's maximum I/O size expressed as Oracle blocks (max_IO_size/DB_BLOCK_SIZE). Attempts to set this parameter to a value greater than the maximum will cause the maximum to be used.

For information on the optimizer, see Oracle8 Tuning. See also your operating system-specific Oracle documentation for the default value.

DB_FILE_NAME_CONVERT

Parameter type:	string
Parameter class:	static
Default value:	none
Range of values:	character string

Use DB_FILE_NAME_CONVERT to convert the filename of a new data file on the primary database to a filename on the standby database. Adding a datafile to the primary database necessitates adding a corresponding file to the standby database. When the standby database is updated, this parameter is used to convert the datafile name on the primary database to the a datafile name on the standby database. The file must exist and be writable on the standby database or the recovery process will halt with an error.

Set the value of this parameter to two strings: the first string is the pattern found in the datafile names on the primary database; the second string is the pattern found in the datafile names on the standby database.

DB_FILE_SIMULTANEOUS_WRITES

Parameter type:	integer
Parameter class:	static
Default value:	4
Range of values:	minimum: 1 maximum: when striping used: 4 times the number of disks in the file that is striped the most. when striping not used: 4

DB_FILE_SIMULTANEOUS_WRITES specifies the maximum number of simultaneous writes that can be made to a given database file. Oracle also uses the value of this parameter in computing various internal parameters that affect read and write operations to database files.

If you specify an excessively large value for this parameter, significant delays in performing read and write operations to a given database file might occur. This is because I/O requests get queued in the disk. If you set a value which is too small, the number of I/Os that can be issued to a given database file will be limited.

In environments where the database files reside on RAM devices or which use disk striping at the operating system level, it is beneficial to increase the value of this parameter. If striped files are used, Oracle recommends that you set the value of this parameter to 4 times the maximum number of disks in the file that is striped the most.

This parameter is also used to determine the number of reads-per-file in the redo read-ahead when reading redo during recovery.

For more information, see Oracle8 Tuning. See also your operating system-specific Oracle documentation for the default value.

DB_NAME

Parameter type:	string
Parameter class:	static
Default value:	NULL
Range of values:	any valid database name
Multiple instances:	must have the same value, or else the same value must be specified in STARTUP OPEN db_name or ALTER DATABASE db_name MOUNT

DB_NAME can specify a database identifier of up to eight characters. If specified, it must correspond to the name specified in the CREATE DATABASE statement. Although the use of DB_NAME is optional, it should generally be set before invoking CREATE DATABASE and then referenced in that statement.

If not specified, a database name must appear on either the STARTUP or the ALTER DATABASE MOUNT command line for each instance of the parallel server.

The following are valid characters in a database name:

• alphabetic characters
• numbers
• underscore (_)
• pound (#)
• dollar symbol ($)

No other characters are valid. Double quotation marks are removed before processing the database name. They cannot be used to embed other characters in the name.

Lowercase characters are not treated with special significance. They are considered the same as their uppercase counterparts.

For more information, see the Oracle8 Administrator's Guide.

DB_WRITER_PROCESSES

Parameter type:	integer
Parameter class:	static
Default value:	1
Range of values:	1-10

DB_WRITER_PROCESSES specifies the initial number of database writer processes for an instance.

If you use DBWR_IO_SLAVES, only one database writer process will be used, regardless of the setting for DB_WRITER_PROCESSES.

DBLINK_ENCRYPT_LOGIN

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

DBLINK_ENCRYPT_LOGIN specifies whether attempts to connect to other Oracle Servers through database links should use encrypted passwords. When you attempt to connect to a database using a password, Oracle encrypts the password before sending it to the database. If the DBLINK_ENCRYPT_LOGIN parameter is TRUE, and the connection fails, Oracle does not re-attempt the connection. If this parameter is FALSE, Oracle re-attempts the connections using an unencrypted version of the password.

For more information, see the Oracle8 Administrator's Guide.

DBWR_IO_SLAVES

Parameter type:	integer
Parameter class:	static
Default value:	0
Range of values:	0 to system-dependent value

DBWR_IO_SLAVES specifies the number of I/O slaves used by the DBWR process. The DBWR process and its slaves always write to disk. By default, the value is 0 and I/O slaves are not used.

Typically I/O slaves are used to "simulate" asynchronous I/O on platforms that do not support asynchronous I/O or implement it inefficiently. However, I/O slaves can be used even when asynchronous I/O is being used. In that case the I/O slaves will use asynchronous I/O.

I/O slaves are also useful in database environments with very large I/O throughput, even if asynchronous I/O is enabled.

DELAYED_LOGGING_BLOCK_CLEANOUTS

Parameter type:	boolean
Parameter class:	static
Default value:	TRUE
Range of values:	TRUE/FALSE
OK to change:	yes
Multiple instances:	need not be identical

DELAYED_LOGGING_BLOCK_CLEANOUTS turns the delayed block cleanout feature on or off. This reduces pinging in an Oracle Parallel Server. Keeping this feature set to TRUE sets a fast path, no logging block cleanout at commit time. Logging the block cleanout occurs at the time of a subsequent change to the block. This generally improves Oracle Parallel Server performance, particularly if block pings are a problem.

When Oracle commits a transaction, each block that the transaction changed is not immediately marked with the commit time. This is done later, on demand, when the block is read or updated. This is called block cleanout.

When block cleanout is performed during an update to a current block, the cleanout changes and the redo records are appended with those of the update. In previous releases, when block cleanout was needed during a read to a current block, extra cleanout redo records were generated and the block was dirtied. This has been changed.

When a transaction commits, all blocks changed by the transaction are cleaned out immediately. This cleanout performed at commit time is a "fast version" which does not generate redo log records (delayed logging) and does not reping the block. Most blocks will be cleaned out in this way, with the exception of blocks changed by long running transactions.

During queries, therefore, the data block's transaction information is normally up-to-date and the frequency of needing block cleanout is greatly reduced. Regular block cleanouts are still needed when querying a block where the transactions are still truly active, or when querying a block which was not cleaned out during commit.

Note: In long-running transactions, block cleanouts will not be performed during the transaction. If the transaction is not long running, block cleanout will be performed and the block cleanout is logged at the change of block.

During changes (INSERT, DELETE, UPDATE), the cleanout redo log records are generated and appended with the redo of the changes.

DISCRETE_TRANSACTIONS_ENABLED

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

Set DISCRETE_TRANSACTIONS_ENABLED to TRUE to implement a simpler, faster rollback mechanism that improves performance for certain kinds of transactions. There are strict limits on the kinds of transactions that can occur in discrete mode, but greater efficiency can be obtained for these transactions.

Discrete transactions are not supported for Parallel DML.

For more information about supplied packages, see the Oracle8 Tuning.

DISK_ASYNCH_IO

Parameter type:	boolean
Parameter class:	static
Default value:	TRUE
Range of values:	TRUE, FALSE

DISK_ASYNCH_IO can be used to control whether I/O to datafiles, controlfiles and logfiles are asynchronous. If a platform supports asynchronous I/O to disk, it is recommended that this parameter is left to its default. However, if the asynchronous I/O implementation is not stable, this parameter can be set to FALSE to disable asynchronous I/O. If a platform does not support asynchronous I/O to disk, this parameter has no effect.

If DISK_ASYNCH_IO is set to FALSE, then DBWR_IO_SLAVES should also be set.

DISTRIBUTED_LOCK_TIMEOUT

Parameter type:	integer
Parameter class:	static
Default value:	60 seconds
Range of values:	1 - unlimited

DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time in seconds for distributed transactions to wait for locked resources.

For more information on data concurrency, see Oracle8 Concepts and Oracle8 Distributed Database Systems.

DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME

Parameter type:	integer
Parameter class:	static
Default value:	200 seconds
Range of values:	0 - 1800 seconds

DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME specifies the length of time to hold a remote connection open after a distributed transaction fails, in hope that communication will be restored without having to reestablish the connection. Larger values minimize reconnection time, but they also consume local resources for a longer time period. Values larger than 1800 seconds can be specified. Because the reconnection and recovery background process runs every 30 minutes (1800 seconds) (whether or not a failure occurs), a value of 1800 or larger means that the connection never closes.

For more information, see the Oracle8 Administrator's Guide and Oracle8 Distributed Database Systems.

DISTRIBUTED_TRANSACTIONS

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent
Range of values:	0 - TRANSACTIONS

DISTRIBUTED_TRANSACTIONS specifies the maximum number of distributed transactions in which this database can concurrently participate. The value of this parameter cannot exceed the value of the parameter TRANSACTIONS.

If network failures are occurring at an abnormally high rate, causing many in-doubt transactions, you may want to decrease this parameter's value temporarily. This limits the number of concurrent distributed transactions, which then reduces the number of in-doubt transactions. Thus, the amount of blocked data and possible heuristic decision making (because of in-doubt transactions) is reduced.

If DISTRIBUTED_TRANSACTIONS is set to 0, no distributed transactions are allowed for the database. The recovery (RECO) process also does not start when the instance starts up.

For more information, see the Oracle8 Administrator's Guide and Oracle8 Distributed Database Systems. See also your operating system-specific Oracle documentation for the default value.

DML_LOCKS

Parameter type:	integer
Parameter class:	static
Default value:	derived (4 * TRANSACTIONS)
Range of values:	20 - unlimited, 0
Multiple instances:	must all have positive values or must all be 0

DML_LOCKS specifies the maximum number of DML locks-one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if 3 users are modifying data in one table, then 3 entries would be required. If 3 users are modifying data in 2 tables, then 6 entries would be required.

The default value assumes an average of 4 tables referenced per transaction. For some systems, this value may not be enough.

If the value is set to 0, enqueues are disabled and performance is slightly increased. However, you cannot use DROP TABLE, CREATE INDEX, or explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE. If the value is set to 0 on one instance, it must be set to 0 on all instances of an Oracle Parallel Server.

For more information on data concurrency, see Oracle8 Parallel Server Concepts and Administration, Oracle8 Concepts, and Oracle8 Distributed Database Systems.

PDML Restrictions

DML_LOCKS has the following PDML restrictions regarding locks acquired by a parallel UPDATE/DELETE/INSERT statement.

• The coordinator acquires one Table lock SX where there is one Partition lock X per partition.
• For parallel UPDATE/DELETE, unless the UPDATE/DELETE's WHERE clause specifies the partitions involved, the coordinator will acquire partition locks for all partitions.
• For parallel INSERT, the coordinator will acquire partition locks for all partitions.
• Each slave acquires one Table lock SX where there is one Partition lock NULL per partition, and where there is one Partition-Wait lock X per partition.
• A slave can work on one or more partitions but a partition can only be worked on by one slave. So for a table with 600 partitions, running with parallel degree 100, assuming all partitions are involved in the parallel UPDATE/DELETE statement. There are the following requirements:
  • The coordinator acquires one Table lock SX and 600 Partition locks X
  • Total slaves acquire 100 Table locks SX, 600 Partition locks NULL, and 600 Partition-Wait locks X

ENQUEUE_RESOURCES

Parameter type:	integer
Parameter class:	static
Default value:	derived
Range of values:	10 - unlimited

An enqueue is a sophisticated locking mechanism which permits several concurrent processes to share known resources to varying degrees. Any object which can be used concurrently can be protected with enqueues. For example, Oracle allows varying levels of sharing on tables: two processes can lock a table in share mode or in share update mode.

One difference between enqueues and latches is that in latches there is no ordered queue of waiting processes as there are in enqueues. Processes waiting for latches can either use timers to wake up and retry or spin (only in multiprocessors).

ENQUEUE_RESOURCES sets the number of resources that can be concurrently locked by the lock manager. The default value of ENQUEUE_RESOURCES is derived from the SESSIONS parameter and should be adequate, as long as DML_LOCKS + 20 is less than ENQUEUE_RESOURCES. For three or fewer sessions, the default value is 20. For 4 to 10 sessions, the default value is ((SESSIONS - 3) * 5) + 20; and for more than 10 sessions, it is ((SESSIONS - 10) * 2) + 55.

If you explicitly set ENQUEUE_RESOURCES to a value higher than DML_LOCKS + 20, then the value you provide is used.

If there are many tables, the value may be increased. Allow one per resource (regardless of the number of sessions or cursors using that resource), not one per lock Only increase this parameter if Oracle returns an error specifying that enqueues are exhausted.

For more information on data concurrency, see Oracle8 Parallel Server Concepts and Administration, Oracle8 Concepts and Oracle8 Distributed Database Systems.

EVENT

Parameter type:	string
Parameter class:	static
Default value:	NULL

EVENT is used to debug the system. This parameter should not usually be altered except at the direction of Oracle technical support personnel.

FAST_FULL_SCAN_ENABLED

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	FALSE
Range of values:	TRUE, FALSE

FAST_FULL_SCAN_ENABLED enables fast full scans, a useful alternative to full table scans. Fast full scans require an index containing all the columns that are needed for the query. Also, at least one column of the table must be NOT NULL.

FIXED_DATE

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	NULL

FIXED_DATE lets you set a constant date that SYSDATE will always return instead of the current date. The format of the date is:

YYYY-MM-DD-HH24:MI:SS. 

It also accepts the default Oracle date format, without a time. Specify the value with double quotes (but not single quotes) or without quotes. For example,

FIXED_DATE = "30-nov-95" 

or

FIXED_DATE = 30-nov-95

This parameter is useful primarily for testing.

FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	see below
Range of values:	TRUE, FALSE
OK to change:	yes
Multiple instances:	must have identical values

FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY is a Parallel Server parameter. The value of this parameter lets the database administrator control whether Oracle freezes the entire database during instance recovery. When this parameter is set to TRUE, Oracle freezes the entire database during instance recovery. The advantage of freezing the whole database is that it stops all other disk activities except those for instance recovery. This lets instance recovery complete faster. The drawback of freezing the whole database is that the entire database becomes unavailable during instance recovery.

When this parameter is set to FALSE, Oracle does not freeze the entire database, unless Oracle is responsible for resilvering some of the mirrored data files. Resilvering means ensuring data consistency of mirrored data files after a node crash. When Oracle does not freeze the entire database, part of the unaffected database will be accessible during instance recovery.

If all online datafiles use hash locks, the default value of this parameter is FALSE. If any data files use fine-grain locks, the default is TRUE.

For more information, see Oracle8 Parallel Server Concepts and Administration.

GC_DEFER_TIME

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	10
Range of values:	any positive integer
Multiple instances:	can have different values
OK to change:	no

GC_DEFER_TIME specifies the time (in 100ths of a second) that the server waits, or defers, before responding to forced-write requests for hot blocks from other instances. Specifying the GC_DEFER_TIME parameter makes it more likely that buffers will be properly cleaned out before being written, thus making them more useful when they are read by other instances. It also improves the chance of hot blocks being used multiple times within an instance between forced writes.

The default value, 0, means that the feature is disabled: no deferring occurs.

GC_FILES_TO_LOCK

Parameter type:	string
Parameter class:	static
Default value:	NULL
Multiple instances:	must have identical values
OK to change:	yes

GC_FILES_TO_LOCKS is a Parallel Server parameter. This parameter controls the mapping of PCM locks to datafiles. The value of the parameter should be set to cover as many files as possible. Thus, to avoid performance problems, you should always change GC_FILES_TO_LOCKS when the size of datafiles change or when new datafiles are added. This requires you to shutdown and restart your parallel server.

GC_FILES_TO_LOCKS has the following syntax:

GC_FILES_TO_LOCKS = "{file_list=lock_count[!blocks][EACH]}[:]..." 

where file_list is one or more datafiles listed by their file numbers, or ranges of file numbers, with comma separators:

filenumber[-filenumber][,filenumber[-filenumber]]... 

and lock_count is the number of PCM locks assigned to file_list. If lock_count is set to 0, then fine-grain locking is used for these files.

A colon (:) separates each clause that assigns a number of PCM locks to file_list. The optional parameter blocks, specified with the "!" separator, indicates the number of contiguous blocks covered by one lock. The default is non-contiguous blocks. EACH specifies that each datafile in file_list is assigned a separate set of lock_count PCM locks. Spaces are not allowed within the quotation marks.

If the number of PCM locks allocated to a datafile is less than or equal to the number of blocks in a datafile, each of these locks will cover a number of contiguous blocks within the datafile equal to !blocks. If the number of PCM locks assigned to the datafile is larger than its number of blocks, resources will be wasted since there will be locks which are not covering any blocks.

The datafiles not specified in GC_FILES_TO_LOCKS are covered, by default, by releasable locks. Releasable locks are controlled by a different parameter, GC_RELEASABLE_LOCKS. See "GC_RELEASABLE_LOCKS" on page 1-46.

To find the correspondence between filenames and file numbers, query the data dictionary view DBA_DATA_FILES. See "DBA_DATA_FILES" on page 2-55.

GC_FILES_TO_LOCKS has no effect on an instance running in exclusive mode.

For more information on GC_FILES_TO_LOCKS, see Oracle8 Parallel Server Concepts and Administration.

GC_LATCHES

Parameter type:	integer
Parameter class:	static
Default value:	derived

GC_LATCHES specifies how many lock element latches each LCK process has. It defaults to (number-of-cpus x 2). So, for example, on a uniprocessor, each LCK process would have 2 latches. This parameter should only be set if there is lock element latch contention. Previously, the only way to avoid lock element contention was to increase the number of LCK processes. Setting GC_LATCHES is a less expensive way of avoiding this contention.

For more information, see Oracle8 Parallel Server Concepts and Administration.

GC_LCK_PROCS

Parameter type:	integer
Parameter class:	static
Default value:	1 (ignored when the database is mounted in exclusive mode)
Range of values:	1 - 10, or 0 for a single instance running in exclusive mode
OK to change:	yes (1 is usually sufficient)
Multiple instances:	must have identical values

GC_LCK_PROCS is a Parallel Server parameter. This parameter sets the number of background lock processes (LCK0 through LCK9) for an instance in a parallel server. The default of 1 is normally sufficient, but you can increase the value if the distributed lock request rate saturates the lock process. The lock process is saturated if it becomes CPU bound.

Increase the value of the PROCESSES parameter by one for each LCKn process, and increase the values of other parameters whose default values are derived from PROCESSES if you do not use their defaults.

For more information, see Oracle8 Parallel Server Concepts and Administration.

GC_RELEASABLE_LOCKS

Parameter type:	integer
Parameter class:	static
Default value:	Defaults to the number of buffers (DB_BLOCK_BUFFERS)
Range of values:	0 - DB_BLOCK_BUFFERS or higher
Multiple instances:	can have different values
OK to change:	yes

Lock elements can be fixed or non-fixed. Fixed lock elements are used by hashed PCM locks, in which the lock element name is preassigned. Non-fixed lock elements are used with fine-grain locking.

If the GC_RELEASABLE_LOCKS parameter is set, its value is used to allocate space for fine-grain locking. There is no maximum value, except as imposed by memory restrictions.

This parameter is specific to the Oracle Parallel Server in shared mode.

For more information, see Oracle8 Parallel Server Concepts and Administration.

GC_ROLLBACK_LOCKS

Parameter type:	string
Parameter class:	static
Default value:	20
Multiple instances:	must have identical values
OK to change:	yes

GC_ROLLBACK_LOCKS is a Parallel Server parameter. This parameter specifies, for each rollback segment, the number of distributed locks available for simultaneously modified rollback segment blocks. The default is adequate for most applications.

These instance locks are acquired in exclusive mode by the instance that acquires the rollback segment. They are used to force the instance to write rollback segment blocks to disk when another instance needs a read-consistent version of a block.

For more information, see Oracle8 Parallel Server Concepts and Administration.

GLOBAL_NAMES

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SESSION, ALTER SYSTEM
Default value:	FALSE
Range of values:	TRUE/FALSE

GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, then no check is performed. Oracle recommends setting this parameter to TRUE to ensure the use of consistent naming conventions for databases and links.

If you use distributed processing, set GLOBAL_NAMES to TRUE to ensure a unique identifying name for your database in a networked environment.

For more information, see the Oracle8 Administrator's Guide.

HASH_AREA_SIZE

Parameter type:	integer
Parameter class:	dynamic, scope= ALTER SESSION
Default value:	2 * SORT_AREA_SIZE
Range of values:	0 - system-dependent value

HASH_AREA_SIZE specifies the maximum amount of memory, in bytes, to be used for hash joins. If this parameter is not set, its value defaults to twice the value of the SORT_AREA_SIZE parameter.

HASH_JOIN_ENABLED

Parameter type:	boolean
Parameter class:	dynamic, scope= ALTER SESSION
Default value:	TRUE
Range of values:	TRUE, FALSE

HASH_JOIN_ENABLED specifies whether the optimizer should consider using a hash join as a join method. When set to FALSE, hash join is turned off; that is, it is not available as a join method that the optimizer can consider choosing. When set to TRUE, the optimizer will compare the cost of a hash join to other types of joins, and choose it if it gives the best cost.

HASH_MULTIBLOCK_IO_COUNT

Parameter type:	integer
Parameter class:	dynamic, scope= ALTER SESSION, ALTER SYSTEM
Default value:	1
Range of values:	operating system dependent

HASH_MULTIBLOCK_IO_COUNT specifies how many sequential blocks a hash join reads and writes in one IO. When operating in multi-threaded server mode, however, this parameter is ignored (a value of 1 is used even if you set the parameter to another value).

The maximum value for HASH_MULTIBLOCK_IO_COUNT varies by operating system. It is always less than the operating system's maximum I/O size expressed as Oracle blocks (max_IO_size/DB_BLOCK_SIZE).

This parameter strongly affects performance because it controls the number of partitions into which the input is divided. If you change the parameter value, try to make sure that the following formula remains true:

R / M <= Po2(M/C)

where:

R = size of(left input to the join)
M = HASH_AREA_SIZE * 0.9
Po2(n) = largest power of 2 that is smaller than n
C = HASH_MULTIBLOCK_IO_COUNT * DB_BLOCK_SIZE

HI_SHARED_MEMORY_ADDRESS

Parameter type:	integer
Parameter class:	static
Default value:	0

SHARED_MEMORY_ADDRESS and HI_SHARED_MEMORY_ADDRESS specify the SGA's starting address at runtime. Many platforms specify the SGA's starting address at linktime; these parameters are ignored on those platforms. Use HI_ SHARED_MEMORY_ADDRESS to specify the high order 32 bits of a 64 bit address on 64 bit platforms. If both parameters are 0 or unspecified, the SGA address defaults to a platform-specific location.

IFILE

Parameter type:	string
Parameter class:	static
Default value:	NULL
Range of values:	valid parameter filenames
Multiple instances:	can have different values

Use IFILE to embed another parameter file within the current parameter file. For example:

IFILE = COMMON.ORA

You can have up to three levels of nesting. In this example, the file COMMON.ORA could contain a second IFILE parameter for the file COMMON2.ORA, which could contain a third IFILE parameter for the file GCPARMS.ORA. You can also include multiple parameter files in one parameter file by listing IFILE several times with different values:

IFILE = DBPARMS.ORA
IFILE = GCPARMS.ORA
IFILE = LOGPARMS.ORA

INSTANCE_GROUPS

Parameter type:	string LIST
Parameter class:	static
Allowable values:	a string of group names, separated by commas.

INSTANCE_GROUPS is a Parallel Server parameter. It can be specified in parallel mode only. This parameter assigns the current instance to the specified groups. The value of INSTANCE_GROUPS must be a comma-separated list of instance groups. Instance groups are used when allocating query slaves for a parallel operation.

See also "PARALLEL_INSTANCE_GROUP" on page 1-94.

For more information, see Oracle8 Parallel Server Concepts and Administration.

INSTANCE_NUMBER

Parameter type:	integer
Parameter class:	static
Default value:	lowest available number (depends on instance start up order and on the INSTANCE_NUMBER values assigned to other instances)
Range of values:	1 - maximum number of instances specified in CREATE DATABASE statement
Multiple instances:	if specified, instances must have different values
OK to change:	yes (can be specified in both parallel and exclusive modes)

INSTANCE_NUMBER is a Parallel Server parameter. This parameter can be specified in parallel mode or exclusive mode. It specifies a unique number that maps the instance to one group of free space lists for each table created with storage option FREELIST GROUPS.

The INSTANCE option of the ALTER TABLE ALLOCATE EXTENT statement assigns an extent to a particular group of free lists. If you set INSTANCE_NUMBER to the value specified for the INSTANCE option, the instance uses that extent for inserts, and updates that expand rows.

The practical maximum value of this parameter is the maximum number of instances specified in the CREATE DATABASE statement; the absolute maximum is operating system dependent.

For more information, see Oracle8 Parallel Server Concepts and Administration.

JOB_QUEUE_INTERVAL

Parameter type:	integer
Parameter class:	static
Default value:	60 (seconds)
Range of values:	1 - 3600 (seconds)
Multiple instances:	can have different values

JOB_QUEUE_INTERVAL specifies the interval between wake-ups for the SNPn background processes of the instance.

For more information on managing table snapshots, see Oracle8 Replication.

JOB_QUEUE_PROCESSES

Parameter type:	integer
Parameter class:	dynamic
Default value:	0
Range of values:	0 - 36
Multiple instances:	can have different values

JOB_QUEUE_PROCESSES specifies the number of SNPn background processes per instance, where n is 0 to 9 followed by A to Z. If you wish to have your snapshots updated automatically, you must set this parameter to a value of one or higher. One snapshot refresh process will usually be sufficient unless you have many snapshots that refresh simultaneously.

Job Queue processes are also used to process requests created by DBMS_JOB.

For more information on managing table snapshots, see Oracle8 Replication.

LARGE_POOL_MIN_ALLOC

Parameter type:	string
Parameter class:	static
Default value:	16K
Range of values:	minimum: 16K maximum: ~64M

LARGE_POOL_MIN_ALLOC specifies the minimum allocation size from the large pool. The value of the parameter can be specified in megabytes or kilobytes.

LARGE_POOL_MIN_ALLOC can accept a numerical value or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000".

LARGE_POOL_SIZE

Parameter type:	string
Parameter class:	static
Default value:	0
Range of values:	minimum: 300K or LARGE_POOL_MIN_ALLOC, whichever is larger. maximum: at least 2GB (maximum is operating system-specific)

The parameter LARGE_POOL_SIZE lets you specify the size of the large pool allocation heap. The default size is 0, and the minimum size is 300K or LARGE_POOL_MIN_ALLOC, whichever is larger. The value of the parameter can be specified in megabytes or kilobytes. If specified, the large pool is used for session memory if running with the multithreaded server. It is also used for IO buffers during backup operations.

LARGE_POOL_SIZE can accept a numerical value or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000".

LGWR_IO_SLAVES

Parameter type:	integer
Parameter class:	static
Default value:	0
Range of values:	0 - system-dependent value

LGWR_IO_SLAVES specifies the number of I/O slaves used by the LGWR process. The LGWR process and its slaves always write to disk. By default the value is 0 and I/O slaves are not used.

Typically I/O slaves are used to "simulate" asynchronous I/O on platforms that do not support asynchronous I/O or implement it inefficiently. However, I/O slaves can be used even when asynchronous I/O is being used. In that case the I/O slaves will use asynchronous I/O.

The default value is almost always adequate.

LICENSE_MAX_SESSIONS

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	0
Range of values:	0 - number of session licenses
Multiple instances:	can have different values

LICENSE_MAX_SESSIONS specifies the maximum number of concurrent user sessions allowed simultaneously. When this limit is reached, only users with the RESTRICTED SESSION privilege can connect to the server. Users who are not able to connect receive a warning message indicating that the system has reached maximum capacity.

A zero value indicates that concurrent usage (session) licensing is not enforced. If you set this parameter to a non-zero number, you might also want to set LICENSE_SESSIONS_WARNING.

Concurrent usage licensing and user licensing should not both be enabled. Either LICENSE_MAX_SESSIONS or LICENSE_MAX_USERS should always be zero.

Multiple instances can have different values, but the total for all instances mounting a database should be less than or equal to the total number of sessions licensed for that database.

For more information, see the Oracle8 Administrator's Guide.

LICENSE_MAX_USERS

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	0
Range of values:	0 - number of user licenses
Multiple instances:	should have the same values

LICENSE_MAX_USERS specifies the maximum number of users you can create in the database. When you reach this limit, you cannot create more users. You can, however, increase the limit.

Concurrent usage (session) licensing and user licensing should not both be enabled. Either LICENSE_MAX_SESSIONS or LICENSE_MAX_USERS, or both, should be zero.

If different instances specify different values for this parameter, the value of the first instance to mount the database takes precedence.

For more information, see the Oracle8 Administrator's Guide.

LICENSE_SESSIONS_WARNING

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	0
Range of values:	0 - LICENSE_MAX_SESSIONS
Multiple instances:	can have different values

LICENSE_SESSIONS_WARNING specifies a warning limit on the number of concurrent user sessions. When this limit is reached, additional users can connect, but Oracle writes a message in the ALERT file for each new connection. Users with RESTRICTED SESSION privilege who connect after the limit is reached receive a warning message stating that the system is nearing its maximum capacity.

If this parameter is set to zero, no warning is given when approaching the concurrent usage (session) limit. If you set this parameter to a nonzero number, you should also set LICENSE_MAX_SESSIONS.

For more information, see the Oracle8 Administrator's Guide.

LM_LOCKS

Parameter type:	integer
Parameter class:	static
Default value:	12000
Range of values:	minimum: 512, maximum: limited by the shared memory available in the operating system the maximum size of contiguous shared memory segment; otherwise, it is limited only by the address space
Multiple instances:	must have the same value

LM_LOCKS is a Parallel Server parameter. This parameter specifies the number of locks which will be configured for the lock manager. The number of locks can be represented by the following equation, where R is the number of resources, N is the total number of nodes, and L is the total number of locks.

L = R + (R*(N - 1))/N

Note that lock configurations are per lock manager instance. Thus the value of LM_LOCKS must be the same for all lock manager instances. Also note that, in the worst case, up to 2 * GC_RELEASABLE_LOCKS could be required with DBA locking.

LM_PROCS

Parameter type:	integer
Parameter class:	static
Default value:	64 + the maximum number of instances supported on the port
Range of values:	minimum: 36 maximum: the result of the following equation: PROCESSES + maximum number of instances + safety factor Note: This assumes that the PROCESSES parameter has already included the Oracle background processes, including LMON and LMD0. The safety factor should be added to account for temporary overhead or unavailability of some procedure during the clean-up of dead processes.
Multiple instances:	must have the same value

LM_PROCS is a Parallel Server parameter. The value of this parameter represents the value of the PROCESSES parameter plus the maximum number of instances. Note that the processes configurations are per lock manager instance. Thus the value for LM_PROCS must be the same for all lock manager instances.

LM_RESS

Parameter type:	integer
Parameter class:	static
Default value:	6000
Range of values:	minimum: 256 maximum: limited by the shared memory available in the operating system the maximum size of contiguous shared memory segment; otherwise, it is limited only by the address space
Multiple instances:	must have the same value

LM_RESS is a Parallel Server parameter. This parameter controls the number of resources that can be locked by each lock manager instance. It is recommended that each instance be assigned the same parameter value.

The value specified for LM_RESS should be less than 2 * DML_LOCKS plus an overhead of about 20 locks. However, with DBA locking, up to 2 * GC_RELEASABLE_LOCKS resources to cover all the BL locks, at least in the worst case.

LM_RESS covers the number of lock resources allocated for DML, DDL (data dictionary locks), data dictionary and library cache locks plus the file and log management locks.

LOCAL_LISTENER

Parameter type:	string
Parameter class:	static
Default value:	"(ADDRESS_LIST = (Address = (Protocol = TCP) (Host=localhost) (Port=1521)) (Address=(Protocol = IPC) (Key= DBname)))"

The LOCAL_LISTENER parameter is optional and identifies "local" Net8 listeners. LOCAL_LISTENER specifies the network name of either a single address or an address list of Net8 listeners. These Net8 listeners need to be running on the same machine as the instance.

The instance and dispatchers register certain information with the listener. This information enables the listener to connect clients to the appropriate dispatchers and dedicated servers. In order to connect clients to dedicated servers, the listener and the instance must be running on the same machine.

When it is present, the LOCAL_LISTENER parameter overrides the obsolete MTS_LISTENER_ADDRESS and MTS_MULTIPLE_LISTENERS parameters if specified. For more information on these parameters, see "MTS_LISTENER_ADDRESS" on page 1-73 and "MTS_MULTIPLE_LISTENERS" on page 1-75.

For more information about instances, listener processes, and dispatcher processes, see the Oracle8 Administrator's Guide. See your operating system-specific Oracle documentation and Net8 documentation for a description of how to specify addresses for the protocols on your system.

LOCK_NAME_SPACE

Parameter type:	string
Parameter class:	static
Range of values:	eight characters maximum, no special characters allowed

LOCK_NAME_SPACE specifies the name space that the distributed lock manager (DLM) uses to generate lock names. This might need to be set if there is a standby or clone database with the same database name on the same cluster.

LOCK_SGA

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

LOCK_SGA is used to lock the entire SGA into physical memory. It is ignored on platforms that don't support it.

LOCK_SGA_AREAS

Parameter type:	integer
Parameter class:	static
Default value:	0

LOCK_SGA_AREAS is used to lock individual components of the SGA (as defined in V$SGA) into physical memory. For each of the 4 components in the SGA, set the corresponding bit in the parameter. For example,

LOCK_SGA_AREAS =15

locks redo buffers (8) + data buffers (4) + variable SGA (2) + fixed SGA (1)

or

LOCK_SGA_ AREAS= 5

locks data buffers (4) + fixed SGA (1)

LOG_ARCHIVE_BUFFER_SIZE

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent
Range of values:	1 - operating system-dependent (in operating system blocks)
Multiple instances:	can have different values

LOG_ARCHIVE_BUFFER_SIZE specifies the size of each archival buffer, in redo log blocks (operating system blocks). The default should be adequate for most applications. This parameter, with LOG_ARCHIVE_BUFFERS, can be used to tune archiving.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the default value.

LOG_ARCHIVE_BUFFERS

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent
Range of values:	operating system-dependent
Multiple instances:	can have different values

LOG_ARCHIVE_BUFFERS specifies the number of buffers to allocate for archiving. The default should be adequate for most applications.

This parameter, with LOG_ARCHIVE_BUFFER_SIZE, can tune archiving so that it runs as fast as necessary, but not so fast that it reduces system performance.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the default value.

LOG_ARCHIVE_DEST

Parameter type:	string
Parameter class:	static
Default value:	operating system-dependent
Range of values:	any valid path or device name, except raw partitions
Multiple instances:	can have different values

LOG_ARCHIVE_DEST is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string to specify the default location and root of the disk file or tape device when archiving redo log files. (Archiving to tape is not supported on all operating systems.) The value cannot be a raw partition.

To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the Server Manager command ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination.

For more information, see the Oracle8 Administrator's Guide. See also, "LOG_ARCHIVE_DUPLEX_DEST" on page 1-61, "LOG_ARCHIVE_MIN_SUCCEED_DEST" on page 1-62 and "V$ARCHIVE_DEST" on page 3-4

See your Oracle operating system-specific documentation for the default value and for an example of how to specify the destination path or filename using LOG_ARCHIVE_DEST.

LOG_ARCHIVE_DUPLEX_DEST

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	a NULL string
Range of values:	Either a NULL string or any valid path or device name, except raw partitions

LOG_ARCHIVE_DUPLEX_DEST is similar to the initialization parameter LOG_ARCHIVE_DEST. This parameter specifies a second archive destination: the duplex archive destination. This duplex archive destination can be either a must-succeed or a best-effort archive destination, depending on how many archive destinations must succeed.

If LOG_ARCHIVE_DUPLEX_DEST is set to be a NULL string ("") or (`'), it means there is no duplex archive destination. The default of this parameter is a NULL string.

For more information, see "LOG_ARCHIVE_DEST" on page 1-60, "LOG_ARCHIVE_MIN_SUCCEED_DEST" on page 1-62, and "V$ARCHIVE_DEST" on page 3-4.

LOG_ARCHIVE_FORMAT

Parameter type:	string
Parameter class:	static
Default value:	operating system-dependent (length for uppercase variables is also operating system-dependent)
Range of values:	any valid filename
Multiple instances:	can have different values, but identical values are recommended

LOG_ARCHIVE_FORMAT is applicable only if you are using the redo log in ARCHIVELOG mode. Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter. The following variables can be used in the format:

%s log sequence number
%t thread number

Using uppercase letters (for example, %S) for the variables causes the value to be a fixed length padded to the left with zeros.

The following is an example of specifying the archive redo log filename format:

LOG_ARCHIVE_FORMAT = "LOG%s_%t.ARC"

For more information, see Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values for LOG_ARCHIVE_FORMAT.

LOG_ARCHIVE_MIN_SUCCEED_DEST

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	1
Range of values:	1 - 2

LOG_ARCHIVE_MIN_SUCCEED_DEST specifies the minimum number of archive log destinations that must succeed. When automatic archiving is enabled, the allowable values are 1 and 2. If this parameter is 1, LOG_ARCHIVE_DEST is a must-succeed destination and LOG_ARCHIVE_DUPLEX_DEST is a best-effort destination. If this parameter is 2, both LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST are must-succeed destinations.

For more information, see "LOG_ARCHIVE_DEST" on page 1-60, "LOG_ARCHIVE_DUPLEX_DEST" on page 1-61, and "V$ARCHIVE_DEST" on page 3-4

LOG_ARCHIVE_START

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE
Multiple instances:	can have different values

LOG_ARCHIVE_START is applicable only when you use the redo log in ARCHIVELOG mode, LOG_ARCHIVE_START indicates whether archiving should be automatic or manual when the instance starts up. TRUE indicates that archiving is automatic. FALSE indicates that the database administrator will archive filled redo log files manually. (The Server Manager command ARCHIVE LOG START or STOP overrides this parameter.)

In ARCHIVELOG mode, if all online redo log files fill without being archived, an error message is issued, and instance operations are suspended until the necessary archiving is performed. This delay is more likely if you use manual archiving. You can reduce its likelihood by increasing the number of online redo log files.

To use ARCHIVELOG mode while creating a database, set this parameter to TRUE. Normally, a database is created in NOARCHIVELOG mode and then altered to ARCHIVELOG mode after creation.

For more information, see the Oracle8 Administrator's Guide.

LOG_BLOCK_CHECKSUM

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

If LOG_BLOCK_CHECKSUM is TRUE, then every log block will be given a checksum before it is written to the current log.

Warning: Setting LOG_BLOCK_CHECKSUM to TRUE can cause performance overhead. Set this parameter to TRUE only under the advice of Oracle Support personnel to diagnose data corruption problems.

LOG_BUFFER

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent
Range of values:	operating system-dependent

LOG_BUFFER specifies the amount of memory, in bytes, that is used when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.

In general, larger values for LOG_BUFFER reduce redo log file I/O, particularly if transactions are long or numerous. In a busy system, the value 65536 or higher would not be unreasonable.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.

LOG_CHECKPOINT_INTERVAL

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	operating system-dependent
Range of values:	unlimited (operating-system blocks, not database blocks)
Multiple instances:	can have different values

LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the number of redo log file blocks that are written between consecutive checkpoints.

Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. If the value exceeds the actual redo log file size, checkpoints occur only when switching logs. The checkpoint frequency is one of the factors which impacts the time required for the database to recover from an unexpected failure.

Extremely frequent checkpointing can cause excessive writes to disk, possibly impacting transaction performance. In addition, if the intervals are so close together that the interval checkpoint requests are arriving at a rate faster than the rate at which Oracle can satisfy these requests, Oracle can choose to ignore some of these requests in order to avoid excessive interval checkpointing activity.

The number of times DBWR has been notified to do a checkpoint for a given instance is shown in the cache statistic DBWR checkpoints, which is displayed in the System Statistics Monitor of the Enterprise Manager. For more information about this statistic, see "DBWR checkpoints" on page C-2

Note that specifying a value of 0 (zero) for the interval might cause interval checkpoints to be initiated very frequently since a new request will be started even if a single redo log buffer is written since the last request was initiated. Hence, setting the value to 0 is not recommended.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the default value.

LOG_CHECKPOINT_TIMEOUT

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	0 seconds
Range of values:	0 - unlimited
Multiple instances:	can have different values

LOG_CHECKPOINT_TIMEOUT specifies the maximum amount of time before another checkpoint occurs. The value is specified in seconds. The time begins at the start of the previous checkpoint, then a checkpoint occurs after the amount of time specified by this parameter.

Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended.

Note: A checkpoint scheduled to occur because of this parameter is delayed until the completion of the previous checkpoint if the previous checkpoint has not yet completed.

For more information, see the Oracle8 Administrator's Guide.

LOG_CHECKPOINTS_TO_ALERT

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

LOG_CHECKPOINTS_TO_ALERT allows you to log your checkpoints to the alert file. This parameter is useful to determine if checkpoints are occurring at the desired frequency.

For more information, see Oracle8 Concepts.

LOG_FILE_NAME_CONVERT

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	none
Range of values:	character strings

The value of LOG_FILE_NAME_CONVERT converts the filename of a new log file on the primary database to the filename of a log file on the standby database. Adding a log file to the primary database necessitates adding a corresponding file to the standby database. When the standby database is updated, this parameter is used to convert the log file name on the primary database to the log file name on the standby database. The file must exist and be writable on the standby database or the recovery process will halt with an error.

Set the value of this parameter to two strings: the first string is the pattern found in the log file names on the primary database; the second string is the pattern found in the log file names on the standby database.

LOG_FILES

Parameter type:	integer
Parameter class:	static
Default value:	255
Range of values:	2 - 255 (must be a minimum of MAXLOGFILES*MAXLOGMEMBERS)
Multiple instances:	must have the same value

LOG_FILES specifies the maximum log group number. This value specifies the maximum number of redo log files that can be opened at runtime for the database. It also gives the upper limit on the group numbers that can be specified when issuing log-related commands. Reduce the value only if you need SGA space and have fewer redo log files.

For more information, see the Oracle8 Administrator's Guide.

LOG_SIMULTANEOUS_COPIES

Parameter type:	integer
Parameter class:	static
Default value:	CPU_COUNT
Range of values:	0 - unlimited

LOG_SIMULTANEOUS_COPIES specifies the maximum number of redo buffer copy latches available to write log entries simultaneously. For good performance, you can have up to twice as many redo copy latches as CPUs. For a single-processor system, set to zero so that all log entries are copied on the redo allocation latch.

If this parameter is set to 0, redo copy latches are turned off, and the parameters LOG_ENTRY_PREBUILD_THRESHOLD and LOG_SMALL_ENTRY_MAX_SIZE are ignored.

For more information, see the Oracle8 Administrator's Guide.

LOG_SMALL_ENTRY_MAX_SIZE

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	operating system-dependent
Range of values:	operating system-dependent

LOG_SMALL_ENTRY_MAX_SIZE specifies the size in bytes of the largest copy to the log buffers that can occur under the redo allocation latch without obtaining the redo buffer copy latch. If the value for LOG_SIMULTANEOUS_COPIES is 0, this parameter is ignored (all writes are "small" and are made without the copy latch).

If the redo entry is copied on the redo allocation latch, the user process releases the latch after the copy. If the redo entry is larger than this parameter, the user process releases the latch after allocating space in the buffer and getting a redo copy latch.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.

MAX_COMMIT_PROPAGATION_DELAY

Parameter type:	integer
Parameter class:	static
Default value:	90000
Range of values:	0 - 90000
Multiple instances:	must have identical values
OK to change:	no

MAX_COMMIT_PROPAGATION_DELAY is a Parallel Server parameter. This initialization parameter should not be changed except under a limited set of circumstances specific to the Parallel Server. This parameter specifies the maximum amount of time allowed before the System Change Number (SCN) held in the SGA of an instance is refreshed by LGWR. It determines if the local SCN should be refreshed from the lock value when getting the snapshot SCN for a query. Units are in hundredths of seconds. Under very unusual circumstances involving rapid updates and queries of the same data from different instances, the SCN might not be refreshed in a timely manner. Setting the parameter to zero causes the SCN to be refreshed immediately after a commit. The default value of 90,000 hundredths of a second, or fifteen minutes, is an upper bound that allows the preferred existing high performance mechanism to remain in place.

Change this parameter only when it is absolutely necessary to see the most current version of the database when doing a query.

For more information, see Oracle8 Parallel Server Concepts and Administration.

MAX_DUMP_FILE_SIZE

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SYSTEM, ALTER SYSTEM DEFERRED, ALTER SESSION
Default value:	10000 blocks
Range of values:	0 - UNLIMITED

MAX_DUMP_FILE_SIZE specifies the maximum size of trace files to be written. Change this limit if you are concerned that trace files may take up too much space.

MAX_DUMP_FILE_SIZE can accept a numerical value or a number followed by the suffix "K", or "M", where "K" means multiply by 1000 and "M" means multiply by 1000000. A numerical value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks, whereas a number followed by a "K" or "M" suffix specifies the file size in number of bytes. MAX_DUMP_FILE_SIZE can also assume the special value string UNLIMITED. UNLIMITED means that there is no upper limit on trace file size, thus dump files can be as large as the operating system permits.

For more information, see the Oracle8 Administrator's Guide.

MAX_ENABLED_ROLES

Parameter type:	integer
Parameter class:	static
Default value:	20
Range of values:	0 - 148

MAX_ENABLED_ROLES specifies the maximum number of database roles that a user can enable, including sub-roles.

The actual number of roles a user can enable is 2 plus the value of MAX_ENABLED_ROLES, because each user has two additional roles, PUBLIC, and the user's own role. For example, if MAX_ENABLED_ROLES is set to 5, user SCOTT can have 7 roles enabled, the five enabled by MAX_ENABLED_ROLES plus PUBLIC and SCOTT.

For more information, see the Oracle8 Administrator's Guide.

MAX_ROLLBACK_SEGMENTS

Parameter type:	integer
Parameter class:	static
Default value:	30
Range of values:	2 - 65535

MAX_ROLLBACK_SEGMENTS specifies the maximum size of the rollback segment cache in the SGA. The number specified signifies the maximum number of rollback segments that can be kept online (that is, status of INUSE) simultaneously by one instance. For more information, see the Oracle8 Administrator's Guide.

MAX_TRANSACTION_BRANCHES

Parameter type:	integer
Parameter class:	static
Default value:	8
Range of values:	1 - 32

MAX_TRANSACTION_BRANCHES controls the number of branches in a distributed transaction. For example, a certain TP monitor uses one branch per server involved in a distributed transaction. Another TP monitor uses one branch per server group involved in a distributed transaction.

The previously fixed maximum number of branches limited the number of servers or server groups involved in a distributed transaction to 8 per Oracle instance. With the MAX_TRANSACTION_BRANCHES parameter, the maximum number of branches can be increased to 32, allowing for 32 servers or server groups per Oracle instance to work on one distributed transaction.

Setting MAX_TRANSACTION_BRANCHES to a lower value reduces shared pool memory usage slightly according to the following equation:

MAX_TRANSACTION_BRANCHES * DISTRIBUTED_TRANSACTIONS * 72 bytes

MTS_DISPATCHERS

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	NULL

MTS_DISPATCHERS lets the database administrator enable various attributes for each dispatcher. In Oracle 7.3, the database administrator could specify a protocol and an initial number of dispatchers. These attributes are specified in a position-dependent, comma-separated string assigned to MTS_DISPATCHERS. For example:

MTS_DISPATCHERS = "TCP, 3"

While remaining backwardly compatible with this format, the parsing software in Oracle8 supports a name-value syntax (similar to the syntax used by Net8) to enable the specification of the existing and additional attributes in a position-independent case-insensitive manner. For example:

MTS_DISPATCHERS = "(PROTOCOL=TCP)(DISPATCHERS=3)"

One and only one of the following attributes is required: ADDRESS, DESCRIPTION, or PROTOCOL.

Attribute	Description
ADDRESS (ADD or ADDR)	The network address (in Net8 syntax) of the end point which the dispatchers will listen on. (Includes the protocol.)
DESCRIPTION (DES or DESC)	The network description (in Net8 syntax) of the end point which the dispatchers will listen on. (Includes the protocol.)
PROTOCOL (PRO or PROT)	The network protocol for which the dispatchers will generate a listening end point.

The ADDRESS and DESCRIPTION attributes provides support for the specification of additional network attributes. (This enables support of multi-homed hosts.)

The attributes CONNECTIONS, DISPATCHERS, LISTENER, MULTIPLEX, POOL, SERVICE, and TICKS are optional:

Attribute	Description
CONNECTIONS (CON or CONN)	The maximum number of network connections to allow for each dispatcher. Default is set by Net8 and is platform specific.
DISPATCHERS (DIS or DISP)	The initial number of dispatchers to start. Default is 1.
LISTENER (LIS, LIST)	The network name of an address or address list of the Net8 listeners with which the dispatchers will register. The LISTENER attribute makes it easier to administer multi-homed hosts. This attribute specifies the appropriate listeners with which the dispatchers will register. The LISTENER attribute overrides the LOCAL_LISTENER parameter and the denigrated MTS_LISTENER_ADDRESS and MTS_MULTPLE_LISTENERS parameters. For more information, see "LOCAL_LISTENER" on page 1-57, "MTS_LISTENER_ADDRESS" on page 1-73, and "MTS_MULTIPLE_LISTENERS" on page 1-75.
MULTIPLEX (MUL or MULT)	Used to enable the Net8 "Network Session Multiplex" feature. If "1", "ON", "YES", "TRUE", or "BOTH" is specified, then "Network Session Multiplex" is enabled for both incoming and outgoing network connections. If "IN" is specified, then "Network Session Multiplex" is enabled for incoming network connections. If "OUT" is specified, then "Network Session Multiplexing" is enabled for outgoing network connections. If "0", "NO", "OFF", or "FALSE" is specified, then "Network Session Multiplexing" is disabled for both incoming and outgoing network connections. The default "Network Session Multiplex" is disabled on both incoming and outgoing network connections.
POOL (POO)	Used to enable the Net8 "Connection Pooling" feature. If a number is specified, then "Connection Pooling" is enabled for both incoming and outgoing network connections and the number specified is the timeout in ticks for both incoming and outgoing network connections. If "ON", "YES", "TRUE", or "BOTH" is specified, then "Connection Pooling" is enabled for both incoming and outgoing network connections and the default timeout (set by Net8) will be used for both incoming and outgoing network connections. If "IN" is specified, then "Connection Pooling" is enabled for incoming network connections and the default timeout (set by Net8) will be used for incoming network connections. If "OUT" is specified, then "Connection Pooling" is enabled for outgoing network connections and the default timeout (set by Net8) will be used for outgoing network connections. If "NO", "OFF", or "FALSE" is specified, then "Connection Pooling" is disabled for both incoming and outgoing network connections. POOL can also be assigned a name-value string such as: "(IN=10)", "(OUT=20)", or "(IN=10)(OUT=20)", in which case, if an "IN" numeric value is specified, then "Connection Pooling" is enabled for incoming connections and the number specified is the timeout in ticks for incoming network connections. If an "OUT" numeric value is specified, then "Connection Pooling" is enabled for outgoing network connections and the number specified is the timeout in ticks for outgoing network connections. If the numeric value of a specified timeout is 0, then the default value (set by Net8) will be used. The default "Connection Pooling" is disabled on both incoming and outgoing network connections.
SERVICE (SER, SERV)	The service name which the dispatchers register with the Net8 listeners. The SERVICE attribute overrides the MTS_SERVICE parameter. This attribute specifies a service name that the dispatchers will use to register. For more information, see "MTS_SERVICE" on page 1-78.
SESSIONS (SES or SESS)	The maximum number of network sessions to allow for each dispatcher. Default is set by Net8 and is platform specific.
TICKS (TIC or TICK)	The size of a network tick in seconds. See the Oracle Net8 Administrator's Guide for more details about what this means. The default is set by Net8 and is platform specific.

For more information, see the Oracle8 Administrator's Guide. See also the Oracle Net8 Administrator's Guide.

MTS_LISTENER_ADDRESS

Parameter type:	string
Parameter class:	static
Default value:	NULL

MTS_LISTENER_ADDRESS specifies the configuration of the Listener process. The Listener process requires an address to listen for connection requests for each network protocol that is used on your system. Addresses are specified as the Net8 description of the connection address.

Warning: Each address must be specified with its own parameter. (This differs from the Net8 syntax.) For example, if you use TCP/IP as well as DECNet, you would provide specifications similar to the following in your initialization file:

MTS_LISTENER_ADDRESS =    \ 
      "(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=7002))"
      MTS_LISTENER_ADDRESS =    \
      "(ADDRESS=(PROTOCOL=decnet)(NODE=name)(OBJECT=mts))"

Note: If you have multiple MTS_LISTENER_ADDRESS parameters, they must be adjacent to each other in your initialization file.

Address specifications for the Listener process are operating system-specific and network protocol-specific.

MTS_LISTENER_ADDRESS is obsolete but is supported for backward compatibility. The functionality of MTS_LISTENER_ADDRESS has been replaced with the LOCAL_LISTENER parameter and LISTENER attribute of the MTS_DISPATCHERS parameter. For more information on these parameters, see "LOCAL_LISTENER" on page 1-57 and "MTS_DISPATCHERS" on page 1-70.

For more information, see the Oracle8 Administrator's Guide. See your operating system-specific Oracle documentation and Net8 documentation for a description of how to specify addresses for the protocols on your system.

MTS_MAX_DISPATCHERS

Parameter type:	integer
Parameter class:	static
Default value:	if dispatchers are configured, then defaults to whichever is greater: 5 or the number of dispatchers configured
Range of values:	operating system-dependent

MTS_MAX_DISPATCHERS specifies the maximum number of dispatcher processes allowed to be running simultaneously.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.

MTS_MAX_SERVERS

Parameter type:	integer
Parameter class:	static
Default value:	defaults to whichever is greater: 20 or 2 times the value of MAX_SERVERS
Range of values:	operating system-dependent

MTS_MAX_SERVERS specifies the maximum number of shared server processes allowed to be running simultaneously.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.

MTS_MULTIPLE_LISTENERS

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

If MTS_MULTIPLE_LISTENERS is set to TRUE, the syntax of the MTS_LISTENER_ADDRESS parameter changes to the following:

MTS_MULTIPLE_LISTENERS = TRUE
MTS_LISTENER_ADDRESS =
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(PORT=5000)(HOST=zeus))\
(ADDRESS=(PROTOCOL=decnet)(OBJECT=outa)(NODE=zeus))

MTS_MULTIPLE _LISTENERS is obsolete but is supported for backward compatibility. The functionality of MTS_MULTIPLE _LISTENERS has been replaced by the LOCAL_LISTENER parameter and LISTENER attribute of the MTS_DISPATCHERS parameter. For more information, see "LOCAL_LISTENER" on page 1-57 and "MTS_DISPATCHERS" on page 1-70.

MTS_RATE_LOG_SIZE

Parameter type:	string
Parameter class:	static
Default value:	each name-value listed in Table 1-6 defaults to 10
Range of values:	DEFAULTS/EVENT_LOOPS/MESSAGES/SERVER_BUFFERS/CLIENT_BUFFERS/TOTAL_BUFFERS/IN_CONNECTS/OUT_CONNECTS/RECONNECTS

MTS_RATE_LOG_SIZE specifies the sample size used to calculate dispatcher rate statistics. The sample size determines how much memory will be used and the frequency with which maximum rates will be determined. The memory used by each dispatcher is about 8 bytes per statistic multiplied by the sample size specified.

Dispatcher rate statistics themselves are calculated by first logging a sample of events (the size of the sample is specified by MTS_RATE_LOG_SIZE) and the times at which they occur. The rates are then calculated based on this sample.

MTS_RATE_LOG_SIZE accepts a name-value string. Each value defaults to 10. These values are shared among all dispatchers.

The following declaration for MTS_RATE_LOG_SIZE directs each dispatcher to log this many events: 4 inbound connections, 32 buffers to go either to the client or the server, and 16 events for unspecified statistics.

MTS_RATE_LOG_SIZE="(IN_CONNECTS=4)(TOTAL_BUFFERS=32)(DEFAULTS=16)" 

Valid name values for MTS_RATE_LOG_SIZE are listed below.

Table 1-6 Name Values for the MTS_RATE_LOG_SIZE Parameter

Name	Description
DEFAULTS	Overrides 10 as the number of events to log for unspecified statistics.
EVENT_LOOPS	Specifies number of event loops to log.
MESSAGES	Specifies number of messages to log.
SERVER_BUFFERS	Specifies number of buffers going to the server to log.
CLIENT_BUFFERS	Specifies number of buffers going to the client to log.
TOTAL_BUFFERS	Specifies number of buffers going in either direction to log.
IN_CONNECTS	Specifies number of inbound connections to log.
OUT_CONNECTS	Specifies number of outbound connections to log.
RECONNECTS	Specifies number of connection pool reconnections to log.

MTS_RATE_SCALE

Parameter type:	string
Parameter class:	static
Default value:	default values for the name-value strings are listed in Table 1-7
Range of values:	DEFAULTS/EVENT_LOOPS/MESSAGES/SERVER_BUFFERS/CLIENT_BUFFERS/TOTAL_BUFFERS/IN_CONNECTS/OUT_CONNECTS/RECONNECTS

MTS_RATE_SCALE specifies the scale at which dispatcher rate statistics are reported. The values are specified in 100ths of a second. Thus,

MTS_RATE_SCALE = "(EVENT_LOOPS=6000)"

means that the event loops statistic will be reported on a once per-minute interval.

MTS_RATE_SCALE accepts a name-value string. Valid names are listed in Table 1-7.

Table 1-7 Name values for the MTS_RATE_SCALE Parameter

Name	Default	Description
DEFAULTS	none	Specifies the scale for statistics not otherwise specified
EVENT_LOOPS	6000	Specifies scale in which to report event loops.
MESSAGES	100	Specifies scale in which to report messages.
SERVER_BUFFERS	10	Specifies scale in which to report buffers going to the server.
CLIENT_BUFFERS	10	Specifies scale in which to report buffers going to the client.
TOTAL_BUFFERS	10	Specifies scale in which to report buffers going in either direction. (Default = 10)
IN_CONNECTS	6000	Specifies scale in which to report inbound connections.
OUT_CONNECTS	6000	Specifies scale in which to report outbound connections.
RECONNECTS	6000	Specifies scale in which to report connection pool reconnections.

MTS_SERVERS

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	0
Range of values:	operating system-dependent

MTS_SERVERS specifies the number of server processes that you want to create when an instance is started up.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.

MTS_SERVICE

Parameter type:	string
Parameter class:	static
Default value:	NULL

MTS_SERVICE specifies the name of the service you want to be associated with the dispatcher. Using this name in the CONNECT string allows users to connect to an instance through a dispatcher. Oracle always checks for such a service before establishing a normal database connection.

The name you specify must be unique. It should not be enclosed in quotation marks. It is a good idea for this name to be the same as the instance name. That way, if the dispatcher is unavailable for any reason, the CONNECT string will still connect the user to the database.

If not specified, MTS_SERVICE defaults to the value specified by DB_NAME. If DB_NAME also is not specified, the Oracle Server returns an error at startup indicating that the value for this parameter is missing.

For more information, see the Oracle8 Administrator's Guide. See also the Oracle Net8 Administrator's Guide.

NLS_CALENDAR

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	Gregorian
Range of values:	any valid calendar format name

NLS_CALENDAR specifies which calendar system Oracle uses.

NLS_CALENDAR can have one of the following values:

• Arabic Hijrah
• English Hijrah
• Gregorian
• Japanese Imperial
• Persian
• ROC Official (Republic of China)
• Thai Buddha

For example, if NLS_CALENDAR is set to "Japanese Imperial", the date format is "E YY-MM-DD", and the date is May 15, 1997, then the SYSDATE is displayed as follows:

SELECT SYSDATE FROM DUAL;
SYSDATE
--------
H 09-05-15 

For more information, see "NLS_CALENDAR" on page 4-19. See also the Oracle8 Administrator's Guide.

NLS_CURRENCY

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	derived
Range of values:	any valid character string, with a maximum of 10 bytes (not including null)

NLS_CURRENCY specifies the string to use as the local currency symbol for the L number format element. The default value of this parameter is determined by NLS_TERRITORY.

For more information, see "NLS_CURRENCY" on page 4-19. See also the Oracle8 Administrator's Guide.

NLS_DATE_FORMAT

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	derived
Range of values:	any valid date format mask but not exceeding a fixed length

NLS_DATE_FORMAT specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY. The value of this parameter can be any valid date format mask, and the value must be surrounded by double quotation marks. For example:

NLS_DATE_FORMAT = "MM/DD/YYYY"

For more information, see "NLS_DATE_FORMAT" on page 4-20. See also the Oracle8 Administrator's Guide.

NLS_DATE_LANGUAGE

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	value for NLS_LANGUAGE
Range of values:	any valid NLS_LANGUAGE value

NLS_DATE_LANGUAGE specifies the language to use for the spelling of day and month names and date abbreviations (AM, PM, AD, BC). The default value of this parameter is the language specified by NLS_LANGUAGE.

For more information, see "NLS_DATE_ LANGUAGE" on page 4-21. See also the Oracle8 Administrator's Guide.

NLS_ISO_CURRENCY

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	derived
Range of values:	any valid NLS_TERRITORY value

NLS_ISO_CURRENCY specifies the string to use as the international currency symbol for the C number format element. The default value of this parameter is determined by NLS_TERRITORY.

For more information, see "NLS_ISO_CURRENCY" on page 4-23. See also the Oracle8 Administrator's Guide.

NLS_LANGUAGE

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	operating system-dependent
Range of values:	any valid language name

NLS_LANGUAGE specifies the default language of the database. This language is used for messages, the day and month names, the symbols for AD, BC, AM, and PM, and the default sorting mechanism. This parameter has the format:

NLS_LANGUAGE = FRENCH

Examples of supported languages are American, French, and Japanese.

This parameter determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT. For a complete list of languages, see "Supported Languages" on page 4-39.

For more information, see "NLS_LANGUAGE" on page 4-15. See also the Oracle8 Administrator's Guide, your country release notes, and operating system-specific Oracle documentation.

NLS_NUMERIC_CHARACTERS

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	derived

NLS_NUMERIC_CHARACTERS specifies the characters to use as the group separator and decimal and overrides those defined implicitly by NLS_TERRITORY. The group separator is the character that separates integer groups (that is, the thousands, millions, billions, and so on). The decimal separates the integer portion of a number from the decimal portion.

Any character can be the decimal or group separator. The two characters specified must be single-byte, and both characters must be different from each other each other. The characters cannot be any numeric character or any of the following characters: plus (+), hyphen (-), less than sign (<), greater than sign (>).

The characters are specified in the following format:

NLS_NUMERIC_CHARACTERS = "<decimal_character><group_separator>"

For example, if you wish to specify a comma as the decimal character and a space as the group separator, you would set this parameter as follows:

NLS_NUMERIC_CHARACTERS = ", "

The default value of this parameter is determined by NLS_TERRITORY.

For more information, see "NLS_NUMERIC_CHARACTERS" on page 4-23. See also the Oracle8 Administrator's Guide.

NLS_SORT

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	derived
Range of values:	BINARY or valid linguistic definition name

NLS_SORT specifies the collating sequence for ORDER BY queries. If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).

If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.

Note: Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort into the execution plan.

You must use the NLS_SORT operator with comparison operations if you want the linguistic sort behavior.

The default value of this parameter depends on the value of the NLS_LANGUAGE parameter.

For more information on this parameter, see "NLS_SORT" on page 4-24 and the Oracle8 Administrator's Guide. For a list of supported linguistic definitions and extended definitions, see "Linguistic Definitions" on page 4-52. See also your operating system-specific Oracle documentation for the sorting rules used by the linguistic sorting mechanisms.

NLS_TERRITORY

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	operating system-dependent
Range of values:	any valid territory name

NLS_TERRITORY specifies the name of the territory whose conventions are to be followed for day and week numbering. Also specifies the default date format, the default decimal character and group separator, and the default ISO and local currency symbols. Supported territories include America, France, Japan, and so on. For a complete list of territories, see "Supported Territories" on page 4-41.

This parameter determines the default values for the following parameters: NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, and NLS_NUMERIC_CHARACTERS.

For more information, see "NLS_TERRITORY" on page 4-16. See also the Oracle8 Administrator's Guide. See your operating system-specific Oracle documentation for the territory-dependent default values for these parameters.

OBJECT_CACHE_MAX_SIZE_PERCENT

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SESSION, ALTER SYSTEM DEFERRED
Default value:	10%
Range of values:	0% to operating system-dependent maximum

OBJECT_CACHE_MAX_SIZE_PERCENT specifies the percentage of the optimal cache size that the session object cache can grow past the optimal size; the maximum size is equal to the optimal size plus the product of this percentage and the optimal size. When the cache size exceeds this maximum size, the system will attempt to shrink the cache to the optimal size.

OBJECT_CACHE_OPTIMAL_SIZE

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SESSION, ALTER SYSTEM DEFERRED
Default value:	100 Kbytes
Range of values:	10 Kbytes to operating system-dependent maximum

OBJECT_CACHE_OPTIMAL_SIZE specifies the size to which the session object cache is reduced when the size of the cache exceeds the maximum size.

OGMS_HOME

Parameter type:	string
Parameter class:	static
Default value:	platform-dependent
Range of values:	valid local pathname or directory

OGMS_HOME specifies the pathname for a directory where Oracle background processes can retrieve the GMS key file. this file contains the listening port ID of the local running gms process as well as its process ID. The parameter only needs to be set when GMS is started up with a different gms home directory than the default one.

OPEN_CURSORS

Parameter type:	integer
Parameter class:	static
Default value:	50
Range of values:	1 - operating system limit

OPEN_CURSORS specifies the maximum number of open cursors (context areas) a session can have at once. This constrains a session from opening an excessive number of cursors. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead by setting this value too high.

It is important to have the value of OPEN_CURSORS set high enough to prevent your application from running out of open cursors. The number will vary from one application to another.

This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the range of values.

OPEN_LINKS

Parameter type:	integer
Parameter class:	static
Default value:	4
Range of values:	0 - 255

OPEN_LINKS specifies the maximum number of concurrent open connections to remote databases in one session. The value should equal or exceed the number of databases referred to in a single SQL statement that references multiple databases so that all the databases can be open to execute the statement. Value should be increased if many different databases are accessed over time. Thus, if queries alternately access databases A, B, and C and OPEN_LINKS is set to 2, time would be spent waiting while one connection was broken and another made.

This parameter refers only to connections used for distributed transactions. Direct connections to a remote database specified as an application connects are not counted. For information on migratable open connections for XA transactions, see "OPEN_LINKS_PER_INSTANCE" on page 1-86.

If OPEN_LINKS is set to 0, then no distributed transactions are allowed.

For more information, see the Oracle8 Administrator's Guide and Oracle8 Distributed Database Systems.

OPEN_LINKS_PER_INSTANCE

Parameter type:	integer
Parameter class:	static
Default value:	4
Range of values:	0 - UB4MAXVAL
Multiple instances	can be specified for multiple instances. The value need not be same for all instances.
OK to change:	yes, provided a shutdown and restart is performed

OPEN_LINKS_PER_INSTANCE specifies the maximum number of migratable open connections. XA transactions use migratable open connections so that the connections are cached after a transaction is committed. Another transaction can use the connection provided the user that created the connection is the same as the user that owns the transaction.

OPEN_LINKS_PER_INSTANCE is different from the OPEN_LINKS parameter in that OPEN_LINKS indicates the number of connections from a session. The OPEN_LINKS parameter is not applicable to XA applications. For more information, see "OPEN_LINKS" on page 1-85.

OPS_ADMIN_GROUP

Parameter type:	string LIST
Parameter class:	dynamic, scope = ALTER SYSTEM, ALTER SESSION
Default value:	all active instances
Allowable values:	a string representing a group name.

OPS_ADMIN_GROUP is a Parallel Server parameter. OPS_ADMIN_GROUP allows instances to be partitioned in a parallel server environment for monitoring or administration purposes. The database must be mounted in parallel server mode (that is, PARALLEL_SERVER=TRUE).

The value of OPS_ADMIN_GROUP determines which instances return information in a GV$ fixed-view query. For example, assume instances {1,3,4} are active at the time of a GV$ query and the instance group group1 contains the instances 1 and 4. If OPS_ADMIN_GROUP = group1, then a query over GV$viewname retrieves information only from instances 1 and 4.

If none of the instances specified for OPS_ADMIN_GROUP are active and a GV$ view is queried, then an error is returned.

If the database is mounted in non-parallel sever mode (that is, PARALLEL_SERVER=FALSE) then the OPS_ADMIN_GROUP parameter has no effect. Every query involving GV$ views will be run on the local instance.

OPTIMIZER_FEATURES_ENABLE

Parameter type:	string
Parameter class:	static
Default value:	8.0.0
Range of values:	8.0.0; 8.0.3; 8.0.4

OPTIMIZER_FEATURES_ENABLE allows you to change the init.ora parameters which control the optimizer's behavior. The parameters affected are PUSH_JOIN_PREDICATE, FAST_FULL_SCAN_ENABLED, COMPLEX_VIEW_MERGING, and B_TREE_BITMAP_PLANS. The values 8.0.0 and 8.0.3 set those parameters to FALSE; 8.0.4 sets them to TRUE. However, regardless of the setting, you can change each parameter individually.

OPTIMIZER_MODE

Parameter type:	integer
Parameter class:	dynamic, scope=ALTER SESSION
Default value:	CHOOSE
Range of values:	RULE/CHOOSE/FIRST_ROWS/ALL_ROWS

OPTIMIZER_MODE specifies the behavior of the optimizer. When set to RULE, this parameter causes rule-based optimization to be used unless hints are specified in the query. When set to CHOOSE, the optimizer uses the cost-based approach for a SQL statement if there are statistics in the dictionary for at least one table accessed in the statement. (Otherwise, the rule-based approach is used.)

You can set the goal for cost-based optimization by setting this parameter to FIRST_ROWS or ALL_ROWS. FIRST_ROWS causes the optimizer to choose execution plans that minimize response time. ALL_ROWS causes the optimizer to choose execution plans that minimize total execution time.

For more information about tuning SQL statements, see Oracle8 Tuning. For more information about the optimizer, see Oracle8 Concepts and Oracle8 Tuning.

OPTIMIZER_PERCENT_PARALLEL

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	0
Range of values:	0 - 100

OPTIMIZER_PERCENT_PARALLEL specifies the amount of parallelism that the optimizer uses in its cost functions. The default of 0 means that the optimizer chooses the best serial plan. A value of 100 means that the optimizer uses each object's degree of parallelism in computing the cost of a full table scan operation. Low values favor indexes, and high values favor table scans.

Cost-based optimization will always be used for any query that references an object with a nonzero degree of parallelism. For such queries a RULE hint or optimizer mode or goal will be ignored. Use of a FIRST_ROWS hint or optimizer mode will override a nonzero setting of OPTIMIZER_PERCENT_PARALLEL.

OPTIMIZER_SEARCH_LIMIT

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	5

OPTIMIZER_SEARCH_LIMIT specifies the search limit for the optimizer.

ORACLE_TRACE_COLLECTION_NAME

Parameter type:	string
Parameter class:	static
Default value:	NULL
Range of values:	valid collection name up to 16 characters long

ORACLE_TRACE_COLLECTION_NAME specifies the Oracle Trace collection name. This parameter is also used in the output file names (collection definition file .CDF and data file .DAT).

ORACLE_TRACE_COLLECTION_PATH

Parameter type:	string
Parameter class:	static
Default value:	operating system-specific
Range of values:	full directory pathname

ORACLE_TRACE_COLLECTION_PATH specifies the directory pathname where Oracle Trace collection definition and data files are located. If you accept the default, the complete file specification is generally (may be different for non-UNIX systems) $ORACLE_HOME/rdbms/log/collection name.cdf and collection name.dat.

ORACLE_TRACE_COLLECTION_SIZE

Parameter type:	integer
Parameter class:	static
Default value:	5242880
Range of values:	0 - 4294967295

ORACLE_TRACE_COLLECTION_SIZE specifies the maximum size, in bytes, of the Oracle Trace collection file. Once the collection file reaches this maximum, the collection is disabled.

ORACLE_TRACE_ENABLE

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE, FALSE

In order to enable Oracle Trace collections for the server, ORACLE_TRACE_ENABLE should be set and left at TRUE. When set to TRUE, this does not start an Oracle Trace collection, it allows Oracle Trace to be used for that server. When set to TRUE, Oracle Trace can then be started by using the Oracle Trace Manager application (supplied with the Oracle Enterprise Manager Performance Pack), or including a name in the ORACLE_TRACE_COLLECTION_NAME parameter (default = null).

ORACLE_TRACE_FACILITY_NAME

Parameter type:	string
Parameter class:	static
Default value:	operating system-specific
Range of values:	valid product definition filename up to 16 characters long

ORACLE_TRACE_FACILITY_NAME specifies the Oracle Trace product definition file (.FDF file). The file must be located in the directory pointed to by the ORACLE_TRACE_FACILITY_PATH parameter.

The product definition file contains definition information for all the events and data items that can be collected for a product that uses the Oracle Trace data collection API. For example, the Oracle Server has multiple event sets and, therefore, multiple product definition files. Oracle recommends that you use the DEFAULT event set for Server collections, oracled.fdf. See the Oracle Trace documentation for more information on the Server event sets.

ORACLE_TRACE_FACILITY_PATH

Parameter type:	string
Parameter class:	static
Default value:	operating system-specific
Range of values:	full directory pathname

ORACLE_TRACE_FACILITY_PATH specifies the directory pathname where Oracle TRACE facility definition files are located.

OS_AUTHENT_PREFIX

Parameter type:	string
Parameter class:	static
Default value:	operating system-specific (typically "OPS$")

OS_AUTHENT_PREFIX authenticates users attempting to connect to the server with the users' operating system account name and password. The value of this parameter is concatenated to the beginning of every user's operating system account. The prefixed username is compared with the Oracle usernames in the database when a connection request is attempted. The default value of this parameter is OPS$ for backward compatibility with previous versions. However, you might prefer to set the prefix value to "" (a null string), thereby eliminating the addition of any prefix to operating system account names.

Note: The text of the OS_AUTHENT_PREFIX parameter is case sensitive with some operating systems.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation.

OS_ROLES

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

If OS_ROLES is set to TRUE, the database allows the operating system to identify each username's roles. When a user attempts to create a session, the username's security domain is initialized using the roles identified by the operating system. A user can subsequently enable as many roles identified by the operating system as specified by the parameter MAX_ENABLED_ROLES.

If OS_ROLES is set to TRUE, the operating system completely manages the role grants for all database usernames. Any revokes of roles granted by the operating system are ignored, and any previously granted roles are ignored.

The default value, FALSE, causes roles to be identified and managed by the database.

For more information, see the Oracle8 Administrator's Guide.

PARALLEL_ADAPTIVE_MULTI_USER

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	FALSE
Range of values:	TRUE, FALSE

PARALLEL_ADAPTIVE_MULTI_USER, when set to TRUE, enables an adaptive algorithm designed to improve performance in multi-user environments that use Parallel Query(PQ). It does this by automatically reducing the requested degree of parallelism based on the current number of active PQ users on the system. The effective degree of parallelism will be based on the degree of parallelism from the table or hint divided by the total number of PQ users. The algorithm assumes that the degree of parallelism provided has been tuned for optimal performance in a single user environment.

This parameter will work best when used in single node Symmetric Multi-Processors(SMPs). However, it can be set to TRUE when using OPS if all of the following conditions are true:

• All Parallel Query users connect to the same node
• Instance Groups are not configured
• Each node has more than one CPU

On a system running OPS, if all of the above conditions are met, and the parameter is set to TRUE, if the system is not a Shared Nothing MPP, the algorithm will attempt to reduce the instances first, then the degree. If it is a Shared Nothing MPP, it will only reduce the degree. If any of the above conditions is not met, and the parameter is set to TRUE, the algorithm may reduce parallelism excessively, causing the system to become unnecessarily idle.

For more information, see Oracle8 Tuning.

PARALLEL_BROADCAST_ENABLED

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE-FALSE

PARALLEL_BROADCAST_ENABLED allows you to improve performance in certain cases involving hash and merge joins. When set to TRUE, if you are joining a very large join result set with a very small result set (size being measured in bytes, rather than number of rows), the optimizer has the option of broadcasting the row sources of the small result set, such that a single table queue will send all of the small set's rows to each of the parallel servers which are processing the rows of the larger set. The result is enhanced performance.

For more information, see Oracle8 Parallel Server Concepts and Administration.

PARALLEL_DEFAULT_MAX_INSTANCES

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent
Range of values:	0 - number of instances
Multiple instances:	should have the same value

PARALLEL_DEFAULT_MAX_INSTANCES specifies the default number of instances to split a table across for parallel query processing. The value of this parameter is used if the INSTANCES DEFAULT is specified in the PARALLEL clause of a table's definition. This parameter might be desupported in future releases.

For more information, see Oracle8 Parallel Server Concepts and Administration.

PARALLEL_EXECUTION_MESSAGE_SIZE

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent
Range of values:	larger than 2148 bytes
Multiple instances:	must have the same value

PARALLEL_EXECUTION_MESSAGE_SIZE specifies the size of messages for parallel execution (Parallel Query, PDML, Parallel Recovery, replication). The default value should be adequate for most applications. Typical values are 2148 or 4096 bytes. Larger values would require a larger shared pool.

PARALLEL_INSTANCE_GROUP

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION, ALTER SYSTEM
Default value:	group consisting of all instances currently active
Range of values:	a string representing a group name

PARALLEL_INSTANCE_GROUP is a Parallel Server parameter and can be used in parallel mode only. This parameter identifies the parallel instance group to be used for spawning parallel query slaves. Parallel operations will spawn parallel query slaves only on instances that specify a matching group in their INSTANCE_GROUPS parameter.

If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a group that does not exist, then the operation runs serially. No parallelism is used.

For more information see Oracle8 Parallel Server Concepts and Administration.

PARALLEL_MAX_SERVERS

Parameter type:	integer
Parameter class:	static
Default value:	operating system-specific
Range of values:	0 - 256
Multiple instances:	each instance must have either a value of zero or the same value as the other instances

PARALLEL_MAX_SERVERS specifies the maximum number of parallel query servers or parallel recovery processes for an instance. Oracle will increase the number of query servers as demand requires from the number created at instance startup up to this value. The same value should be used for all instances in a parallel server environment.

Proper setting of the PARALLEL_MAX_SERVERS parameter ensures that the number of query servers in use will not cause a memory resource shortage during periods of peak database use.

If PARALLEL_MAX_SERVERS is set too low, some queries may not have a query server available to them during query processing.

Setting PARALLEL_MAX_SERVERS too high leads to memory resource shortages during peak periods, which can degrade performance. For each instance to which you do not want to apply the parallel query option, set this initialization parameter to zero.

If you have reached the limit of PARALLEL_MAX_SERVERS on an instance and you attempt to query a GV$ view, one additional parallel server process will be spawned for this purpose. This extra process will serve any subsequent GV$ queries until expiration of the PARALLEL_SERVER_IDLE_TIME, at which point the process will terminate. The extra process is not available for any parallel operation other than GV$ queries.

Note that if PARALLEL_MAX_SERVERS is set to zero for an instance, then no additional parallel server process will be allocated to accommodate a GV$ query.

For more information, see Oracle8 Parallel Server Concepts and Administration.

PARALLEL_MIN_MESSAGE_POOL

Parameter type:	integer
Parameter class:	static
Default value:	cpus*parallel_max_servers*1.5*(OS message buffer size) or cpus*5*1.5*(OS message size)
Range of values:	0 -(SHARED_POOLSIZE*.90)

PARALLEL_MIN_MESSAGE_POOL specifies the minimum permanent amount of memory which will be allocated from the SHARED POOL (see SHARED_POOL_SIZE), to be used for messages in parallel execution.

This memory is allocated at startup time if PARALLEL_MIN_SERVERS is set to a non-zero value, or when the server is first allocated. Setting this parameter is most effective when PARALLEL_MIN_SERVERS is set to a non-zero value, because the memory will be allocated in a contiguous section.

This parameter should only be set if the default formula is known to be significantly inaccurate. setting this parameter too high will lead to a shortage of memory for the shared pool; setting it too low will lead to costlier memory allocation when doing parallel execution. This parameter cannot be set to a number higher than 90% of the shared pool.

PARALLEL_MIN_PERCENT

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	0
Range of values:	0 - 100
OK to change:	yes
Multiple instances:	can have different values; application dependent

PARALLEL_MIN_PERCENT specifies the minimum percent of threads required for parallel query. Setting this parameter ensures that a parallel query will not be executed sequentially if adequate resources are not available. The default value of 0 means that this parameter is not used.

If too few query slaves are available, an error message is displayed and the query is not executed. Consider the following settings:

PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10

In a system with 20 instances up and running, the system would have a maximum of 200 query slaves available. If 190 slaves are already in use and a new user wants to run a query with 40 slaves (for example, degree 2 instances 20), an error message would be returned because 20 instances (that is, 50% of 40) are not available.

PARALLEL_MIN_SERVERS

Parameter type:	integer
Parameter class:	static
Default value:	0
Range of values:	0 - PARALLEL_MAX_SERVERS
Multiple instances:	can have different values

PARALLEL_MIN_SERVERS specifies the minimum number of query server processes for an instance. This is also the number of query server processes Oracle creates when the instance is started.

For more information, see Oracle8 Parallel Server Concepts and Administration.

PARALLEL_SERVER

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE
Multiple instances:	must have the same value

Set PARALLEL_SERVER to TRUE to enable the Parallel Server option.

For more information, see Oracle8 Parallel Server Concepts and Administration.

PARALLEL_SERVER_IDLE_TIME

Parameter type:	integer
Parameter class:	static
Default value:	operating system-specific
Range of values:	0 to the OS-dependent maximum
Multiple instances:	can have different values

PARALLEL_SERVER_IDLE_TIME specifies the amount of idle time after which Oracle terminates a process for parallel operations (parallel query, parallel DML, or parallel DDL). This value is expressed in minutes. The parameter must be set to 1 or greater for the query processes to terminate. 0 means the processes are never terminated.

PARALLEL_TRANSACTION_RESOURCE_TIMEOUT

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	300
Range of values:	0 to the OS-dependent maximum
Multiple instances:	can have different values; however, it is recommended that the same value is used across all instances

PARALLEL_TRANSACTION_RESOURCE_TIMEOUT is a Parallel Server parameter. This parameter specifies the maximum amount of time which can pass before a session, executing a parallel operation (either parallel DDL or parallel DML), times out while waiting for a resource held by another session in an incompatible lock mode. Such timeouts are an indication of potential deadlock involving the parallel transaction and other transactions currently running in the parallel server system.

The value of the parameter is specified in seconds. The time begins when the session starts to wait for a busy resource. Setting the parameter to 0 sets the maximum timeout to an effectively infinite value. Note that this is only the maximum timeout allowed for parallel transactions. For certain resources, a lower timeout value is used by Oracle.

PARTITION_VIEW_ENABLED

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	FALSE
Range of values:	TRUE/FALSE
OK to change:	yes

If PARTITION_VIEW_ENABLED is set to TRUE, the optimizer prunes (or skips) unnecessary table accesses in a partition view. This parameter also changes the way the cost-based optimizer computes statistics on a partition view from statistics on underlying tables.

PLSQL_V2_COMPATIBILITY

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SESSION, ALTER SYSTEM
Default value:	FALSE
Range of values:	TRUE/FALSE

The PLSQL_V2_COMPATIBILITY initialization parameter is optional and sets the compatibility level for PL/SQL. The default value is FALSE: PL/SQL V3 behavior is enforced and V2 behavior is not allowed.

If PLSQL_V2_COMPATIBILITY=TRUE,then the following PL/SQL V2 behaviors are accepted when you are running PL/SQL V3:

• PL/SQL will allow elements of an index table passed in as an IN parameter to be modified or deleted. For example,

  function foo (x IN table_t) is
  begin 
  x.delete(2);
  end;   
  
  

  In contrast, PL/SQL V3 correctly enforces the read-only semantics of IN parameters and does not let index table methods modify index tables passed in as IN parameters.

• The PL/SQL compiler will allow OUT parameters to be used in expression contexts in some cases (for example, in dot-qualified names on the right-hand side of assignment statements). This behavior is restricted to a few cases - fields of OUT parameters that are record, and OUT parameters referenced in the FROM list of a SELECT statement.

  In contrast, PL/SQL V3 does not permit OUT parameters to be used in expression contexts.

• PL/SQL will allow OUT parameters in the FROM clause of a SELECT list, where their value is read.
• PL/SQL will not return an error on the illegal syntax

  return expression   
  
  

  which should be

  return type  
   
  

  In contrast, PL/SQL V3 returns an error.

• PL/SQL will allow the passing of an IN argument into another procedure as an OUT. (This is restricted to fields of IN parameters that are records.)

  In contrast, PL/SQL V3 does not allow the passing of an IN argument into another procedure as an OUT.

• PL/SQL will allow a type to be referenced earlier than its definition in the source.

  In contrast, PL/SQL V3 requires a type definition to precede its use.

PRE_PAGE_SGA

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	FALSE/TRUE
OK to change:	no

If PRE_PAGE_SGA is set to TRUE, this parameter touches all the SGA pages, causing them to be brought into memory. As a result, it increases instance start up time and user login time, but it can reduce the number of page faults that occur shortly thereafter. The reduction in page faults allows the instance to reach its maximum performance capability quickly rather than through an incremental build up. It is most useful on systems that have sufficient memory to hold all the SGA pages without degrading performance in other areas.

PROCESSES

Parameter type:	integer
Parameter class:	static
Default value:	30
Range of values:	6 to operating system-dependent
Multiple instances:	can have different values

For a multiple-process operation, PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to an Oracle Server. This value should allow for all background processes such as LCK processes, Job Queue processes, and Parallel Query processes.

The default values of SESSIONS is derived from PROCESSES. If you alter the value of PROCESSES, you may want to adjust the values of this derived parameters.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the range of values.

PUSH_JOIN_PREDICATE

Parameter type:	boolean
Parameter class:	dynamic, ALTER SESSION
Default value:	FALSE
Range of values:	TRUE, FALSE

PUSH_JOIN_PREDICATE is a cost-based way of improving performance in certain queries by pushing individual join predicates into a view which is the right side of an outer join. This enables within the view which otherwise cannot be merged a more efficient access path and join methods. Examples of this are hash joins transforming into nested loop joins or full table scans becoming index scans.

READ_ONLY_OPEN_DELAYED

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE-FALSE

READ_ONLY_OPEN_DELAYED, when set to TRUE, causes datafiles in read-only tablespaces to be accessed for the first time only when an attempt is made to read data stored within them. When set to FALSE, datafiles are accessed at database open time.

This parameter is used to speed certain operations, primarily the 'open database' operation, for very large databases, when substantial portions of the database are stored in read-only tablespaces. It should be considered for such databases, especially if portions of the read-only data are stored on slow-access devices or hierarchical storage.

Use of this parameter has the following side-effects:

• A missing or bad read-only file will not be detected at open time. It will only be discovered when there is an attempt to access it.
• ALTER DATABASE CHECK DATAFILES will not check read-only files.
• ALTER TABLESPACE <name> ONLINE and ALTER DATABASE DATAFILE <name> ONLINE will not check read-only files. They will be checked only upon the first access.
• V$RECOVER_FILE, V$BACKUP, and V$DATAFILE_HEADER will not access read-only files; read-only files will be indicated in the results list with the error "DELAYED OPEN", with zeroes for the values of other columns.
• V$DATAFILE will not access read-only files; read-only files will have a size of '0' listed.
• V$RECOVER_LOG will not access read-only files; logs they may need for recovery will not be added to the list.
• ALTER DATABASE NOARCHIVELOG will not access read-only files; it will proceed even if there is a read-only file that needs recovery.

Note that RECOVER DATABASE and ALTER DATABASE OPEN RESETLOGS will continue to access all read-only datafiles regardless of the parameter value. If you want to avoid accessing read-only files for these operations, those files should be taken offline.

Also note that if a backup controlfile is used, the read-only status of some files may be inaccurate. This may cause some of these operations to return unexpected results. Care should be taken in this situation.

RECOVERY_PARALLELISM

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent
Range of values:	operating system-dependent, but cannot exceed PARALLEL_MAX_SERVERS

RECOVERY_PARALLELISM specifies the number of processes to participate in instance or media recovery. A value of zero or one indicates that recovery is to be performed serially by one process.

For more information, see Oracle8 Parallel Server Concepts and Administration.

REDUCE_ALARM

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

REDUCE_ALARM is an initialization parameter.

REMOTE_DEPENDENCIES_MODE

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SESSION, ALTER SYSTEM
Default value:	TIMESTAMP
Range of values:	TIMESTAMP/SIGNATURE

REMOTE_DEPENDENCIES_MODE is used with PL/SQL stored procedures. It specifies how dependencies upon remote stored procedures are to be handled by the database.

If this parameter is set to TIMESTAMP, which is the default setting, the client running the procedure compares the timestamp recorded on the server side procedure with the current timestamp of the local procedure and executes the procedure only if the timestamps match.

If the parameter is set to SIGNATURE, the procedure is allowed to execute as long as the signatures are considered safe. This allows client PL/SQL applications to be run without recompilation.

REMOTE_LOGIN_PASSWORDFILE

Parameter type:	string
Parameter class:	static
Default value:	NONE
Range of values:	NONE/SHARED/EXCLUSIVE
Multiple instances:	should have the same value

REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file and how many databases can use the password file. Setting the parameter to NONE signifies that Oracle should ignore any password file (and therefore privileged users must be authenticated by the operating system). Setting the parameter to EXCLUSIVE signifies that the password file can be used by only one database and the password file can contain names other than SYS and INTERNAL. Setting the parameter to SHARED allows more than one database to use a password file. However, the only users recognized by the password file are SYS and INTERNAL.

For more information about secure connections for privileged users, see the Oracle8 Administrator's Guide.

REMOTE_OS_AUTHENT

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

Setting REMOTE_OS_AUTHENT to TRUE allows authentication of remote clients with the value of OS_AUTHENT_PREFIX.

For more information, see the Oracle8 Administrator's Guide.

REMOTE_OS_ROLES

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

Setting REMOTE_OS_ROLES to TRUE allows operating system roles for remote clients. The default value, FALSE, causes roles to be identified and managed by the database for remote clients.

For more information, see the Oracle8 Administrator's Guide.

REPLICATION_DEPENDENCY_TRACKING

Parameter type:	boolean
Parameter class:	static
Default value:	TRUE
Range of values:	TRUE/FALSE

Setting REPLICATION_DEPENDENCY_TRACKING to TRUE turns on dependency tracking for read/write operations to the database. Dependency tracking is essential for the Replication Server to propagate changes in parallel. This is the default value. FALSE allows read/write operations to the database to run faster, but does not produce dependency information for the Replication Server to perform parallel propagations. Users should not specify this value unless they are sure that their application will perform absolutely no read/write operations to replicated tables.

RESOURCE_LIMIT

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	FALSE
Range of values:	TRUE/FALSE

The value of RESOURCE_LIMIT changes the enforcement status of resource limits set in database profiles. A value of FALSE disables the enforcement of resource limits. A value of TRUE enables the enforcement of resource limits.

For more information, see the Oracle8 Administrator's Guide.

ROLLBACK_SEGMENTS

Parameter type:	string
Parameter class:	static
Default value:	NULL (the instance uses public rollback segments by default if you do not specify this parameter
Range of values:	any rollback segment names listed in DBA_ROLLBACK_SEGS except SYSTEM
Multiple instances:	must have different values (different instances cannot specify the same rollback segment)
OK to change:	yes

ROLLBACK_SEGMENTS specifies one or more rollback segments to allocate by name to this instance. If ROLLBACK_SEGMENTS is set, an instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance (calculated from the ratio TRANSACTIONS /TRANSACTIONS_PER_ROLLBACK_SEGMENT).

Note: Never name the SYSTEM rollback segment as a value for the ROLLBACK_SEGMENTS parameter.

This parameter has the following syntax:

ROLLBACK_SEGMENTS = (rbseg_name [, rbseg_name] ... )

Although this parameter usually specifies private rollback segments, it can also specify public rollback segments if they are not already in use.

Different instances in an Oracle Parallel Server cannot name the same rollback segment for any of the ROLLBACK_SEGMENTS. Query the data dictionary view DBA_ROLLBACK_SEGS to find the name, segment ID number, and status of each rollback segment in the database.

For more information, see the Oracle8 Administrator's Guide.

ROW_CACHE_CURSORS

Parameter type:	integer
Parameter class:	static
Default value:	10
Range of values:	10 - 3300

ROW_CACHE_CURSORS specifies the maximum number of cached recursive cursors used by the dictionary cache manager for selecting rows from the data dictionary. The default value is sufficient for most systems.

For more information about memory structure and processes, see Oracle8 Concepts.

ROW_LOCKING

Parameter type:	string
Parameter class:	static
Default value:	ALWAYS
Range of values:	ALWAYS/DEFAULT/INTENT
Multiple instances:	must have the same value

ROW_LOCKING specifies whether row locks are acquired when a table is updated or on update. The default of ALWAYS means that only row locks are acquired when a table is updated. DEFAULT is the same as ALWAYS. INTENT means that only row locks are used on a SELECT FOR UPDATE, but at update time table locks are acquired.

For information about tuning SQL statements, see Oracle8 Tuning.

SEQUENCE_CACHE_ENTRIES

Parameter type:	integer
Parameter class:	static
Default value:	10
Range of values:	10 - 32000
Multiple instances:	can have different values

SEQUENCE_CACHE_ENTRIES is obsolete, and is allowed for compatibility.

SEQUENCE_CACHE_ENTRIES specifies the number of sequences that can be cached in the SGA for immediate access. This cache is managed on a least recently used (LRU) basis, so if a request is made for a sequence that is not in the cache and there are no free entries, the oldest one on the LRU list is deleted and replaced with the newly requested one. Highest concurrency is achieved when this value is set to the highest possible number of sequences that will be used on an instance at one time.

Each entry requires approximately 110 bytes in the SGA for an Oracle Parallel Server. Sequences created with the NOCACHE option do not reside in this cache. They must be written through to the data dictionary on every use.

For more information about managing schema objects, see Oracle8 Administrator's Guide and Oracle8 Application Developer's Guide.

SEQUENCE_CACHE_HASH_BUCKETS

Parameter type:	integer
Parameter class:	static
Default value:	7
Multiple instances:	can have different values

SEQUENCE_CACHE_HASH_BUCKETS is obsolete, and is allowed for compatibility.

SEQUENCE_CACHE_HASH_BUCKET specifies the number of sequences that can be cached in the SGA for immediate access. This cache is managed on a least recently used (LRU) basis, so if a request is made for a sequence that is not in the cache and there are no free entries, the oldest one on the LRU list is deleted and replaced with the newly requested one. Highest concurrency is achieved when this value is set to the highest possible number of sequences that will be used on an instance at one time.

Each entry requires approximately 110 bytes in the SGA for an Oracle Parallel Server. Sequences created with the NOCACHE option do not reside in this cache. They must be written through to the data dictionary on every use.

For more information about managing schema objects, see Oracle8 Administrator's Guide and Oracle8 Application Developer's Guide.

SERIAL _REUSE

Parameter type:	string LIST
Parameter class:	static
Default Value:	NULL
Range of values:	DISABLE/SELECT/DML/PLSQL/ALL/NULL

This parameter indicates which types of SQL cursors should make use of the serial-reusable memory feature. This feature moves well-structured private cursor memory into the SGA (shared pool) so that it can be reused by sessions executing the same cursor. If CURSOR_SPACE_FOR_TIME is TRUE, then the value of SERIAL_REUSE is ignored and treated as if it were set to DISABLE. The default NULL value is equivalent to setting the value to DISABLE. Values include:

Table 1-8 Values for the SERIAL_REUSE Initialization Parameter

Value	Description
DISABLE	disables the option for all SQL statement types. This value overrides any other values included in the list.
SELECT	enables the option for SELECT statements
DML	enables the option for DML statements
PLSQL	currently has no effect (although PLSQL packages do support the serial-reuse memory option using PLSQL Pragmas).
ALL	enables the option for both DML and SELECT statements. Equivalent to setting SELECT, DML, and PLSQL

SESSION_CACHED_CURSORS

Parameter type:	integer
Parameter class:	static
Default:	0
Range of values:	0 to operating system dependent
Multiple instances:	can have different values:

SESSION_CACHED_CURSORS lets you specify the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and need not reopen the cursor. The value of this parameter is the maximum number of session cursors to keep in the session cursor cache.

SESSION_MAX_OPEN_FILES

Parameter type:	integer
Parameter class:	static
Default value:	10
Range of values:	1 - the least of (50, MAX_OPEN_FILES defined at the OS level)

SESSION_MAX_OPEN_FILES specifies the maximum number of BFILEs that can be opened in any given session. Once this number is reached, subsequent attempts to open more files in the session using DBMS_LOB.FILEOPEN() or OCILobFileOpen() will fail. This parameter is also dependent on the equivalent parameter defined for the underlying operating system.

SESSIONS

Parameter type:	integer
Parameter class:	static
Default value:	derived (1.1 * PROCESSES + 5)

SESSIONS specifies the total number of user and system sessions. The default number is greater than PROCESSES to allow for recursive sessions.

The default values of ENQUEUE_RESOURCES and TRANSACTIONS are derived from SESSIONS. If you alter the value of SESSIONS, you might want to adjust the values of ENQUEUE_RESOURCES and TRANSACTIONS.

With the multi-threaded server, you should adjust the value of SESSIONS to approximately 1.1 * (total number of connections).

For more information on memory structures and processes, see Oracle8 Concepts.

SHADOW_CORE_DUMP

Parameter type:	string
Parameter class:	static
Default value:	FULL
Range of values:	FULL/PARTIAL

The value of SHADOW_CORE_DUMP determines whether the SGA will be included in core dumps. By default (FULL), the SGA is included in the core dump. If SHADOW_CORE_DUMP=PARTIAL, the SGA is not dumped.

SHARED_MEMORY_ADDRESS

Parameter type:	integer
Parameter class:	static
Default value:	0

SHARED_MEMORY_ADDRESS and HI_SHARED_MEMORY_ADDRESS specify the SGA's starting address at runtime. Many platforms specify the SGA's starting address at linktime; these parameters are ignored on those platforms. Use HI_ SHARED_MEMORY_ADDRESS to specify the high order 32 bits of a 64 bit address on 64 bit platforms. If both parameters are 0 or unspecified, the SGA address defaults to a platform-specific location.

SHARED_POOL_RESERVED_MIN_ALLOC

Parameter type:	string
Parameter class:	static
Default value:	5000
Range of values:	5000 - SHARED_POOL_RESERVED_SIZE (in bytes)

The value of SHARED_POOL_RESERVED_MIN_ALLOC controls allocation of reserved memory. Memory allocations larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists.

The default value is adequate for most systems. If you increase the value, then the Oracle Server will allow fewer allocations from the reserved list and will request more memory from the shared pool list.

SHARED_POOL_RESERVED_MIN_ALLOC can accept a numerical value or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000".

For more information on this parameter see Oracle8 Tuning.

SHARED_POOL_RESERVED_SIZE

Parameter type:	string
Parameter class:	static
Default value:	5% of the value of SHARED_POOL_SIZE
Range of values:	from SHARED_POOL_RESERVED_MIN_ALLOC to one half of SHARED_POOL_SIZE (in bytes)

SHARED_POOL_RESERVED_SIZE specifies the shared pool space which is reserved for large contiguous requests for shared pool memory. This parameter, along with the SHARED_POOL_RESERVED_MIN_ALLOC parameter, can be used to avoid performance degradation in the shared pool from situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request.

The shared pool contains the library cache of shared SQL requests, the dictionary cache, stored procedures, and other cache structures that are specific to a particular instance configuration. For example, in an MTS configuration, the session and private SQL area for each client process is included in the shared pool. When the instance is configured for parallel query, the shared pool includes the parallel query message buffers.

Proper sizing of the shared pool can reduce resource consumption in at least three ways:

• Parse time is avoided if the SQL statement is already in the shared pool. This saves CPU resources.
• Application memory overhead is reduced, since all applications use the same pool of shared SQL statements and dictionary resources.
• I/O resources are saved, since dictionary elements which are in the shared pool do not require disk access.

Default value for SHARED_POOL_RESERVED_SIZE is 5% of the SHARED_POOL_SIZE. This means that, by default, the reserved list will always be configured.

If SHARED_POOL_RESERVED_SIZE > 1/2 SHARED_POOL_SIZE, Oracle signals an error.

Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. The amount of operating system memory, however, may constrain the size of the shared pool. In general, you should set shared_pool_reserved_size to 10% of shared_pool_size. For most systems, this value will be sufficient if you have already tuned the shared pool.

SHARED_POOL_RESERVED_SIZE can accept a numerical value or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000".

For more information on this parameter see Oracle8 Tuning.

SHARED_POOL_SIZE

Parameter type:	string
Parameter class:	static
Default value:	3,500,000 bytes
Range of values:	300 Kbytes - operating system-dependent

SHARED_POOL_SIZE specifies the size of the shared pool in bytes. The shared pool contains shared cursors and stored procedures. Larger values improve performance in multi-user systems. Smaller values use less memory.

SHARED_POOL_SIZE can accept a numerical value or a number followed by the suffix "K" or "M" where "K" means "multiply by 1000" and "M" means "multiply by 1000000".

For more information, see the Oracle8 Administrator's Guide.

SORT_AREA_RETAINED_SIZE

Parameter type:	integer
Parameter class:	dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED
Default value:	the value of SORT_AREA_SIZE
Range of values:	from the value equivalent of one database block to the value of SORT_AREA_SIZE

SORT_AREA_RETAINED_SIZE specifies the maximum amount, in bytes, of User Global Area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer which is used to maintain a portion of the sort in memory. This memory is released back to the UGA, not to the operating system, after the last row is fetched from the sort space.

If a sort requires more memory, a temporary segment is allocated and the sort becomes an external (disk) sort. The maximum amount of memory to use for the sort is then specified by SORT_AREA_SIZE instead of by this parameter.

Larger values permit more sorts to be performed in memory. However, multiple sort spaces of this size may be allocated. Usually, only one or two sorts occur at one time, even for complex queries. In some cases, though, additional concurrent sorts are required. Each sort occurs in its own memory area, as specified by SORT_AREA_RETAINED_SIZE. If the value is set too high, it will be converted to a usable value.

For more information, see Oracle8 Concepts.

SORT_AREA_SIZE

Parameter type:	integer
Parameter class:	dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED
Default value:	operating system-dependent
Range of values:	0 - system-dependent value

SORT_AREA_SIZE specifies the maximum amount, in bytes, of Program Global Area (PGA) memory to use for a sort. If MTS is enabled, the sort area is allocated from the SGA. After the sort is complete and all that remains to do is to fetch the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is fetched, all memory is freed. The memory is released back to the PGA, not to the operating system.

Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never exist; there is only one memory area of SORT_AREA_SIZE for each user process at any time.

If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk hold the intermediate sort runs.

The default is usually adequate for most OLTP operations. You might want to adjust this parameter for decision support systems, batch jobs, or large CREATE INDEX operations.

For more information, see Oracle8 Concepts. See also your operating system-specific Oracle documentation for the default value on your system.

SORT_DIRECT_WRITES

Parameter type:	string
Parameter class:	dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED
Default value:	AUTO
Range of values:	AUTO/TRUE/FALSE

SORT_DIRECT_WRITES can improve sort performance if memory and temporary space are abundant on your system. This parameter controls whether sort data will bypass the buffer cache to write intermediate sort results to disk. When set to the default of AUTO, and the value of the sort area size is greater than ten times the block size, memory is allocated from the sort area to do this. When SORT_DIRECT_WRITES is TRUE, additional buffers are allocated from memory during each sort.

Additional temporary segment space can be required when SORT_DIRECT_WRITES is enabled. The sort allocation mechanism allocates temporary space using fixed-size chunks which are based on the SORT_WRITE_BUFFER_SIZE parameter. Since the values for this parameter are typically an order of magnitude larger than the DB_BLOCK_SIZE chunks used when SORT_DIRECT_WRITES is disabled, unused temporary space in the final sort segment increases the overall space requirements.

When SORT_DIRECT_WRITES is set to FALSE, the sorts that write to disk write through the buffer cache.

For more information, see Oracle8 Tuning.

SORT_READ_FAC

Parameter type:	integer
Parameter class:	dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED
Default value:	operating system-dependent

SORT_READ_FAC is a unitless ratio that describes the amount of time to read a single database block divided by the block transfer rate. The value is operating system-specific. You can set the value for your specific disk subsystem using the following equation:

See your operating system-specific Oracle documentation for the default value.

SORT_SPACEMAP_SIZE

Parameter type:	integer
Parameter class:	static
Default value:	operating system-dependent

SORT_SPACEMAP_SIZE specifies the size in bytes of the sort space map. Only if you have very large indexes should you adjust this parameter. A sort automatically increases its space map if necessary, but it does not necessarily do so when it will make best use of disk storage. The sort makes optimal use of disk storage if SORT_SPACEMAP_SIZE is set to

[(total_sort_bytes) / (sort_area_size)] + 64

where total_sort_bytes is

(number_of_records) * [sum_of_average_column_sizes + (2 * number_of_col)]

Here, columns include the SELECT list for the ORDER BY, the SELECT list for the GROUP BY, and the key list for CREATE INDEX. It also includes 10 bytes for ROWID for CREATE INDEX and GROUP BY or ORDER BY columns not mentioned in the SELECT list for these cases.

For more information on memory structures and processes, see Oracle8 Concepts. See also your operating system-specific Oracle documentation for the default value.

SORT_WRITE_BUFFER_SIZE

Parameter type:	integer
Parameter class:	dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED
Default value:	32768
Range of values:	32Kb, 64Kb

SORT_WRITE_BUFFER_SIZE sets the size of the sort IO buffer when the SORT_DIRECT_WRITES parameter is set to TRUE. SORT_WRITE_BUFFER_SIZE is recommended for use with symmetric replication.

SORT_WRITE_BUFFERS

Parameter type:	integer
Parameter class:	dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED
Default value:	1
Range of values:	2-8

SORT_WRITE_BUFFERS specifies the number of sort buffers when the SORT_DIRECT_WRITES parameter is set to TRUE. SORT_WRITE_BUFFERS is recommended for use with symmetric replication.

SPIN_COUNT

Parameter type:	integer
Parameter class:	dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED
Default value:	1
Range of values:	1-1,000,000

In multi-processor environments, you can improve performance by tuning the SPIN_COUNT initialization parameter.

A process continues to request a latch until it obtains one. If the number of requests reaches SPIN_COUNT, the process fails to acquire the latch, sleeps, then tries to acquire the latch again. Because a latch is a low-level lock, a process does not hold it long. It is less expensive to use CPU time by spinning a process than it is to make a process sleep.

You can check the contention level of the latch by monitoring the miss rate and sleep rate from the UTLBSTAT and UTLESTAT scripts. Try reducing the sleep rate by tuning the spin count. If the contention level is high, increase the spin count to allow processes to spin more before acquiring latches. However, since increasing the spin count increases CPU usage, system throughput may decline at some point.

The default value is adequate for almost all systems.

See your operating system-specific documentation for more information.

SQL_TRACE

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	FALSE
Range of values:	TRUE/FALSE

The value of SQL_TRACE disables or enables the SQL trace facility. Setting this parameter to TRUE provides information on tuning that you can use to improve performance. Because the SQL trace facility causes system overhead, you should run the database with the value TRUE only for the purpose of collecting statistics. The value can also be changed using the DBMS_SYSTEM package.

For more information about performance diagnostic tools, see Oracle8 Tuning. See also Oracle8 SQL Reference.

SQL92_SECURITY

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

SQL92_SECURITY specifies whether table-level SELECT privileges are required to execute an update or delete that references table column values.

STAR_TRANSFORMATION_ENABLED

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SESSION
Default value:	FALSE
Range of values:	TRUE/FALSE

The value of STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries. If set to TRUE, the optimizer will consider performing a cost-based query transformation on the star query. If set to FALSE, the transformation will not be applied.

For more information, see Oracle8 Concepts.

TAPE_ASYNCH_IO

Parameter type:	boolean
Parameter class:	static
Default value:	TRUE
Range of values:	TRUE, FALSE

TAPE_ASYNCH_IO can be used to control whether I/O to sequential devices (for example, BACKUP/RESTORE of Oracle data TO/FROM tape) is asynchronous. If a platform supports asynchronous I/O to sequential devices, it is recommended that this parameter is left to its default. However, if the asynchronous I/O implementation is not stable, TAPE_ASYNCH_IO can be used to disable its use. If a platform does not support asynchronous I/O to sequential devices, this parameter has no effect.

TEXT_ENABLE

Parameter type:	boolean
Parameter class:	dynamic
Default value:	FALSE
Range of values:	TRUE/FALSE

TEXT_ENABLE enables transparent use of the CONTAINS function through the Oracle ConText Option or the Oracle ConText Cartridge.

TEMPORARY_TABLE_LOCKS

Parameter type:	integer
Parameter class:	static
Default value:	derived (SESSIONS)
Range of values:	0 - operating system-dependent

TEMPORARY_TABLE_LOCKS specifies the number of temporary tables that can be created in the temporary segment space. A temporary table lock is needed any time a sort occurs that is too large too hold in memory, either as the result of a select on a large table with ORDER BY or as a result of sorting a large index. Installations with many users of applications that simultaneously perform several ordered queries on large tables might need to increase this number. Most installations should do well with the default.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the range of values.

THREAD

Parameter type:	integer
Parameter class:	static
Default value:	0
Range of values:	0 - maximum number of enabled threads
Multiple instances:	if specified, must have different values

THREAD is applicable only to instances that intend to run in parallel (shared) mode.

THREAD specifies the number of the redo thread that is to be used by the instance. Any available redo thread number can be used, but an instance cannot use the same thread number as another instance. Also, an instance cannot start when its redo thread is disabled. A value of zero causes an available, enabled public thread to be chosen. An instance cannot mount a database if the thread is used by another instance or if the thread is disabled.

Redo threads are specified with the THREAD option of the ALTER DATABASE ADD LOGFILE command. Redo threads are enabled with the ALTER DATABASE ENABLE [PUBLIC] THREAD command. The PUBLIC keyword signifies that the redo thread may be used by any instance. This is useful when running systems that have faster access to disks from certain nodes.

Thread 1 is the default thread in exclusive mode. An instance running in exclusive mode can specify THREAD to use the redo log files in a thread other than thread 1.

For more information, see Oracle8 Parallel Server Concepts and Administration and Oracle8 SQL Reference.

TIMED_OS_STATISTICS

Parameter type:	integer
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	0 (not to refresh OS statistics)
Range of values:	time in seconds

TIMED_OS_STATISTICS can be used by the system administrator to gather operating system statistics when a request is made from the client to the server or a request completes. For dedicated servers, OS statistics are gathered at the time of user logon and thereafter when calls are popped, provided the specified time limit has expired. The statistics are also gathered at the user logoff time.

For Mult-threaded Servers, if TIMED_OS_STATISTICS has a nonzero value, then OS statistics are gathered when calls are pushed or popped.

Gathering OS statistics is expensive and should be done on an as-needed basis. further, as this is a dynamic parameter, this should be set to zero immediately after the need for gathering OS statistics has been satisfied.

TIMED_OS_STATISTICS can be assigned one of the following strings:

OFF	do not gather operating system statistics while calls are pushed or popped or when the user logs off. This is the default value.
CALL	gather statistics at every push or pop call. Because this option implies significant overhead, it must be used with caution.
LOGOFF	gather statistics when the user logs off from an Oracle session.

TIMED_STATISTICS

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SYSTEM, ALTER SESSION
Default value:	FALSE
Range of values:	TRUE/FALSE

If TIMED_STATISTICS is FALSE, the statistics related to time are always zero and the server can avoid the overhead of requesting the time from the operating system. To turn on statistics, set the value to TRUE. Normally, TIMED_STATISTICS should be FALSE. On some systems with very fast timer access, timing might be enabled even when the parameter is set to FALSE. On these systems, setting the parameter to TRUE might produce more accurate statistics for long-running operations.

For more information about performance diagnostic tools, see Oracle8 Tuning.

TRANSACTION_AUDITING

Parameter type:	boolean
Parameter class:	dynamic, scope = ALTER SYSTEM DEFERRED
Default value:	TRUE
Range of values:	TRUE/FALSE

If TRANSACTION_AUDITING is TRUE, the transaction layer generates a special redo record which contains session and user information. This information includes the user logon name, user name, the session ID, some operating system information, and client information. On each successive commit, the transaction layer generates a record that contains only the session ID (which links back to the first record since it also contains the session ID). These records might be useful if using a redo log analysis tool.

If TRANSACTION_AUDITING is FALSE, no redo record is generated.

TRANSACTIONS

Parameter type:	integer
Parameter class:	static
Default value:	derived (1.1 * SESSIONS)
Multiple instances:	can have different values

TRANSACTIONS specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.

For more information about memory structures and processes, see Oracle8 Concepts and the Oracle8 Administrator's Guide.

TRANSACTIONS_PER_ROLLBACK_SEGMENT

Parameter type:	integer
Parameter class:	static
Default value:	21
Range of values:	1 - operating system-dependent
Multiple instances:	can have different values

TRANSACTIONS_PER_ROLLBACK_SEGMENT specifies the number of concurrent transactions allowed per rollback segment. The minimum number of rollback segments acquired at startup is TRANSACTIONS divided by the value for this parameter. For example, if TRANSACTIONS is 101 and this parameter is 10, then the minimum number of rollback segments acquired would be the ratio 101/10, rounded up to 11.

More rollback segments can be acquired if they are named in the parameter ROLLBACK_SEGMENTS.

For more information, see the Oracle8 Administrator's Guide. See also your operating system-specific Oracle documentation for the range of values.

USE_INDIRECT_DATA_BUFFERS

Parameter type:	boolean
Parameter class:	static
Default value:	FALSE
Range of values:	TRUE/FALSE

USE_INDIRECT_DATA_BUFFERS controls the use of the extended buffer cache mechanism for 32 bit platforms that can support more than 4GB of physical memory. It is ignored on other platforms.

USE_ISM

Parameter type:	boolean
Parameter class:	static
Default value:	TRUE
Range of values:	TRUE/FALSE

USE_ISM controls the use of shared page tables. On platforms that support it, setting USE_ISM to TRUE will cause different processes to use the same set of page tables to reference the SGA. This may result in a performance improvement, because context switches should become less expensive. However, individual processes can no longer write-protect individual regions of the SGA, so this should not be used for debugging. Platforms that don't support this parameter will ignore it.

USER_DUMP_DEST

Parameter type:	string
Parameter class:	dynamic, scope = ALTER SYSTEM
Default value:	operating system-dependent
Range of values:	valid local pathname, directory, or disk

USER_DUMP_DEST specifies the pathname for a directory where the server will write debugging trace files on behalf of a user process.

For example, this directory might be set to C:\ORACLE\UTRC on MS-DOS; to /oracle/utrc on UNIX; or to DISK$UR3:[ORACLE.UTRC] on VMS.

For more information about performance diagnostic tools, see Oracle8 Tuning. See also your operating system-specific Oracle documentation for the range of values.

UTL_FILE_DIR

Parameter type:	string
Parameter class:	static
Default value:	none
Range of values:	any valid directory path

UTL_FILE_DIR allows database administrators to specify directories that are permitted for PL/SQL file I/O. Each directory must be specified with a separate UTL_FILE_DIR parameter in the INIT.ORA file.

Note that all users can read or write all files specified in the UTL_FILE_DIR parameter(s). This means that all PL/SQL users must be trusted with the information in the directories specified by the UTL_FILE_DIR parameters.

---