Oracle8i Reference
Release 8.1.5

A67790-01

Library

Product

Contents

Index

Prev Next

1
Initialization Parameters

This chapter contains detailed descriptions of the database initialization parameters, and includes the following topics:

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 read by the client-side tool which is being used to start the server (e.g., SQL*Plus).

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 after completely shutting down the instance and then restarting it.

Database administrators can use initialization parameters to do the following:

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 the Trusted Oracle documentation 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

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

Suggestion: Listing parameters in alphabetical order in the parameter file is a good idea. 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", 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

DB_BLOCK_CHECKING  

DB_FILE_MULTIBLOCK_READ_COUNT  

GLOBAL_NAMES  

HASH_AREA_SIZE  

HASH_JOIN_ENABLED  

HASH_MULTIBLOCK_IO_COUNT  

LOG_ARCHIVE_DEST_n  

LOG_ARCHIVE_DEST_STATE_n  

LOG_ARCHIVE_MIN_SUCCEED_DEST  

MAX_DUMP_FILE_SIZE  

NLS_CALENDAR  

NLS_COMP  

NLS_CURRENCY  

NLS_DATE_FORMAT  

NLS_DATE_LANGUAGE  

NLS_DUAL_CURRENCY  

NLS_ISO_CURRENCY  

NLS_LANGUAGE  

NLS_NUMERIC_CHARACTERS  

NLS_SORT  

NLS_TERRITORY  

OBJECT_CACHE_MAX_SIZE_PERCENT  

OBJECT_CACHE_OPTIMAL_SIZE  

OPS_ADMIN_GROUP  

OPTIMIZER_INDEX_CACHING  

OPTIMIZER_INDEX_COST_ADJ  

OPTIMIZER_MAX_PERMUTATIONS  

OPTIMIZER_MODE  

OPTIMIZER_PERCENT_PARALLEL  

OPTIMIZER_SEARCH_LIMIT  

PARALLEL_BROADCAST_ENABLED  

PARALLEL_INSTANCE_GROUP  

PARALLEL_MIN_PERCENT  

PARTITION_VIEW_ENABLED  

PLSQL_V2_COMPATIBILITY  

QUERY_REWRITE_ENABLED  

QUERY_REWRITE_INTEGRITY  

REMOTE_DEPENDENCIES_MODE  

SESSION_CACHED_CURSORS  

SORT_AREA_RETAINED_SIZE  

SORT_AREA_SIZE  

SORT_MULTIBLOCK_READ_COUNT  

STAR_TRANSFORMATION_ENABLED  

TIMED_STATISTICS  

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_CHECKSUM  

DB_BLOCK_MAX_DIRTY_TARGET  

DB_FILE_MULTIBLOCK_READ_COUNT  

FAST_START_IO_TARGET  

FAST_START_PARALLEL_ROLLBACK  

FIXED_DATE  

FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY  

GC_DEFER_TIME  

GLOBAL_NAMES  

HASH_MULTIBLOCK_IO_COUNT  

HS_AUTOREGISTER  

JOB_QUEUE_PROCESSES  

LICENSE_MAX_SESSIONS  

LICENSE_MAX_USERS  

LICENSE_SESSIONS_WARNING  

LOG_ARCHIVE_DEST  

LOG_ARCHIVE_DEST_n  

LOG_ARCHIVE_DEST_STATE_n  

LOG_ARCHIVE_DUPLEX_DEST  

LOG_ARCHIVE_MAX_PROCESSES  

LOG_ARCHIVE_MIN_SUCCEED_DEST  

LOG_CHECKPOINT_INTERVAL  

LOG_CHECKPOINT_TIMEOUT  

MAX_DUMP_FILE_SIZE  

MTS_DISPATCHERS  

MTS_SERVERS  

PARALLEL_ADAPTIVE_MULTI_USER  

PARALLEL_INSTANCE_GROUP  

PARALLEL_THREADS_PER_CPU  

PLSQL_V2_COMPATIBILITY  

QUERY_REWRITE_ENABLED  

QUERY_REWRITE_INTEGRITY  

REMOTE_DEPENDENCIES_MODE  

RESOURCE_LIMIT  

RESOURCE_MANAGER_PLAN  

STANDBY_ARCHIVE_DEST  

TIMED_OS_STATISTICS  

TIMED_STATISTICS  

USER_DUMP_DEST  

 

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

DB_BLOCK_CHECKING  

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_MULTIBLOCK_READ_COUNT  

TRANSACTION_AUDITING  

 

Displaying Current Parameter Values

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

SHOW PARAMETERS

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

Enter the following text string to see a display for all parameters having BLOCK in their name.:

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:

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

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 will override the calculated value.

Global Cache Parameters with the Prefix GC

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

Additional Information: For more information about the Parallel Server, see Oracle8i 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 Oracle8i 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:

When Parameters Are Set Incorrectly

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

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 Oracle8i. 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 Oracle8i Administrator's Guide.

ALLOW_PARTIAL_SN_RESULTS

Obsoleted in 8.1.3.

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  

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, it uses the hash semijoin algorithm.

AQ_TM_PROCESSES

Parameter type:  

integer  

Parameter class:  

dynamic, 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 Oracle8i Application Developer's Guide - Fundamentals .

ARCH_IO_SLAVES

Obsoleted in 8.1.3.

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 Oracle8i Administrator's Guide.

B_TREE_BITMAP_PLANS

Obsoleted in 8.1.3.

BACKGROUND_CORE_DUMP

Parameter type:  

string  

Parameter class:  

static  

Default value:  

PARTIAL  

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:  

dynamic, scope = ALTER SYSTEM  

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, DBWn, 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 Oracle8i Administrator's Guide. See your operating system-specific Oracle documentation for the default value.

BACKUP_DISK_IO_SLAVES

Obsoleted in 8.1.3.

BACKUP_TAPE_IO_SLAVES

Parameter type:  

boolean  

Parameter class:  

dynamic, scope = ALTER SYSTEM DEFERRED  

Default value:  

TRUE  

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.

In order to perform duplexed backups, this parameter needs to be enabled, otherwise an error will be signalled. Recovery Manager will configure as many slaves as needed for the number of backup copies requested whent his parameter is enabled.

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 Oracle8i 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 Oracle8i Tuning.

CACHE_SIZE_THRESHOLD

Obsoleted in 8.1.3.

CCF_IO_SIZE

Obsoleted in 8.0.4. Use DB_FILE_DIRECT_IO_COUNT instead.

CLEANUP_ROLLBACK_ENTRIES

Obsoleted in 8.1.3.

CLOSE_CACHED_OPEN_CURSORS

Obsoleted in 8.1.3.

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 Oracle8i Concepts and Oracle8i 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.1.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.

The value of COMPATIBLE must be set to 8.1 or higher in order to use stored columns of UROWID type. ROWID pseudo-columns for index-organized tables will work irrespective of the parameter setting.

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

COMPATIBLE_NO_RECOVERY

Obsoleted in 8.1.3.

COMPLEX_VIEW_MERGING

Obsoleted in 8.1.3.

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 Oracle8i Administrator's Guide.

CORE_DUMP_DEST

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SYSTEM  

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 1.

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 Oracle8i 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 Oracle8i Concepts.

DB_BLOCK_BUFFERS

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

32768 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.

Note: The checkpoint process is enabled by default if the system configuration is such that moving checkpoint processing from the logwriter to a separate process is deemed to improve performance. Currently, the criteria are DB_FILES >= 50 or
DB_BLOCK_BUFFERS >= 10000.

For more information, see Oracle8i Concepts. See also your operating system-specific Oracle documentation.

DB_BLOCK_CHECKPOINT_BATCH

Obsoleted in 8.1.3.

DB_BLOCK_CHECKING

Parameter type:  

boolean  

Parameter class:  

dynamic, scope = ALTER SESSION, ALTER SYSTEM DEFERRED  

Default value:  

FALSE  

Range of values:  

TRUE / FALSE  

DB_BLOCK_CHECKING is used to control whether block checking is done fro transaction managed blocks. As early detection of corruptions is ueful, and has minimal, if any, performance impact, Oracle recommends that the default setting be used. The FALSE setting is provided for compatibility with ealier releases where block checking is disabled as a default. As the parameter is dynamic, it provides more flexibility than events 10210 and 10211, which it will ultimately replace. Note that the setting of DB_BLOCK_CHECKING overrides any setting of events 10210 and 10211.

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, DBWn 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.

If DB_BLOCK_CHECKSUM is set to TRUE, every log block will also be given a checksum before it is written to the current log.

Warning: Setting DB_BLOCK_CHECKSUM to TRUE can cause performance overhead.

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

DB_BLOCK_LRU_EXTENDED_STATISTICS

Obsoleted in 8.1.3.

DB_BLOCK_LRU_LATCHES

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

CPU_COUNT/2  

Range of values:  

1 - twice 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

Obsoleted in 8.1.3.

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:  

1000 to all buffers in the cache, setting to 0 disables limiting dirty buffers  

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) in the buffer cache. It indirectly specifies a rough limit on the number of blocks that must be read during crash and instance recovery.

Note that the FAST_START_IO_TARGET parameter, (available only with the Oracle Enterprise Edition), can be used to enforce a a more accurate bound on the number of IOs during recovery.)

DBWn will write out buffers to attempt to limit the number of dirty buffers in the cache below the specified value.

Setting this value to 0 disables the mechanism that limits the number of dirty buffers in the cache. All other writing 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 Oracle8i 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 all databases in a domain is 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".

If you omit the domains from the name of a database link, Oracle expands the name by qualifying the database with the domain of your local database as it currently exists in the data dictionary, and then stores the link name in the data dictionary. See also the view GLOBAL_NAME.

The following characters are valid in a database domain name:

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

DB_FILE_DIRECT_IO_COUNT

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SYSTEM DEFERRED  

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 I/O operations done by backup, restore or direct path read and write functions. The I/O buffer size is a product of DB_FILE_DIRECT_IO_COUNT and DB_BLOCK_SIZE. The I/O 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.

Note: If you have previously used CCF_IO_SIZE and are migrating to DB_FILE_DIRECT_IO_COUNT, remember that CCF_IO_SIZE was specified in bytes while DB_FILE_DIRECT_IO_COUNT must be specified in database blocks.

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 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 Oracle8i 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_NAME_CONVERT can also be used to rename the datafiles in the clone coltrolfile when setting up the clone database during Tablespace Point-In-Time Recovery. For more information, see Oracle8i Backup and Recovery Guide.

DB_FILE_SIMULTANEOUS_WRITES

Obsoleted in 8.1.3.

DB_FILES

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

operating system-dependent; 200 on Solaris  

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.

Note: The checkpoint process is enabled by default if the system configuration is such that moving checkpoint processing from the logwriter to a separate process is deemed to improve performance. Currently, the criteria are DB_FILES >= 50 or
DB_BLOCK_BUFFERS >= 10000.

For more information, see the Oracle8i Administrator's Guide. 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:

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 Oracle8i 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 Oracle8i 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 DBWn process. The DBWn process and its slaves always write to disk. By default, the value is 0 and I/O slaves are not used.

If DBWR_IO_SLAVES is set to a non-zero value, the numbers of I/O slaves used by the ARCH process LGWR process, and Recovery Manager are set to 4.

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 re-ping 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

Obsoleted in 8.1.3.

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 will have no effect.

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

DISTRIBUTED_LOCK_TIMEOUT

Obsoleted in 8.1.3.

DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME

Obsolete in 8.1.3.

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 occur at an abnormally high rate, causing many in-doubt transactions, you may want to temporarily decrease this parameter's value. 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 Oracle8i Administrator's Guide and Oracle8i 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 Oracle8i Parallel Server Concepts and Administration, Oracle8i Concepts, and Oracle8i Distributed Database Systems.

PDML Restrictions

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

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 Oracle8i Parallel Server Concepts and Administration, Oracle8i Concepts and Oracle8i Distributed Database Systems.

ENT_DOMAIN_NAME

Parameter type:  

X.500 Distinguished Name  

Parameter class:  

static  

Default value:  

none  

Range of values:  

all X.500 Distinguished Name format values  

Note: Global user functionality is currently available only to beta customers. This feature will be available to all users in a subsequent release of Oracle8i.

Use this parameter to indicate which Enterprise Domain (for Global User Security) the server belongs to. Enterprise Roles for the server will be searched for under this domain in the enterprise directory service. See the Oracle Advanced Security Administrator's Guide for more information.

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

Obsoleted in 8.1.3.

FAST_START_IO_TARGET


Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SYSTEM set at runtime  

Default value:  

all the buffers in the cache  

Range of values:  

1000 to all buffers in the cache, setting to 0 disables limiting recovery IOs  

OK to change:  

yes, at run-time  

Multiple instances:  

can have different values  

FAST_START_IO_TARGET (available only with the Oracle Enterprise Edition) specifies the number of IOs that should be needed during crash or instance recovery. It imposes a more accurate bound on the number of recovery IOs than
DB_BLOCK_MAX_DIRTY_TARGET.

When this parameter is set, DBWn writes dirty buffers out more aggressively to keep the number of blocks that must be processed during recovery below the value specified in the parameter. Note that this parameter does not impose a hard limit on the number of recovery IOs. There may be transient workload situations in which the number of IOs needed during recovery is greater than the value specified in this parameter, but if this occurs, DBWn will not slow down database activity.

Smaller values for this parameter result in faster recovery times. This improvement in recovery performance is achieved at the expense of additional writing activity during normal processing. See the Oracle8i Backup and Recovery Guide and V$INSTANCE_RECOVERY for more information.

Setting this parameter's value to 0 disables the mechanism that limits the number of IOs that need to be performed during recovery. All other writing activity is unaffected.

FAST_START_PARALLEL_ROLLBACK

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SYSTEM  

Default value:  

low  

Range of values:  

false, low, high  

FAST_START_PARALLEL_ROLLBACK helps to determine the maximum number of processes which may exist for performing parallel rollback. If the value is false, parallel rollback is disabled. If the value is low, 2 * CPU_COUNT number of processes may be used. If the value is high, at most 4 * CPU_COUNT number of rollback servers are used for parallel rollback.

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 Oracle8i 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_LOCKS

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".

To find the correspondence between filenames and file numbers, query the data dictionary view DBA_DATA_FILES. See "DBA_DATA_FILES".

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

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

GC_LATCHES

Obsoleted in 8.1.3.

GC_LCK_PROCS

Obsoleted in 8.1.3.

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 Oracle8i 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 Oracle8i Parallel Server Concepts and Administration.

GLOBAL_NAMES

Parameter type:  

boolean  

Parameter class:  

dynamic, scope = ALTER SESSION, ALTER SYSTEM  

Default value:  

TRUE  

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

query dependent, appears as 0  

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). Because Oracle computes the value for this parameter based on the query, you need not set the value for this parameter.

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. These parameters are ignored on platforms which specify the SGA's starting address at linktime.

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.

HS_AUTOREGISTER

Parameter type:  

boolean  

Parameter class:  

dynamic, scope= ALTER SYSTEM  

Default value:  

TRUE  

Range of values:  

TRUE, FALSE  

HS_AUTOREGISTER enables or disables automatic self-registration of HS agents. When enabled, this entails uploading information into the server's data dictionary to describe a previously unknown agent class or a new agent version.

Recommended usage is to set this parameter to TRUE. The Oracle server incurs less overhead when establishing subsequent connections through the same agent if this self-registered information is available in the server's data dictionary.

If HS_AUTOREGISTER = FALSE is used as the normal operating mode, new agents or agent version will repeatedly upload on each new connection. Operations on the connection will work, but the server data dictionary will not be populated automatically. Database administrators should identify such situations by watching for upload entries in the server's alert log,

The corrective action is:

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".

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

INSTANCE_NAME

Parameter type:  

string  

Parameter class:  

static  

Default value:  

the SID  

Allowable values:  

any alphanumeric characters  

INSTANCE_NAME is a string value representing the name of the instance and is used to uniquely identify a specific instance when multiple instances share common services names. INSTANCE_NAME should not be confused with the SID, which actually uniquely identifies the instances shared memory on a host.

For more information, see Oracle8i Parallel Server Concepts and Administration and Net8 Administrator's Guide.

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 Oracle8i Parallel Server Concepts and Administration.

JAVA_POOL_SIZE

Parameter type:  

string  

Parameter class:  

static  

Default value:  

10 MB  

JAVA_POOL_SIZE specifies the size in bytes of the Java pool.

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.

JOB_QUEUE_PROCESSES

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SYSTEM  

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. Job queue processes are used to process requests created by DBMS_JOB.

Some job queue requests are created automatically; an example is refresh support for table snapshots. If you wish to have your table snapshots updated automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.

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

LARGE_POOL_MIN_ALLOC

Obsoleted in 8.1.3.

LARGE_POOL_SIZE

Parameter type:  

string  

Parameter class:  

static  

Default value:  

0, unless parallel execution or DBWR_IO_SLAVES are configured  

Range of values:  

minimum: 600K

maximum: at least 2GB (maximum is operating system-specific)  

LARGE_POOL_SIZE lets you specify the size of the large pool allocation heap.

The Large Pool allocation heap is used by the Multi-Threaded Server for session memory, by Parallel Execution for message buffers, and by Backup for disk I/O buffers.

Parallel Execution allocates buffers out of the Large Pool only when PARALLEL_AUTOMATIC_TUNING is set to TRUE.

If LARGE_POOL_SIZE is left unset and the pool is required by parallel execution, then Oracle will compute a value automatically. The computation will be based on PARALLEL_MAX_SERVERS, PARALLEL_THREADS_PER_CPU, PARALLEL_SERVER_INSTANCES, MTS_DISPATCHERS, and DBWR_IO_SLAVES.

The computation will add 250k per session for the MTS server if MTS_DISPATCHERS is configured. A final computation will add a port-specific amount of memory for Backup I/O buffers.

Warning: this default computation can yield a size that is either too large to allocate or causes performance problems. In that case, you should set LARGE_POOL_SIZE to a number sufficiently small so that the database can start.

The value of the parameter can be specified in megabytes or kilobytes. 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".

See Oracle8i Tuning and Oracle8i Migration for further information.

LGWR_IO_SLAVES

Obsoleted in 8.1.3.

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 Oracle8i 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 Oracle8i 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 Oracle8i 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" and "MTS_MULTIPLE_LISTENERS".

For more information about instances, listener processes, and dispatcher processes, see the Oracle8i 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

Obsoleted in 8.1.3.

LOG_ARCHIVE_BUFFER_SIZE

Obsoleted in 8.1.3.

LOG_ARCHIVE_BUFFERS

Obsoleted in 8.1.3.

LOG_ARCHIVE_DEST

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SYSTEM  

Default value:  

NULL string default  

Range of values:  

either a NULL string or any valid path or device name, except raw partitions  

Multiple instances:  

can have different values  

Deprecated in favor of LOG_ARCHIVE_DEST_n when Oracle Enterprise Edition is installed. If Oracle Enterprise Edition is not installed or it is installed but you have not specified LOG_ARCHIVE_DEST_n, this parameter can be used as described below.

LOG_ARCHIVE_DEST is applicable only if you are running the database in ARCHIVELOG mode, or are recovering a database from archived redo logs. LOG_ARCHIVE_DEST is incompatible with the LOG_ARCHIVE_DEST_n parameters, and must be defined as the NULL string (") or ('') when any LOG_ARCHIVE_DEST_n parameter has a non_NULL string value. 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.

If LOG_ARCHIVE_DEST is not explicitly defined and all the LOG_ARCHIVE_DEST_n parameters have NULL string values, LOG_ARCHIVE_DEST is set to an operating system-specific default value on instance startup.

To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the SQL*Plus command ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination. To permanently change the destination, use the command ALTER SYSTEM SET LOG_ARCHIVE_DEST = filespec, where filespec is the new archive destination.

For more information, see the Oracle8i Backup and Recovery Guide. See also "LOG_ARCHIVE_DUPLEX_DEST", "LOG_ARCHIVE_MIN_SUCCEED_DEST", and "V$ARCHIVE_DEST".

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_DEST_n

Parameter type  

string  

Parameter class:  

dynamic, scope = ALTER SYSTEM, ALTER SESSION  

Default value:  

NULL  

Range of values:  

valid keyword definitions  

This parameter is valid only if you have installed the Oracle Enterprise Edition. You may continue to use LOG_ARCHIVE_DEST if you have installed the Oracle Enterprise Edition, however you cannot use both LOG_ARCHIVE_DEST_n and LOG_ARCHIVE_DEST as they are not compatible.

LOG_ARCHIVE_DEST_n defines a destination and attributes for the archive redo log file group. The parameter suffix (1 through 5) specifies one of the 5 corresponding LOG_ARCHIVE_DEST_n destination parameters. The parameter number suffix is defined as the 'handle' displayed by the fixed-table queries.

LOG_ARCHIVE_DEST_n = ("null_string" | SERVICE=tnsnames-service | 
                     LOCATION=directory-spec)[MANDATORY | OPTIONAL]
                     [REOPEN=integer]

SERVICE specifies a standby destination. Net8 (IPC or TCP) will be used to transmit the archivelog. There must be a standby instance associated with the destination. tnsnames-service corresponds to an appropriate service-name in TNSNAMES.ORA.

LOCATION specifies a local file-system destination.

MANDATORY specifies that archiving to the destination must succeed before the REDO logfile can be made available for re-use.

OPTIONAL specifies that successful archiving to the destination is not required before the REDO log file can be made available for re-use. If the "must succeed count" (LOG_ARCHIVE_MIN_SUCCEED_DEST) is met the REDO logfile is marked for re-use. This is the default

REOPEN specifies an interval of time (in seconds) that must pass after an error has been encountered during archiving to the destination before future archives to the destination can be attempted. Future attempts are made when the next REDO logfile is archived. If a destination is MANDATORY Oracle recommend that you specify a REOPEN time that reduces the possibility of primary database shutdown due to lack of available online REDO logfiles.

Following is an example scenario:

LOG_ARCHIVE_MIN_SUCCEED_DEST=2

LOG_ARCHIVE_DEST_1='SERVICE=standby1 OPTIONAL REOPEN=120'
LOG_ARCHIVE_DEST_2='LOCATION=filespec MANDATORY REOPEN=5'
LOG_ARCHIVE_DEST_3='SERVICE=standby2 OPTIONAL REOPEN=60'

LOG_ARCHIVE_DEST_STATE_1=enable
LOG_ARCHIVE_DEST_STATE_2=enable
LOG_ARCHIVE_DEST_STATE_3=enable

In the above example, destination 1 and 3 are standby destinations, both are optional. Destination 1 has a reopen interval of 2 minutes, destination 3 has a reopen interval of 1 minute. Destination 2 is a local destination, completion is mandatory and a five second reopen interval is specified.

All three destinations are enabled and therefore available to ARCHIVELOG operations as target destinations.

The minimum number of destinations that must archive successfully for the redo log to be marked for re-use is set to two. This means, in addition to destination 2 (the mandatory destination) either destination 1 and/or destination 3 must also archive successfully.

LOG_ARCHIVE_DEST_STATE_n

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SYSTEM, ALTER SESSION  

Default value:  

ENABLE  

Range of values:  

ENABLE, DEFER  

LOG_ARCHIVE_DEST_STATE_n specifies the availability state of the corresponding destination. The parameter suffix (1 through 5) specifies one of the 5 corresponding LOG_ARCHIVE_DEST_n destination parameters.

If enabled, a valid log archive destination can be used for a subsequent archiving operation (automatic or manual). If deferred, any valid destination information and attributes are preserved, but the destination is excluded from archiving operations until re-enabled.

The LOG_ARCHIVE_DEST_STATE_n parameters have no effect on the Enable state for the LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST parameters.

Changed by ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n = value or ALTER SESSION SET LOG_ARCHIVE_DEST_STATE_n = value. For example:

ALTER SESSION SET LOG_ARCHIVE_DEST_STATE_n = enable

ALTER SESSION effectively hides the system level value for the session issuing the command. The system level value can only be reestablished by an ALTER SESSION using the current system level value.

Always refer to V$ARCHIVE_DEST for values in use for the current session. Corresponds to V$ARCHIVE_DEST index n.

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  

Deprecated in favor of LOG_ARCHIVE_DEST_n when Oracle Enterprise Edition is installed. If Oracle Enterprise Edition is not installed, this parameter is valid.

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_n", "LOG_ARCHIVE_MIN_SUCCEED_DEST", and "V$ARCHIVE_DEST".

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 Oracle8i 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_MAX_PROCESSES

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SYSTEM  

Default value:  

1  

Range of values:  

any integer from 1 - 10 inclusive  

LOG_ARCHIVE_MAX_PROCESSES specifies the number of ARCH processes to be invoked. This value is evaluated at instance startup if the LOG_ARCHIVE_START initialization parameter has the value TRUE; otherwise, this parameter is evaluated when the ARCH process is invoked via SQL*Plus or SQL syntax.

LOG_ARCHIVE_MIN_SUCCEED_DEST

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SESSION, ALTER SYSTEM  

Default value:  

1  

Range of values:  

1 - 5 (restricted to 1-2 when used with LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST)  

LOG_ARCHIVE_MIN_SUCCEED_DEST defines the minimum number of destinations that must succeed in order for the online logfile to be available for reuse. When automatic archiving is enabled, the value generally cannot be more than the total number of destinations, and the number of enabled, valid MANDATORY destinations plus the number of enabled, valid non-standby OPTIONAL destinations.

If the value is less than the number of enabled, valid MANDATORY destinations, it will be ignored in favor of the MANDATORY destination count. If the value is more than the number of enabled, valid MANDATORY destinations, some of the enabled, valid OPTIONAL non-standby destinations will essentially be treated as MANDATORY.

ALTER SESSION SET LOG_ARCHIVE_MIN_SUCCEED_DEST = n cannot be used when LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST are in use.

Changed by ALTER SYSTEM SET LOG_ARCHIVE_MIN_SUCCEED_DEST = number or ALTER SESSION SET LOG_ARCHIVE_MIN_SUCCEED_DEST = number. ALTER SESSION effectively hides the system level value for the session issuing the command. The system level value can only be re-established by an ALTER SESSION using the current system level value.

LOG_ARCHIVE_MIN_SUCCEED_DEST can be set to 1, and all destinations can be set to the null string as a necessary transitory state used to dynamically switch between the destinations specified by the LOG_ARCHIVE_DEST, LOG_ARCHIVE_DUPLEX_DEST parameters and the destinations specified by the LOG_ARCHIVE_DEST_n parameters.

For more information, see "LOG_ARCHIVE_DEST_n", "LOG_ARCHIVE_DUPLEX_DEST", and "V$ARCHIVE_DEST".

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 SQL*Plus 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 Oracle8i Administrator's Guide.

LOG_BLOCK_CHECKSUM

Obsoleted in 8.1.3.

LOG_BUFFER

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

maximum: 500K or 128K * CPU_COUNT, whichever is greater  

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 Oracle8i 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 DBWn 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" in Appendix C, "Statistics Descriptions".

Note that specifying a value of 0 (zero) for LOG_CHECKPOINT_INTERVAL causes the parameter to be ignored. Only non-zero values of this parameter are considered meaningful. A LOG_CHECKPOINT_INTERVAL value of 0 has the same effect as setting the parameter to infinity.

For more information, see the Oracle8i 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:  

Oracle8i: 900 seconds. Enterprise Edition: 1800 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 Oracle8i 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 Oracle8i Backup and Recovery Guide.

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_FILE_NAME_CONVERT should also be used to rename the logfiles in the clone controlfile when setting up the clone database during Tablespace Point-In-Time Recovery. For more information, see Oracle8i Backup and Recovery Guide.

LOG_FILES

Obsoleted in 8.1.3.

LOG_SIMULTANEOUS_COPIES

Obsoleted in 8.1.3.

LOG_SMALL_ENTRY_MAX_SIZE

Obsoleted in 8.1.3.

MAX_COMMIT_PROPAGATION_DELAY

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

700  

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 700 hundredths of a second, or seven seconds, 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 Oracle8i Parallel Server Concepts and Administration.

MAX_DUMP_FILE_SIZE

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SYSTEM, 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 Oracle8i 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 Oracle8i Administrator's Guide.

MAX_ROLLBACK_SEGMENTS

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

max(30, TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT)  

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 Oracle8i Administrator's Guide.

MAX_TRANSACTION_BRANCHES

Obsoleted in 8.1.3.

MTS_DISPATCHERS

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SYSTEM  

Default value:  

NULL  

The parsing software supports a name-value syntax to enable the specification of 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.  

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.  

PRESENTATION
(PRE or PRES)  

Used to enable support of specific presentation protocols.

If GIOP is specified, the dispatcher will listen on the specified protocol for GIOP messages. For IIOP support, the protocol specified must be TCP.

The default presentation is "TTC".  

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 or 1, 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".  

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 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 Oracle8i Administrator's Guide. See also the Net8 Administrator's Guide.

MTS_LISTENER_ADDRESS

Obsoleted in 8.1.3.

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

20  

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 Oracle8i Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.

MTS_MULTIPLE_LISTENERS

Obsoleted in 8.1.3.

MTS_RATE_LOG_SIZE

Obsoleted in 8.1.3.

MTS_RATE_SCALE

Obsoleted in 8.1.3.

MTS_SERVERS

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SYSTEM,  

Default value:  

1  

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 Oracle8i Administrator's Guide. See also your operating system-specific Oracle documentation for the default value and range of values.

MTS_SERVICE

Obsoleted in 8.1.3.

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.

It can have one of the following values:

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 Oracle8i National Language Support Guide. See also the Oracle8i Administrator's Guide.

NLS_COMP

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SESSION  

Default value:  

BINARY  

Range of values:  

any valid character string, with a maximum of 10 bytes (not including null)  

NLS_COMP lets you avoid the cumbersome process of using NLS_SORT in SQL statements. Normally, comparison in the WHERE clause is binary. To use linguistic comparison, the NLSSORT function must be used. Sometimes this can be tedious, especially when the linguistic sort needed has already been specified in the NLS_SORT session parameter. NLS_COMP can be used in such cases to indicate that the comparisons must be linguistic according to the NLS_SORT session parameter.

For more information, see Oracle8i National Language Support Guide. See also the Oracle8i 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 Oracle8i National Language Support Guide. See also the Oracle8i 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 Oracle8i National Language Support Guide. See also the Oracle8i 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 Oracle8i National Language Support Guide. See also the Oracle8i Administrator's Guide.

NLS_DUAL_CURRENCY

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SESSION  

Default value:  

Dual currency symbol  

Range of values:  

any valid format name  

NLS_DUAL_CURRENCY can be used to override the default dual currency symbol defined in the territory. When starting a new session without setting NLS_DUAL_CURRENCY, the default dual currency symbol defined in the territory of your current language environment will be used. When you set NLS_DUAL_CURRENCY, you will start up a session with its value as the dual currency symbol.

For more information, see Oracle8i National Language Support Guide. See also the Oracle8i 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 Oracle8i National Language Support Guide. See also the Oracle8i 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 Oracle8i National Language Support Guide.

For more information, see Oracle8i National Language Support Guide. See also the Oracle8i 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 Oracle8i National Language Support Guide. See also the Oracle8i 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 Oracle8i National Language Support Guide and the Oracle8i Administrator's Guide. 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 Oracle8i National Language Support Guide.

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 Oracle8i National Language Support Guide and the Oracle8i 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

Obsoleted in 8.1.3.

OPEN_CURSORS

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

64  

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 re-executed by a user.

For more information, see the Oracle8i 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".

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

For more information, see the Oracle8i Administrator's Guide

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".

OPS_ADMIN_GROUP

Obsoleted in 8.1.3.

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; 8.1.3  

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_INDEX_CACHING

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SESSION  

Default value:  

0  

Range of values:  

0-100  

OPTIMIZER_INDEX_CACHING lets the user adjust the behavior of the cost-based optimizer to select nested loops joins more often. The cost of executing a nested loops join where an index is used to access the inner table is highly dependent on the caching of that index in the buffer cache. The amount of caching depends on factors, such as the load on the system and the block access patterns of different users, that the optimizer cannot predict. The user can modify the optimizer's assumptions about index caching for nested loops joins by setting this parameter to a value between 0 and 100 percent and thereby indicate what percentage of the index blocks should be assumed to be in the cache. Setting this parameter to a higher value makes nested loops join look less expensive to the optimizer and it will be more likely to pick nested loops joins over hash or sort-merge joins. The default for this parameter is 0, which gives the old optimizer behavior.

OPTIMIZER_INDEX_COST_ADJ

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SESSION  

Default value:  

100  

Range of values:  

1-10000  

OPTIMIZER_INDEX_COST_ADJ lets the user tune the optimizer behavior for access path selection to be more or less index-friendly. Some users are using the first_rows optimizer mode to get plans that use more index access paths and that are more similar to those generated by the rule-based optimizer. However, the first_rows mode was never intended as a pure mechanism to force the use of indexes. The OPTIMIZER_INDEX_COST_ADJ parameter lets the user adjust the costing of index access paths in the cost-based optimizer and thereby make the optimizer more or less prone to selecting an index access path over a full table scan. The default for this parameter is 100 percent, which makes the optimizer cost index access paths a the regular cost. Any other value will make the optimizer cost the access path at that percentage of the regular cost, e.g., setting it to 50 percent, will make the index access path look half as expensive as normal. The legal range of values for this parameter is 1 to 10000 percent. This parameter can be used to tune the performance of a system where it is felt that the optimizer chooses too few or too many index access paths. The adjustment does not apply to user-defined cost functions for domain indexes.

OPTIMIZER_MAX_PERMUTATIONS

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SESSION  

Default value:  

80,000  

Range of values:  

4-2^32 (~4.3 billion)  

OPTIMIZER_MAX_PERMUTATIONS lets the user limit the amount of work the optimizer spends on optimizing queries with large joins. By restricting the number of permutations of the tables the optimizer will consider, the user can ensure that the parse time for the query stays within acceptable limits.

However, in doing so, there is a slight risk that the optimizer will overlook a good plan it would otherwise have found. The default value for this parameter is 80000, which corresponds to the old behavior. Setting this parameter to a value less than 1000 should ensure parse times of a few seconds or less.

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 Oracle8i Tuning. For more information about the optimizer, see Oracle8i Concepts and Oracle8i Tuning.

OPTIMIZER_PERCENT_PARALLEL

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SESSION  

Default value:  

100  

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 non-zero 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 maximum number of tables in a query block for which the optimizer will consider join orders with Cartesian products.

If there are more tables than the limit, the optimizer will, if possible, only consider join orderings where all the join are between tables that are connected by equality join conditions. This prevent the optimizer from spending an inordinate amount of time on join orderings that are likely to be suboptimal.

ORACLE_TRACE_COLLECTION_NAME

Parameter type:  

string  

Parameter class:  

static  

Default value:  

NULL  

Range of values:  

valid collection name up to 16 characters long (except for platforms that enforce 8-character file names)  

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 collection file .dat). If the ORACLE_TRACE_ENABLE parameter is set to TRUE, setting this value to a non-null string will start a default Oracle Trace collection that will run until this value is set to NULL again.

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 the Oracle Trace collection definition (.cdf) and data collection (.dat) files are located. If you accept the default, the Oracle Trace .cdf and .dat files will be located in $ORACLE_HOME/otrace/admin/cdf.

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. When the Range of Values is zero, there is no size limit.

ORACLE_TRACE_ENABLE

Parameter type:  

boolean  

Parameter class:  

static  

Default value:  

FALSE  

Range of values:  

TRUE, FALSE  

To enable Oracle Trace collections for the server, ORACLE_TRACE_ENABLE should be set and left at TRUE. When set to TRUE, this alone does not start an Oracle Trace collection, but it allows Oracle Trace to be used for that server.

When set to TRUE, an Oracle Trace collection can then be started by using the Oracle Trace Manager application (supplied with the Oracle Diagnostics Pack), or by including a name in the ORACLE_TRACE_COLLECTION_NAME parameter (default = null).

ORACLE_TRACE_FACILITY_NAME

Parameter type:  

string  

Parameter class:  

static  

Default value:  

oracled  

Range of values:  

valid facility name 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 which can be collected for a product that used the Oracle Trace data collection API. Products can have multiple product definition files (multiple event sets and data items). The Oracle server has multiple event sets and therefore multiple product definition files. Oracle Corporation recommends that you use the "default" event set for Oracle server collections ORCLED.FDF. See the Oracle Trace documentation for more information on the Oracle 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. For Solaris, the file specification is ?/otrace/admin/fdf/. For NT, the file specification is: %OTRACE80%\ADMIN\FDF\.

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 Oracle8i 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 Oracle8i Administrator's Guide.

PARALLEL_ADAPTIVE_MULTI_USER

Parameter type:  

boolean  

Parameter class:  

dynamic, scope = ALTER SYSTEM  

Default value:  

If PARALLEL_AUTOMATIC_TUNING = TRUE, then TRUE, else 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 execution. It does this by automatically reducing the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction factor.

The reduction factor is computed using the load on the system and the number of active parallel execution users. This load is compared to a target optimal system load which is computed using the number of CPUs on the system and the parameter PARALLEL_THREADS_PER_CPU among others.

The algorithm assumes that the system has been tuned for optimal performance in a single user environment, and that it works best when the parameter
PARALLEL_AUTOMATIC_TUNING set to true, and the tables and hint use the DEFAULT degree of parallelism.

On a 16 CPU machine, for example, the default degree of parallelism could be set to 32. If one user issues a parallel execution, that user gets a degree of 32, effectively using all of the CPUs and memory in the system. When a second user enters the system, that user could get a degree of 16. As the number of users on the system increases, this algorithm will continue to reduce the degree until the users are running using degree 1, when the load on the system significantly exceeds the target optimal load. Over time, the behavior of the system as a whole will be improved.

For more information, see Oracle8i Tuning.

PARALLEL_AUTOMATIC_TUNING

Parameter type:  

boolean  

Parameter class:  

static  

Default value:  

FALSE  

Range of values:  

TRUE, FALSE  

Note: This parameter applies to Parallel Execution, not the Oracle8i Parallel Server Option.

Enable PARALLEL_AUTOMATIC_TUNING when you want Oracle to determine the default values for parameters that control Parallel Execution. In addition to setting this parameter, you must enable PARALLEL, for the target tables in the system. All subsequent tuning will be done by the system.

If you used Parallel Execution in a previous release and are now enabling PARALLEL_AUTOMATIC_TUNING, you should reduce the amount of memory allocated from the Shared Pool to account for the decreased demand on that pool. This memory will be allocated from the Large Pool, and will be computed automatically if LARGE_POOL_SIZE is left unset.

This will include setting the PARALLEL_ADAPTIVE_MULTI_USER parameter which will override user-provided hints in favor of maintaining the load on the system within acceptable ranges. The database administrator can override any of the system-provided defaults if desired.

For more information see Oracle8i Tuning.

PARALLEL_BROADCAST_ENABLED

Parameter type:  

boolean  

Parameter class:  

dynamic, scope = ALTER SESSION  

Default value:  

FALSE  

Range of values:  

TRUE, FALSE  

Note: This parameter refers to Parallel Execution, not the Oracle8i Parallel Server Option.

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 Oracle8i Tuning.

PARALLEL_DEFAULT_MAX_INSTANCES

Obsoleted in 8.1.3.

PARALLEL_EXECUTION_MESSAGE_SIZE

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

operating system-dependent. (Usually 2148 if PARALLEL_AUTOMATIC_TUNING is FALSE, 4096 if PARALLEL_AUTOMATIC_TUNING is TRUE.  

Range of values:  

2148 - infinity  

Multiple instances:  

must have the same value  

PARALLEL_EXECUTION_MESSAGE_SIZE specifies the size of messages for parallel execution (formerly parallel query, PDML, Parallel Recovery, replication). The default value should be adequate for most applications.

Typical values are 2048 or 4096 bytes. Larger values would require a larger shared pool. Replication gets no benefit from increasing the size.

Note: When PARALLEL_AUTOMATIC_TUNING is set to TRUE, message buffers are allocated out of the Large Pool. In this case, the default will generally be higher.

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 execution slaves. Parallel operations will spawn parallel execution 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 Oracle8i Parallel Server Concepts and Administration.

PARALLEL_MAX_SERVERS

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

depends on CPU_COUNT, PARALLEL_AUTOMATIC_TUNING, and PARALLEL_ADAPTIVE_MULTI_USER  

Range of values:  

0 - 256  

Multiple instances:  

each instance must have either a value of zero or the same value as the other instances  

Note: This parameter applies to Parallel Execution, not the Oracle8i Parallel Server Option.

PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution 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.

PARALLEL_MAX_SERVERS will be used to size the Large Pool and other memory structures and parameters that are used by parallel execution.

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 execution 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.

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 Oracle8i Tuning.

PARALLEL_MIN_MESSAGE_POOL

Obsoleted in 8.1.3.

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 execution.

Setting this parameter ensures that parallel execution 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.

The parameter PARALLEL_MIN_PERCENT can also be used in conjunction with PARALLEL_ADAPTIVE_MULTI_USER. In a multi-user environment, a user or application can set PARALLEL_MIN_PERCENT to a minimum value until sufficient resources are available on the system and an acceptable degree is returned.

For more information see Oracle8i Tuning.

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  

Note: This parameter applies to Parallel Execution, not the Oracle8i Parallel Server Option.

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 Oracle8i Tuning.

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 Oracle8i Parallel Server Concepts and Administration.

PARALLEL_SERVER_IDLE_TIME

Obsoleted in 8.1.3.

PARALLEL_SERVER_INSTANCES

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

1  

Range of values:  

any non-zero value  

PARALLEL_SERVER_INSTANCES shows the number of instances currently configured. It is used to size SGA structures which depend on the number of instances configured. Setting this parameter properly will improve memory use of the SGA. The default values of several parameters are computed using this number.

PARALLEL_THREADS_PER_CPU

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SYSTEM  

Default value:  

OS-dependent, usually 2  

Range of values:  

any non-zero number  

Note: This parameter applies to Parallel Execution, not the Oracle8i Parallel Server Option.

PARALLEL_ THREADS_PER_CPU is used to set the default degree of parallelism, and to tune the parallel adaptive and load balancing algorithms. The parameter describes the number of processes or threads that a CPU can handle during parallel execution. The default is platform-dependent. The default provided by the system should be adequate for most cases. This number should be decreased from the default provided if the machine appears to be overloaded when a representative query is executed. The value for this parameter should be increased if the system is I/O bound.

For more information, see Oracle8i Tuning.

PARALLEL_TRANSACTION_RESOURCE_TIMEOUT

Obsoleted in 8.1.3.

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:

PRE_PAGE_SGA

Parameter type:  

boolean  

Parameter class:  

static  

Default value:  

FALSE  

Range of values:  

FALSE, TRUE  

OK to change:  

no  

PRE_PAGE_SGA is a platform-specific parameter; see your platform-specific documentation for further details.

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 startup 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 buildup. 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:  

depends on PARALLEL_MAX_SERVERS  

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 execution 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 parameter.

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

PUSH_JOIN_PREDICATE

Obsoleted in 8.1.3.

QUERY_REWRITE_ENABLED

Parameter type:  

boolean  

Parameter class:  

dynamic, scope = ALTER SYSTEM, ALTER SESSION  

Default value:  

FALSE  

Range of values:  

TRUE, FALSE  

Multiple instances:  

can have different values  

OK to change:  

yes  

QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting.

Query rewriting is enabled for a particular materialized view only if both the session parameter and the individual materialized view are enabled and when cost-based optimization is enabled.

QUERY_REWRITE_INTEGRITY

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SESSION, ALTER SYSTEM  

Default value:  

ENFORCE  

Range of values:  

ENFORCE, NO_ENFORCE, USE_STALE  

Multiple instances:  

can have different values  

OK to change:  

yes  

QUERY_REWRITE_INTEGRITY determines the degree to which query rewriting must be enforced by the Oracle server. In the safest level, query rewrite transformations that rely on unenforced relationships are not used.

With ENFORCE, consistency and integrity are enforced and guaranteed by Oracle. With NO_ENFORCE, rewrites are allowed using relationships that have been declared, but that are not enforced by Oracle. With USE_STALE, rewrites are allowed using unenforced relationships, and materialized views are eligible for rewrite even if they are known to be inconsistent with the underlying detail data.

RDBMS_SERVER_DN

Parameter type:  

X.500 Distinguished Name  

Parameter class:  

static  

Default value:  

none  

Range of values:  

all X.500 Distinguished Name format values  

Note: Global user functionality is currently available only to beta customers. This feature will be available to all users in a subsequent release of Oracle8i.

This parameter value is the Distinguished Name of the RDBMS server. It is used for retrieving Enterprise Roles from an enterprise directory service. See the Oracle Advanced Security Administrator's Guide for more information.

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:

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 Oracle8i Parallel Server Concepts and Administration.

REDUCE_ALARM

Obsoleted in 8.1.3.

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 (required for the Oracle Parallel Server) 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 Oracle8i 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 Oracle8i 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 Oracle8i 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 Oracle8i Administrator's Guide.

RESOURCE_MANAGER_PLAN

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SYSTEM  

Default value:  

NULL  

Range of values:  

any valid character string  

This parameter dictates which top plan to use for this instance. The resource manager will load this top plan as well as all its descendants (subplans, directives and consumer groups). If the parameter is not specified, the resource manager is, by default, off.

The administrator may use the ALTER SYSTEM command on the parameter to turn on the resource manager (if it was previously off), turn off the resource manager or change the current plan schema (if it was previously on). If a plan is specified that does not exist in the data dictionary, an error message will be returned.

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 Oracle8i Administrator's Guide.

ROW_CACHE_CURSORS

Obsoleted in 8.1.3.

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 Oracle8i Tuning.

SEQUENCE_CACHE_ENTRIES

Obsoleted in 8.1.3.

SEQUENCE_CACHE_HASH_BUCKETS

Obsoleted in 8.1.3.

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-6 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.  

SERVICE_NAMES

Parameter type:  

string  

Parameter class:  

static  

Default value:  

DB_NAME.DB_DOMAIN if defined  

Range of values:  

any ASCII string, or comma-separated list of string names  

SERVICE_NAMES specifies the service names supported by the instance.

SERVICE_NAMES is one or more strings which represent the names of the database on the network. Net8 8.1 wants a service name, rather than a SID, to identify a database. It is possible to provide multiple services names so that different usages of a single database can be identified separately. Service names can also be used to identify a single service that is available from two different databases through the use of replication.

Example:

SERVICE_NAMES = sales.acme.com, widgetsales.acme.com

This value/values will be sent to the TNS Listeners as the service name(s) that this instance belongs to. If the names in this parameter are not domain qualified, they will be qualified with the value of the DB_DOMAIN parameter before being sent to the listener. If this parameter is not specified, as of 8.1, the default value that will be registered is <db_name>.<db_domain>.

Using the above example, the client CONNECT_DATA should read as follows:

(CONNECT_DATA=(SERVICE_NAMES=widgetsales.acme.com))

See Net8 Administrator's Guide.for further information.

SESSION_CACHED_CURSORS

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SESSION  

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 Oracle8i Concepts.

SHADOW_CORE_DUMP

Parameter type:  

string  

Parameter class:  

static  

Default value:  

PARTIAL  

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

Obsoleted in 8.1.3.

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 execution, the shared pool includes the parallel execution message buffers.

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

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 will signal 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 Oracle8i Tuning.

SHARED_POOL_SIZE

Parameter type:  

string  

Parameter class:  

static  

Default value:  

If 64 bit, 64MB, else 16MB  

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, stored procedures, control structures, Parallel Execution message buffers among others. Larger values improve performance in multi-user systems. Smaller values use less memory.

If you set PARALLEL_AUTOMATIC_TUNING to TRUE, Parallel Execution message buffers are allocated out of the large pool. If you set it to FALSE, Oracle allocates these buffers from the shared pool. See "LARGE_POOL_SIZE" and Oracle8i Migration for details. To monitor utilization of the shared pool, query the view V$SGASTAT. For example:

select * from v$sgastat;

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 Oracle8i 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 two database blocks 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. Multiple sort spaces of this size may be allocated for each query. 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 keeps its own memory area, as specified by SORT_AREA_RETAINED_SIZE. If the multi-threaded server is used, allocation is to the SGA until the value in SORT_AREA_RETAINED_SIZE is reached, the difference between SORT_AREA_RETAINED_SIZE and SORT_AREA_SIZE is allocated to the PGA.

For more information, see Oracle8i Concepts.

SORT_AREA_SIZE

Parameter type:  

integer  

Parameter class:  

dynamic, scope= ALTER SESSION, ALTER SYSTEM DEFERRED  

Default value:  

operating system-dependent  

Range of values:  

minimum-the value equivalent of six database blocks
maximum-system-dependent  

SORT_AREA_SIZE specifies the maximum amount, in bytes, of memory to use for a sort. After the sort is complete and all that remains to do is to return the rows, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is returned, all memory is freed.

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.

Larger values of SORT_AREA_SIZE permit more sorts to be performed in memory. If more space is required to complete the sort than will fit into the memory provided, then temporary segments on disk are used to 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 Oracle8i Concepts. See also your operating system-specific Oracle documentation for the default value on your system.

SORT_DIRECT_WRITES

Obsoleted in 8.1.3.

SORT_MULTIBLOCK_READ_COUNT

Parameter type:  

integer  

Parameter class:  

dynamic, scope = ALTER SESSION, ALTER SYSTEM DEFERRED  

Default value:  

2  

Range of values:  

1 - system-dependent value  

SORT_MULTIBLOCK_READ_COUNT specifies the number of database blocks to read each time a sort performs a read from a temporary segment. Temporary segments are used by a sort when the data does not fit in SORT_ARE_SIZE of memory. In these situations, sort writes out sections of data to temporary segments in the form of sorted runs. Once all the data has been partially sorted to these runs, sort merges the runs by reading pieces of them from the temporary segment into memory to produce the final sorted output. If SORT_AREA_SIZE is not large enough to merge all the runs at once, subsets of the runs are merged in a number of merge passes.

Increasing the SORT_MULTIBLOCK_READ_COUNT parameter forces sort to read a larger section of each run into memory during a merge pass. This reduces the merge width, or number of runs that can be merged in one merge pass, and may increase the number of merge passes. Each merge pass produces an intermediate run on disk, a run that contains all the data that was part of the runs that were just merged. Any increase in I/O throughput obtained by increasing SORT_MULTIBLOCK_READ_COUNT needs to be balanced with a possible increase in total amount of I/O performed due to an increase in the number of merge passes.

Sort may read more blocks at a time than what is specified by SORT_MULTIBLOCK_READ_COUNT in cases where the number of runs, and therefore the merge width is small relative to SORT_AREA_SIZE.

SORT_READ_FAC

Obsoleted in 8.1.3.

SORT_SPACEMAP_SIZE

Obsoleted in 8.1.3.

SORT_WRITE_BUFFER_SIZE

Obsoleted in 8.1.3.

SORT_WRITE_BUFFERS

Obsoleted in 8.1.3.

SPIN_COUNT

Obsoleted in 8.1.3.

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.

SQL_TRACE

Parameter type:  

boolean  

Parameter class:  

static  

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 Oracle8i Tuning. See also Oracle8i SQL Reference.

STANDBY_ARCHIVE_DEST

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SYSTEM  

Default value:  

NULL  

Range of values:  

NULL string or valid path/device name other than raw  

STANDBY_ARCHIVE_DEST defines the standby database destination for the archive redo log file group. It is used by the RFS server on the standby database as the archive log destination, so that it can be specified separately from the LOG_ARCHIVE_DEST parameter. Note that there is no corresponding RFS_ARCHIVE_DUPLEX_DEST parameter.

STANDBY_ARCHIVE_DEST specifies the location of archivelogs arriving from a primary instance. STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT are used to fabricate the fully-qualified archivelog filename at the standby site.

Use the view V$ARCHIVE_DEST of the standby instance to see the values for this parameter. The syntax is:

STANDBY_ARCHIVE_DEST='filespec'

STAR_TRANSFORMATION_ENABLED

Parameter type:  

string  

Parameter class:  

dynamic, scope = ALTER SESSION  

Default value:  

FALSE  

Range of values:  

TRUE, FALSE, or TEMP_DISABLE  

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. If set to
TEMP_DISABLE, the optimizer will consider performing a cost-based query transformation on the star query but will not use temporary tables in the star transformation.

For more information, see Oracle8i 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.

TEMPORARY_TABLE_LOCKS

Obsoleted in 8.1.3.

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 Oracle8i Parallel Server Concepts and Administration and Oracle8i 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_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 Oracle8i 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 will be 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 Oracle8i Concepts and the Oracle8i Administrator's Guide.

TRANSACTIONS_PER_ROLLBACK_SEGMENT

Parameter type:  

integer  

Parameter class:  

static  

Default value:  

5  

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 Oracle8i 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

Obsoleted in 8.1.3.

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 Oracle8i 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.




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index