ALTER SYSTEM

Purpose

Use the ALTER SYSTEM statement to dynamically alter your Oracle instance. The settings stay in effect as long as the database is mounted.

Prerequisites

You must have ALTER SYSTEM system privilege.

To specify the archive_log_clause, you must have the SYSDBA or SYSOPER system privilege.

Syntax

alter_system::=

Text description of alter_system

archive_log_clause::=

Text description of archive_log_clause

end_session_clauses::=

Text description of end_session_clauses

alter_system_set_clause::=

Text description of alter_system_set_clause

alter_system_reset_clause::=

Text description of alter_system_reset_clause

Keywords and Parameters

archive_log_clause

The archive_log_clause manually archives redo log files or enables or disables automatic archiving. To use this clause, your instance must have the database mounted. The database can be either open or closed unless otherwise noted.

THREAD Clause

Specify THREAD to indicate the thread containing the redo log file group to be archived.

Restriction: Set this parameter only if you are using Oracle with Real Application Clusters.

SEQUENCE Clause

Specify SEQUENCE to manually archive the online redo log file group identified by the log sequence number integer in the specified thread. If you omit the THREAD parameter, Oracle archives the specified group from the thread assigned to your instance.

CHANGE Clause

Specify CHANGE to manually archive the online redo log file group containing the redo log entry with the system change number (SCN) specified by integer in the specified thread. If the SCN is in the current redo log file group, Oracle performs a log switch. If you omit the THREAD parameter, Oracle archives the groups containing this SCN from all enabled threads.

You can use this clause only when your instance has the database open.

CURRENT Clause

Specify CURRENT to manually archive the current redo log file group of the specified thread, forcing a log switch. If you omit the THREAD parameter, Oracle archives all redo log file groups from all enabled threads, including logs previous to current logs. You can specify CURRENT only when the database is open.

Specify NOSWITCH if you want to manually archive the current redo log file group without forcing a log switch. You can use this clause only when your instance has the database mounted but not open. If the database is open, this operation closes the database automatically. You must then manually shut down the database before you can reopen it.

GROUP Clause

Specify GROUP to manually archive the online redo log file group with the GROUP value specified by integer. You can determine the GROUP value for a redo log file group by querying the data dictionary view DBA_LOG_FILES. If you specify both the THREAD and GROUP parameters, the specified redo log file group must be in the specified thread.

LOGFILE Clause

Specify LOGFILE to manually archive the online redo log file group containing the redo log file member identified by 'filename'. If you specify both the THREAD and LOGFILE parameters, the specified redo log file group must be in the specified thread.

If the database was mounted with a backup controlfile, specify USING BACKUP CONTROLFILE to permit archiving of all online logfiles, including the current logfile.

Restriction: You must archive redo log file groups in the order in which they are filled. If you specify a redo log file group for archiving with the LOGFILE parameter, and earlier redo log file groups are not yet archived, Oracle returns an error.

NEXT Clause

Specify NEXT to manually archive the next online redo log file group from the specified thread that is full but has not yet been archived. If you omit the THREAD parameter, Oracle archives the earliest unarchived redo log file group from any enabled thread.

ALL Clause

Specify ALL to manually archive all online redo log file groups from the specified thread that are full but have not been archived. If you omit the THREAD parameter, Oracle archives all full unarchived redo log file groups from all enabled threads.

START Clause

Specify START to enable automatic archiving of redo log file groups.

Restriction: You can enable automatic archiving only for the thread assigned to your instance.

TO location Clause

Specify TO 'location' to indicate the primary location to which the redo log file groups are archived. The value of this parameter must be a fully specified file location following the conventions of your operating system. If you omit this parameter, Oracle archives the redo log file group to the location specified by the initialization parameters LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_n.

STOP Clause

Specify STOP to disable automatic archiving of redo log file groups. You can disable automatic archiving only for the thread assigned to your instance.

CHECKPOINT Clause

Specify CHECKPOINT to explicitly force Oracle to perform a checkpoint, ensuring that all changes made by committed transactions are written to datafiles on disk. You can specify this clause only when your instance has the database open. Oracle does not return control to you until the checkpoint is complete.

GLOBAL

In a Real Application Clusters environment, this setting causes Oracle to perform a checkpoint for all instances that have opened the database. This is the default.

LOCAL

In a Real Application Clusters environment, this setting causes Oracle to perform a checkpoint only for the thread of redo log file groups for the instance from which you issue the statement.

CHECK DATAFILES Clause

In a distributed database system, such as a Real Application Clusters environment, this clause updates an instance's SGA from the database control file to reflect information on all online datafiles.

• Specify GLOBAL to perform this synchronization for all instances that have opened the database. This is the default.

• Specify LOCAL to perform this synchronization only for the local instance.

Your instance should have the database open.

end_session_clauses

The end_session_clauses give you several ways to end the current session.

DISCONNECT SESSION Clause

Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared Server). To use this clause, your instance must have the database open. You must identify the session with both of the following values from the V$SESSION view:

• For integer1, specify the value of the SID column.

• For integer2, specify the value of the SERIAL# column.

If system parameters are appropriately configured, application failover will take effect.

• The POST_TRANSACTION setting allows ongoing transactions to complete before the session is disconnected. If the session has no ongoing transactions, this clause has the same effect as KILL SESSION, described below.

• The IMMEDIATE setting disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.
  • If you also specify POST_TRANSACTION and the session has ongoing transactions, the IMMEDIATE keyword is ignored.

  • If you do not specify POST_TRANSACTION, or you specify POST_TRANSACTION but the session has no ongoing transactions, this clause has the same effect as KILL SESSION IMMEDIATE, described below.

KILL SESSION Clause

The KILL SESSION clause lets you mark a session as dead, roll back ongoing transactions, release all session locks, and partially recover session resources. To use this clause, your instance must have the database open, and your session and the session to be killed must be on the same instance. You must identify the session with both of the following values from the V$SESSION view:

• For integer1, specify the value of the SID column.

• For integer2, specify the value of the SERIAL# column.

If the session is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, Oracle waits for this activity to complete, marks the session as dead, and then returns control to you. If the waiting lasts a minute, Oracle marks the session to be killed and returns control to you with a message that the session is marked to be killed. The PMON background process then marks the session as dead when the activity is complete.

Whether or not the session has an ongoing transaction, Oracle does not recover the entire session state until the session user issues a request to the session and receives a message that the session has been killed.

IMMEDIATE

Specify IMMEDIATE to instruct Oracle to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.

DISTRIBUTED RECOVERY Clause

The DISTRIBUTED RECOVERY clause lets you enable or disable distributed recovery. To use this clause, your instance must have the database open.

ENABLE

Specify ENABLE to enable distributed recovery. In a single-process environment, you must use this clause to initiate distributed recovery.

You may need to issue the ENABLE DISTRIBUTED RECOVERY statement more than once to recover an in-doubt transaction if the remote node involved in the transaction is not accessible. In-doubt transactions appear in the data dictionary view DBA_2PC_PENDING.

DISABLE

Specify DISABLE to disable distributed recovery.

RESTRICTED SESSION Clause

The RESTRICTED SESSION clause lets you restrict logon to Oracle.

You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

ENABLE

Specify ENABLE to allows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.

DISABLE

Specify DISABLE to reverse the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle. This is the default.

FLUSH SHARED_POOL Clause

The FLUSH SHARED POOL clause lets you clear all data from the shared pool in the system global area (SGA). The shared pool stores

• Cached data dictionary information and

• Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers.

This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

SWITCH LOGFILE Clause

The SWITCH LOGFILE clause lets you explicitly force Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. When you force a log switch, Oracle begins to perform a checkpoint but returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open.

SUSPEND | RESUME

The SUSPEND clause lets you suspend all I/O (datafile, control file, and file header) as well as queries, in all instances, enabling you to make copies of the database without having to handle ongoing transactions.

Restrictions:

• Do not use this clause unless you have put the database tablespaces in hot backup mode.

• If you start a new instance while the system is suspended, that new instance will not be suspended.

The RESUME clause lets you make the database available once again for queries and I/O.

QUIESCE RESTRICTED | UNQUIESCE

Use the QUIESCE RESTRICTED and UNQUIESCE clauses to put the database in and take it out of the quiesced state. This state enables database administrators to perform administrative operations that cannot be safely performed in the presence of concurrent transactions, queries, or PL/SQL operations.

If multiple QUIESCE RESTRICTED or UNQUIESCE statements issue at the same time from different sessions or instances, all but one will receive an error.

QUIESCE RESTRICTED

Specify QUIESCE RESTRICTED to put the database in the quiesced state. For all instances with the database open, this clause has the following effect:

• Oracle instructs the Database Resource Manager in all instances to prevent all inactive sessions (other than SYS and SYSTEM) from becoming active. No user other than SYS and SYSTEM can start a new transaction, a new query, a new fetch, or a new PL/SQL operation.

• Oracle waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or abort). Oracle also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish. If a query is carried out by multiple successive OCI fetches, Oracle does not wait for all fetches to finish. It waits for the current fetch to finish and then blocks the next fetch. Oracle also waits for all sessions (other than those of SYS or SYSTEM) that hold any shared resources (such as enqueues) to release those resources. After all these operations finish, Oracle places the database into quiesced state and finishes executing the QUIESCE RESTRICTED statement.

• If an instance is running in shared server mode, Oracle instructs the Database Resource Manager to block logins (other than SYS or SYSTEM) on that instance. If an instance is running in non-shared-server mode, Oracle does not impose any restrictions on user logins in that instance.

During the quiesced state, you cannot change the Resource Manager plan in any instance.

UNQUIESCE

Specify UNQUIESCE to take the database out of quiesced state. Doing so permits transactions, queries, fetches, and PL/SQL procedures that were initiated by users other than SYS or SYSTEM to be undertaken once again. The UNQUIESCE statement does not have to originate in the same session that issued the QUIESCE RESTRICTED statement.

SHUTDOWN Clause

The SHUTDOWN clause is relevant only if your system is using Oracle's shared server architecture. It shuts down a dispatcher identified by dispatcher_name. The dispatcher_name must be a string of the form 'Dxxx', where xxx indicates the number of the dispatcher. For a listing of dispatcher names, query the NAME column of the V$DISPATCHER dynamic performance view.

• If you specify IMMEDIATE, the dispatcher stops accepting new connections immediately and Oracle terminates all existing connections through that dispatcher. After all sessions are cleaned up, the dispatcher process literally shuts down.

• If you do not specify IMMEDIATE, the dispatcher stops accepting new connections immediately but waits for all its users to disconnect and for all its database links to terminate. Then it literally shuts down.

REGISTER Clause

Specify REGISTER to instruct the PMON background process to register the instance with the listeners immediately. If you do not specify this clause, registration of the instance does not occur until the next time PMON executes the discovery routine. As a result, clients may not be able to access the services for as long as 60 seconds after the listener is started.

See Also: Oracle9i Database Concepts and Oracle9i Net Services Administrator's Guide for information on the PMON background process and listeners

alter_system_set_clause

The alter_system_set_clause lets you set or reset the value of any initialization parameter. The parameters are described in "Initialization Parameters and ALTER SYSTEM".

When setting the parameter's value, you can specify additional settings as follows:

COMMENT

The COMMENT clause lets you associate a comment string with this change in the value of the parameter. If you also specify SPFILE, this comment will appear in the parameter file to indicate the most recent change made to this parameter.

DEFERRED

The DEFERRED keyword sets or modifies the value of the parameter for future sessions that connect to the database. Current sessions retain the old value.

SCOPE

The SCOPE clause lets you specify when the change takes effect. Scope depends on whether you are started up the database using a parameter file (pfile) or server parameter file (spfile).

MEMORY

MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), this is the only scope you can specify.

SPFILE

SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify SPFILE when changing the value of a static parameter.

BOTH

BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again.

If a server parameter file was used to start up the database, BOTH is the default. If a parameter file was used to start up the database, MEMORY is the default, as well as the only scope you can specify.

SID

The SID clause is relevant only in a Real Application Clusters environment. This clause lets you specify the SID of the instance where the value will take effect.

• Specify SID = '*' if you want Oracle to change the value of the parameter for all instances.

• Specify SID = 'sid' if you want Oracle to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID = '*'.

If you do not specify this clause:

• If the instance was started up with a pfile (client-side initialization parameter file), Oracle assumes the SID of the current instance.

• If the instance was started up with an spfile (server parameter file), Oracle assumes SID = '*'.

If you specify an instance other than the current instance, Oracle sends a message to that instance to change the parameter value in the memory of that instance.

alter_system_reset_clause

The alter_system_reset_clause is for use in a Real Application Clusters environments. It gives you separate control for an individual instance over parameters that may have been set for all instances in a server parameter file. The SCOPE clause has the same behavior as described for the alter_system_set_clause.

SID

Specify the SID clause to remove a previously specified setting of this parameter for your instance (that is, a previous ALTER SYSTEM SET ... SID = 'sid' statement). Your instance will assume the value of the parameter as specified in a previous or subsequent ALTER SYSTEM SET ... SID = '*' statement.

See Also: Oracle9i Real Application Clusters Deployment and Performance for information on setting parameter values for an individual instance in a Real Application Clusters environment

Initialization Parameters and ALTER SYSTEM

This section contains an alphabetical listing of all initialization parameters. These are brief descriptions only. For a complete description of these parameters, please refer to their full description in Oracle9i Database Reference.

ACTIVE_INSTANCE_COUNT

Parameter type	Integer
Default value	There is no default value.
Parameter class	Static
Range of values	1 or >= the number of instances in the cluster. (Values other than 1 have no effect on the active or standby status of any instances.)
Oracle9i Real Application Clusters	You must set this parameter for every instance, and multiple instances must have the same value.

Note: This parameter functions only in a cluster with only two instances.

ACTIVE_INSTANCE_COUNT enables you to designate one instance in a two-instance cluster as the primary instance and the other instance as the secondary instance. This parameter has no functionality in a cluster with more than two instances.

AQ_TM_PROCESSES

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 to 10

AQ_TM_PROCESSES enables time monitoring of queue messages. The times can be used in messages that specify delay and expiration properties. Values from 1 to 10 specify the number of queue monitor processes created to monitor the messages. If AQ_TM_PROCESSES is not specified or is set to 0, then the queue monitor is not created.

ARCHIVE_LAG_TARGET

Parameter type	Integer
Default value	0 (disabled)
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 or any integer in [60, 7200]
Oracle9i Real Application Clusters	Multiple instances should use the same value.

ARCHIVE_LAG_TARGET limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after a user-specified time period elapses.

AUDIT_FILE_DEST

Parameter type	String
Syntax	AUDIT_FILE_DEST = 'directory'
Default value	ORACLE_HOME/rdbms/audit
Parameter class	Static

AUDIT_FILE_DEST specifies the directory where Oracle stores auditing files.

AUDIT_TRAIL

Parameter type	String
Syntax	AUDIT_TRAIL = {NONE | FALSE | DB | TRUE | OS}
Default value	There is no default value.
Parameter class	Static

AUDIT_TRAIL enables or disables the automatic writing of rows to the audit trail.

BACKGROUND_CORE_DUMP

Parameter type	String
Syntax	BACKGROUND_CORE_DUMP = {FULL | PARTIAL}
Default value	PARTIAL
Parameter class	Static

BACKGROUND_CORE_DUMP is primarily a UNIX parameter. It specifies whether Oracle includes the SGA in the core file for Oracle background processes.

BACKGROUND_DUMP_DEST

Parameter type	String
Syntax	BACKGROUND_DUMP_DEST = {pathname | directory}
Default value	Operating system-dependent
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any valid local path, directory, or disk

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

BACKUP_TAPE_IO_SLAVES

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SYSTEM ... DEFERRED
Range of values	true | false

BACKUP_TAPE_IO_SLAVES specifies whether I/O server processes (also called slaves) are used by the Recovery Manager to back up, copy, or restore data to tape. When the value is set to true, Oracle uses an I/O server process to write to or read from a tape device. When the value is set to false (the default), Oracle does not use I/O server process for backups. Instead, the shadow process engaged in the backup accesses the tape device.

BITMAP_MERGE_AREA_SIZE

Parameter type	Integer
Default value	1048576 (1 MB)
Parameter class	Static
Range of values	Operating system-dependent

Note: Oracle does not recommend using the BITMAP_MERGE_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. BITMAP_MERGE_AREA_SIZE is retained for backward compatibility.

BITMAP_MERGE_AREA_SIZE is relevant only for systems containing bitmap indexes. It specifies the amount of memory Oracle uses to merge bitmaps retrieved from a range scan of the index. The default value is 1 MB. A larger value usually improves performance, because the bitmap segments must be sorted before being merged into a single bitmap.

BLANK_TRIMMING

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

BLANK_TRIMMING specifies the data assignment semantics of character datatypes.

BUFFER_POOL_KEEP

Parameter type	String
Syntax	BUFFER_POOL_KEEP = {integer | (BUFFERS:integer, LRU_LATCHES:integer)} where integer is the number of buffers and, optionally, the number of LRU latches.
Default value	There is no default value.
Parameter class	Static

Note: This parameter is deprecated in favor of the DB_KEEP_CACHE_SIZE parameter. Oracle recommends that you use DB_KEEP_CACHE_SIZE instead. Also, BUFFER_POOL_KEEP cannot be combined with the new dynamic DB_KEEP_CACHE_SIZE parameter; combining these parameters in the same parameter file will produce an error. BUFFER_POOL_KEEP is retained for backward compatibility only.

BUFFER_POOL_KEEP lets you save objects in the buffer cache by setting aside a portion of the total number of buffers (the value of the DB_BLOCK_BUFFERS parameter) as a KEEP buffer pool. You can also allocate to the KEEP buffer pool a specified portion of the total number of LRU latches.

BUFFER_POOL_RECYCLE

Parameter type	String
Syntax	BUFFER_POOL_RECYCLE = {integer | (BUFFERS:integer, LRU_LATCHES:integer)} where integer is the number of buffers and, optionally, the number of LRU latches.
Default value	There is no default value.
Parameter class	Static

Note: This parameter is deprecated in favor of the DB_RECYCLE_CACHE_SIZE parameter. Oracle recommends that you use DB_RECYCLE_CACHE_SIZE instead. Also, BUFFER_POOL_RECYCLE cannot be combined with the new dynamic DB_RECYCLE_CACHE_SIZE parameter; combining these parameters in the same parameter file will produce an error. BUFFER_POOL_RECYCLE is retained for backward compatibility only.

BUFFER_POOL_RECYCLE lets you limit the size of objects in the buffer cache by setting aside a portion of the total number of buffers (the value of the DB_BLOCK_BUFFERS parameter) as a RECYCLE buffer pool. You can also allocate to the RECYCLE buffer pool a specified portion of the total number of LRU latches.

CIRCUITS

Parameter type	Integer
Default value	Derived: If you are using shared server architecture, then the value of SESSIONS If you are not using the shared server architecture, then the value is 0
Parameter class	Static

CIRCUITS specifies the total number of virtual circuits that are available for inbound and outbound network sessions. It is one of several parameters that contribute to the total SGA requirements of an instance.

CLUSTER_DATABASE

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false
Oracle9i Real Application Clusters	Multiple instances must have the same value.

CLUSTER_DATABASE is an Oracle9i Real Application Clusters parameter that specifies whether or not Oracle9i Real Application Clusters is enabled.

CLUSTER_DATABASE_INSTANCES

Parameter type	Integer
Default value	1
Parameter class	Static
Range of values	Any nonzero value

CLUSTER_DATABASE_INSTANCES is an Oracle9i Real Application Clusters parameter that specifies the number of instances that are configured as part of your cluster database. You must set this parameter for every instance. Normally you should set this parameter to the number of instances in your Oracle9i Real Application Clusters environment. A proper setting for this parameter can improve memory use.

CLUSTER_INTERCONNECTS

Parameter type	String
Syntax	CLUSTER_INTERCONNECTS = ifn [: ifn ... ]
Default value	There is no default value.
Parameter class	Static
Range of values	One or more IP addresses, separated by colons

CLUSTER_INTERCONNECTS provides Oracle with information about additional cluster interconnects available for use in Oracle9i Real Application Clusters environments.

COMMIT_POINT_STRENGTH

Parameter type	Integer
Default value	1
Parameter class	Static
Range of values	0 to 255

COMMIT_POINT_STRENGTH is relevant only in distributed database systems. It 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.

COMPATIBLE

Parameter type	String
Syntax	COMPATIBLE = release_number
Default value	8.1.0
Parameter class	Static
Range of values	Default release to current release
Oracle9i Real Application Clusters	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 helpful if it becomes necessary to revert to the earlier release.

CONTROL_FILE_RECORD_KEEP_TIME

Parameter type	Integer
Default value	7 (days)
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 to 365 (days)

CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file 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 this parameter is set to 0, then reusable sections never expand, and records are reused as needed.

CONTROL_FILES

Parameter type	String
Syntax	CONTROL_FILES = filename [, filename [...] ] Note: The control file name can be an OMF (Oracle Managed Files) name. This occurs when the control file is re-created using the CREATE CONTROLFILE REUSE statement.
Default value	Operating system-dependent
Parameter class	Static
Range of values	1 to 8 filenames
Oracle9i Real Application Clusters	Multiple instances must have the same value.

Every database has a control file, which contains entries that describe the structure of the database (such as its name, the timestamp of its creation, and the names and locations of its datafiles and redo files). CONTROL_FILES specifies one or more names of control files, separated by commas.

CORE_DUMP_DEST

Parameter type	String
Syntax	CORE_DUMP_DEST = directory
Default value	ORACLE_HOME/DBS
Parameter class	Dynamic: ALTER SYSTEM

CORE_DUMP_DEST is primarily a UNIX parameter and may not be supported on your platform. It specifies the directory where Oracle dumps core files.

CPU_COUNT

Parameter type	Integer
Default value	Set automatically by Oracle
Parameter class	Static
Range of values	0 to unlimited

Caution: 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.

CPU_COUNT specifies the number of CPUs available to Oracle. On single-CPU computers, the value of CPU_COUNT is 1.

CREATE_BITMAP_AREA_SIZE

Parameter type	Integer
Default value	8388608 (8 MB)
Parameter class	Static
Range of values	Operating system-dependent

Note: Oracle does not recommend using the CREATE_BITMAP_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. CREATE_BITMAP_AREA_SIZE is retained for backward compatibility.

CREATE_BITMAP_AREA_SIZE is relevant only for systems containing bitmap indexes. It specifies the amount of memory (in bytes) allocated for bitmap creation. The default value is 8 MB. A larger value may speed up index creation.

CREATE_STORED_OUTLINES

Syntax:

CREATE_STORED_OUTLINES = {TRUE | FALSE | category_name} [NOOVERRIDE] 

The CREATE_STORED_OUTLINES parameter determines whether Oracle should automatically create and store an outline for each query submitted on the system. CREATE_STORED_OUTLINES is not an initialization parameter.

• TRUE enables automatic outline creation for subsequent queries in the system. These outlines receive a unique system-generated name and are stored in the DEFAULT category. If a particular query already has an outline defined for it in the DEFAULT category, that outline will remain and a new outline will not be created.

• FALSE disables automatic outline creation for the system. This is the default.

• category_name has the same behavior as TRUE except that any outline created in the system is stored in the category_name category.

• NOOVERRIDE specifies that this system setting will not override the setting for any session in which this parameter was explicitly set. If you do not specify NOOVERRIDE, this setting takes effect in all sessions.

CURSOR_SHARING

Parameter type	String
Syntax	CURSOR_SHARING = {SIMILAR | EXACT | FORCE}
Default value	EXACT
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

CURSOR_SPACE_FOR_TIME

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

CURSOR_SPACE_FOR_TIME lets you use more space for cursors in order to save time. It affects both the shared SQL area and the client's private SQL area.

DB_nK_CACHE_SIZE

Parameter type	Big integer
Syntax	DB_nK_CACHE_SIZE = integer [K | M | G]
Default value	0M (additional block size caches are not configured by default)
Parameter class	Dynamic: ALTER SYSTEM
Range of values	n = 2, 4, 8, 16, 32

DB_nK_CACHE_SIZE specifies the size of the cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=4096, it is illegal to specify the parameter DB_4K_CACHE_SIZE (because the size for the 4 KB block cache is already specified by DB_CACHE_SIZE).

DB_BLOCK_BUFFERS

Parameter type	Integer
Default value	Derived: 48 MB / DB_BLOCK_SIZE
Parameter class	Static
Range of values	50 to an operating system-specific maximum
Oracle9i Real Application Clusters	Multiple instances can have different values, and you can change the values as needed.

Note: This parameter is deprecated in favor of the DB_CACHE_SIZE parameter. Oracle recommends that you use DB_CACHE_SIZE instead. Also, DB_BLOCK_BUFFERS cannot be combined with the new dynamic DB_CACHE_SIZE parameter; combining these parameters in the same parameter file will produce an error. DB_BLOCK_BUFFERS is retained for backward compatibility.

DB_BLOCK_BUFFERS specifies the number of database buffers in the buffer cache. It is one of several parameters that contribute to the total memory requirements of the SGA of an instance.

DB_BLOCK_CHECKING

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	true | false

DB_BLOCK_CHECKING controls whether Oracle performs block checking for data blocks. When this parameter is set to true, Oracle performs block checking for all data blocks. When it is set to false, Oracle does not perform block checking for blocks in the user tablespaces. However, block checking for the SYSTEM tablespace is always turned on.

DB_BLOCK_CHECKSUM

Parameter type	Boolean
Default value	true
Parameter class	Dynamic: ALTER SYSTEM
Range of values	true | false

DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read-only if this parameter is true and the last write of the block stored a checksum. In addition, Oracle gives every log block a checksum before writing it to the current log.

DB_BLOCK_SIZE

Parameter type	Integer
Default value	2048
Parameter class	Static
Range of values	2048 to 32768, but your operating system may have a narrower range
Oracle9i Real Application Clusters	You must set this parameter for every instance, and multiple instances must have the same value.

Caution: Set this parameter at the time of database creation. Do not alter it afterward.

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 the time you create the database determines the size of the blocks. The value must remain set to its initial value.

DB_CACHE_ADVICE

Parameter type	String
Syntax	DB_CACHE_ADVICE = {ON | READY | OFF}
Default value	OFF
Parameter class	Dynamic: ALTER SYSTEM

DB_CACHE_ADVICE enables and disables statistics gathering used for predicting behavior with different cache sizes through the V$DB_CACHE_ADVICE performance view.

DB_CACHE_SIZE

Parameter type	Big integer
Syntax	DB_CACHE_SIZE = integer [K | M | G]
Default value	48M
Parameter class	Dynamic: ALTER SYSTEM

DB_CACHE_SIZE specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE parameter).

DB_CREATE_FILE_DEST

Parameter type	String
Syntax	DB_CREATE_FILE_DEST = directory
Default value	There is no default value.
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM

DB_CREATE_FILE_DEST sets the default location for datafile, control file, and online log creation.

DB_CREATE_ONLINE_LOG_DEST_n

Parameter type	String
Syntax	DB_CREATE_ONLINE_LOG_DEST_n = directory (n>=1, n<=5)
Default value	There is no default value.
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM

DB_CREATE_ONLINE_LOG_DEST_n sets the default locations for online log and control file creation.

DB_DOMAIN

Parameter type	String
Syntax	DB_DOMAIN = domain_name
Default value	There is no default value.
Parameter class	Static
Range of values	Any legal string of name components, separated by periods and up to 128 characters long (including the periods). This value cannot be NULL.
Oracle9i Real Application Clusters	You must set this parameter for every instance, and multiple instances must have the same value.

In a distributed database system, DB_DOMAIN specifies the logical location of the database within the network structure. You should set this parameter if this database is or ever will be part of a distributed system. The value consists of the extension components of a global database name, consisting of valid identifiers, separated by periods. Oracle Corporation recommends that you specify DB_DOMAIN as a unique string for all databases in a domain.

DB_FILE_MULTIBLOCK_READ_COUNT

Parameter type	Integer
Default value	8
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	Operating system-dependent

DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It 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 such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

DB_FILE_NAME_CONVERT

Parameter type	String
Syntax	DB_FILE_NAME_CONVERT = [(]'string1' , 'string2' , 'string3' , 'string4' , ...[)] where: string1 is the pattern of the primary database filename string2 is the pattern of the standby database filename string3 is the pattern of the primary database filename string4 is the pattern of the standby database filename You can use as many pairs of primary and standby replacement strings as required. You can use single or double quotation marks. The parentheses are optional. Following are example settings that are acceptable: DB_FILE_NAME_CONVERT = ('/dbs/t1/','/dbs/t1/s_ ','dbs/t2/ ','dbs/t2/s_')
Default value	None
Parameter class	Static

DB_FILE_NAME_CONVERT is useful for creating a duplicate database for recovery purposes. It converts the filename of a new datafile on the primary database to a filename on the standby database. If you add a datafile to the primary database, you must add a corresponding file to the standby database. When the standby database is updated, this parameter converts the datafile name on the primary database to the datafile name on the standby database. The file on the standby database must exist and be writable, or the recovery process will halt with an error.

DB_FILES

Parameter type	Integer
Default value	200
Parameter class	Static
Range of values	Minimum: the current actual number of datafiles in the database Maximum: the value that was specified in the MAXDATAFILES clause the last time CREATE DATABASE or CREATE CONTROLFILE was executed
Oracle9i Real Application Clusters	You must set this parameter for every instance, and 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 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 statements.

DB_KEEP_CACHE_SIZE

Parameter type	Big integer
Syntax	DB_KEEP_CACHE_SIZE = integer [K | M | G]
Default value	0M (KEEP cache is not configured by default)
Parameter class	Dynamic: ALTER SYSTEM

DB_KEEP_CACHE_SIZE specifies the number of buffers in the KEEP buffer pool. The size of the buffers in the KEEP buffer pool is the primary block size (the block size defined by the DB_BLOCK_SIZE parameter).

DB_NAME

Parameter type	String
Syntax	DB_NAME = database_name
Default value	There is no default value.
Parameter class	Static
Oracle9i Real Application Clusters	You must set this parameter for every instance. Multiple instances must have the same value, or the same value must be specified in the STARTUP OPEN SQL*Plus statement or the ALTER DATABASE MOUNT SQL statement.

DB_NAME specifies a database identifier of up to 8 characters. If specified, it must correspond to the name specified in the CREATE DATABASE statement. Although the use of DB_NAME is optional, you should generally set it before issuing the CREATE DATABASE statement, and then reference it in that statement.

DB_RECYCLE_CACHE_SIZE

Parameter type	Big integer
Syntax	DB_RECYCLE_CACHE_SIZE = integer [K | M | G]
Default value	0M (RECYCLE cache is not configured by default)
Parameter class	Dynamic: ALTER SYSTEM

DB_RECYCLE_CACHE_SIZE specifies the size of the RECYCLE buffer pool. The size of buffers in the RECYCLE pool is the primary block size defined in DB_BLOCK_SIZE).

DB_WRITER_PROCESSES

Parameter type	Integer
Default value	1
Parameter class	Static
Range of values	1 to 10

DB_WRITER_PROCESSES is useful for systems that modify data heavily. It specifies the initial number of database writer processes for an instance.

DBLINK_ENCRYPT_LOGIN

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

When you attempt to connect to a database using a password, Oracle encrypts the password before sending it to the database. DBLINK_ENCRYPT_LOGIN specifies whether or not attempts to connect to other Oracle servers through database links should use encrypted passwords.

DBWR_IO_SLAVES

Parameter type	Integer
Default value	0
Parameter class	Static
Range of values	0 to operating system-dependent

DBWR_IO_SLAVES is relevant only on systems with only one database writer process (DBW0). It specifies the number of I/O server processes used by the DBW0 process. The DBW0 process and its server processes always write to disk. By default, the value is 0 and I/O server processes are not used.

DISK_ASYNCH_IO

Parameter type	Boolean
Default value	true
Parameter class	Static
Range of values	true | false

DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans). If your platform supports asynchronous I/O to disk, Oracle Corporation recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.

DISPATCHERS

Parameter type	String
Syntax	DISPATCHERS = 'dispatch_clause'
	dispatch_clause::= (PROTOCOL = protocol) | (ADDRESS = address) | (DESCRIPTION = description ) [options_clause]
	options_clause::= (DISPATCHERS = integer | SESSIONS = integer | CONNECTIONS = integer | TICKS = seconds | POOL = {1 | ON | YES | TRUE | BOTH | ({IN | OUT} = ticks) | 0 | OFF | NO | FALSE | ticks} | MULTIPLEX = {1 | ON | YES | TRUE | 0 | OFF | NO | FALSE | BOTH | IN | OUT} | LISTENER = tnsname | SERVICE = service | INDEX = integer)
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM

DISPATCHERS configures dispatcher processes in the shared server architecture. The parsing software supports a name-value syntax to enable the specification of attributes in a position-independent case-insensitive manner. For example:

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

DISTRIBUTED_TRANSACTIONS

Parameter type	Integer
Default value	.25 * TRANSACTIONS
Parameter class	Static
Range of values	0 to the value of the TRANSACTIONS parameter

DISTRIBUTED_TRANSACTIONS is relevant only if you are using Oracle's Distributed Systems feature. It specifies the maximum number of distributed transactions in which this database can participate at one time. The value of this parameter cannot exceed the value of the parameter TRANSACTIONS.

DRS_START

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SYSTEM
Range of values	true | false

DRS_START enables Oracle to determine whether or not the DRMON (Disaster Recovery Monitor) process should be started. DRMON is a non-fatal Oracle background process and exists as long as the instance exists.

EVENT

Parameter type	String
Default value	There is no default value.
Parameter class	Static

EVENT is a parameter used only to debug the system. Do not alter the value of this parameter except under the supervision of Oracle Corporation Worldwide Support staff.

FAL_CLIENT

Parameter type	String
Syntax	FAL_CLIENT = string
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM

FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the FAL_SERVER parameter, to refer to the FAL client. The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).

FAL_SERVER

Parameter type	String
Syntax	FAL_SERVER = string
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM

FAL_SERVER specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.

FAST_START_IO_TARGET

Parameter type	Integer
Default value	All the buffers in the cache
Parameter class	Dynamic: ALTER SYSTEM
Range of values	1000 to all buffers in the cache. A setting of 0 disables limiting recovery I/Os.
Oracle9i Real Application Clusters	Multiple instances can have different values, and you can change the values at runtime.

Note: This parameter is deprecated in favor of the FAST_START_MTTR_TARGET parameter. Oracle recommends that you use FAST_START_MTTR_TARGET instead. FAST_START_IO_TARGETis retained for backward compatibility only.

FAST_START_IO_TARGET (available only with the Oracle Enterprise Edition) specifies the number of I/Os that should be needed during crash or instance recovery.

FAST_START_MTTR_TARGET

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 to 3600 seconds
Oracle9i Real Application Clusters	Multiple instances can have different values, and you can change the values at runtime.

FAST_START_MTTR_TARGET enables you to specify the number of seconds the database takes to perform crash recovery of a single instance. When specified, FAST_START_MTTR_TARGET

• Is overridden by FAST_START_IO_TARGET

• Is overridden by LOG_CHECKPOINT_INTERVAL

FAST_START_PARALLEL_ROLLBACK

Parameter type	String
Syntax	FAST_START_PARALLEL_ROLLBACK = {HI | LO | FALSE}
Default value	LOW
Parameter class	Dynamic: ALTER SYSTEM

FAST_START_PARALLEL_ROLLBACK determines the maximum number of processes that can exist for performing parallel rollback. This parameter is useful on systems in which some or all of the transactions are long running.

FIXED_DATE

Parameter type	String
Syntax	FIXED_DATE = YYYY-MM-DD-HH24:MI:SS (or the default Oracle date format)
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM

FIXED_DATE enables you to set a constant date that SYSDATE will always return instead of the current date. This parameter is useful primarily for testing. The value can be in the format shown above or in the default Oracle date format, without a time.

GC_FILES_TO_LOCKS

Parameter type	String
Syntax	GC_FILES_TO_LOCKS = '{file_list=lock_count[!blocks][EACH][:...]}' Spaces are not allowed within the quotation marks.
Default value	There is no default value.
Parameter class	Static
Oracle9i Real Application Clusters	You must set this parameter for every instance, and multiple instances must have identical values. To change the value, you must shut down all instances in the cluster, change the value for each instance, and then start up each instance.

Note: Setting this parameter to any value other than the default will disable Cache Fusion processing in Oracle9i Real Application Clusters.

GC_FILES_TO_LOCKS is an Oracle9i Real Application Clusters parameter that has no effect on an instance running in exclusive mode. It controls the mapping of pre-release 9.0.1 parallel cache management (PCM) locks to datafiles.

GLOBAL_CONTEXT_POOL_SIZE

Parameter type	String
Default value	1MB
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any integer value in MB

GLOBAL_CONTEXT_POOL_SIZE specifies the amount of memory to allocate in the SGA for storing and managing global application context.

GLOBAL_NAMES

Parameter type	Boolean
Default value	true
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
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. If you use or plan to use distributed processing, Oracle Corporation recommends that you set this parameter to true to ensure the use of consistent naming conventions for databases and links in a networked environment.

HASH_AREA_SIZE

Parameter type	Integer
Default value	Derived: 2 * SORT_AREA_SIZE
Parameter class	Dynamic: ALTER SESSION
Range of values	0 to operating system-dependent

Note: Oracle does not recommend using the HASH_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. HASH_AREA_SIZE is retained for backward compatibility.

HASH_AREA_SIZE is relevant to parallel execution operations and to the query portion of DML or DDL statements. It specifies the maximum amount of memory, in bytes, to be used for hash joins.

HASH_JOIN_ENABLED

Parameter type	Boolean
Default value	true
Parameter class	Dynamic: ALTER SESSION
Range of values	true | false

HASH_JOIN_ENABLED specifies whether the optimizer should consider using a hash join as a join method. If set to false, then hashing is not available as a join method. If set to true, then the optimizer compares the cost of a hash join with other types of joins, and chooses hashing if it gives the best cost. Oracle Corporation recommends that you set this parameter to true for all data warehousing applications.

HI_SHARED_MEMORY_ADDRESS

Parameter type	Integer
Default value	0
Parameter class	Static

HI_SHARED_MEMORY_ADDRESS specifies the starting address at runtime of the system global area (SGA). It is ignored on platforms that specify the SGA's starting address at linktime.

HS_AUTOREGISTER

Parameter type	Boolean
Default value	true
Parameter class	Dynamic: ALTER SYSTEM
Range of values	true | false

HS_AUTOREGISTER enables or disables automatic self-registration of Heterogeneous Services (HS) agents. When enabled, information is uploaded into the server's data dictionary to describe a previously unknown agent class or a new agent version.

IFILE

Parameter type	File
Syntax	IFILE = parameter_file_name
Default value	There is no default value.
Parameter class	Static
Range of values	Valid parameter filenames
Oracle9i Real Application Clusters	Multiple instances can have different values.

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

IFILE = COMMON.ORA

INSTANCE_GROUPS

Parameter type	String
Syntax	INSTANCE_GROUPS = group_name [, group_name ... ]
Default value	There is no default value.
Parameter class	Static
Range of values	One or more instance group names, separated by commas
Oracle9i Real Application Clusters	Multiple instances can have different values.

INSTANCE_GROUPS is an Oracle9i Real Application Clusters parameter that you can specify only in parallel mode. Used in conjunction with the PARALLEL_INSTANCE_GROUP parameter, it lets you restrict parallel query operations to a limited number of instances.

INSTANCE_NAME

Parameter type	String
Syntax	INSTANCE_NAME = instance_id
Default value	The instance's SID Note: The SID identifies the instance's shared memory on a host, but may not uniquely distinguish this instance from other instances.
Parameter class	Static
Range of values	Any alphanumeric characters

In an Oracle9i Real Application Clusters environment, multiple instances can be associated with a single database service. Clients can override Oracle's connection load balancing by specifying a particular instance by which to connect to the database. INSTANCE_NAME specifies the unique name of this instance.

INSTANCE_NUMBER

Parameter type	Integer
Default value	Lowest available number; derived from instance start up order and INSTANCE_NUMBER value of other instances. If not configured for Oracle9i Real Application Clusters, then 0.
Parameter class	Static
Range of values	1 to maximum number of instances specified when the database was created
Oracle9i Real Application Clusters	You must set this parameter for every instance, and all instances must have different values.

INSTANCE_NUMBER is an Oracle9i Real Application Clusters parameter that can be specified in parallel mode or exclusive mode. It specifies a unique number that maps the instance to one free list group for each database object created with storage parameter FREELIST GROUPS.

JAVA_MAX_SESSIONSPACE_SIZE

Parameter type	Integer
Default value	0
Parameter class	Static
Range of values	0 to 4 GB

Java session space is the memory that holds Java state from one database call to another. JAVA_MAX_SESSIONSPACE_SIZE specifies (in bytes) the maximum amount of session space made available to a Java program executing in the server. When a user's session-duration Java state attempts to exceed this amount, the Java virtual machine kills the session with an out-of-memory failure.

JAVA_POOL_SIZE

Parameter type	String
Default value	20000 bytes
Parameter class	Static
Range of values	1000000 to 1000000000 bytes

JAVA_POOL_SIZE specifies the size (in bytes) of the Java pool, from which the Java memory manager allocates most Java state during runtime execution. This memory includes the shared in-memory representation of Java method and class definitions, as well as the Java objects that are migrated to the Java session space at end-of-call.

JAVA_SOFT_SESSIONSPACE_LIMIT

Parameter type	Integer
Default value	0
Parameter class	Static
Range of values	0 to 4 GB

Java session space is the memory that holds Java state from one database call to another. JAVA_SOFT_SESSIONSPACE_LIMIT specifies (in bytes) a soft limit on Java memory usage in a session, as a means to warn you if a user's session-duration Java state is using too much memory. When a user's session-duration Java state exceeds this size, Oracle generates a warning that goes into the trace files.

JOB_QUEUE_PROCESSES

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 to 1000
Oracle9i Real Application Clusters	Multiple instances can have different values.

JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB utility.

LARGE_POOL_SIZE

Parameter type	String
Syntax	LARGE_POOL_SIZE = integer [K | M]
Default value	0 if both of the following are true: The pool is not required by parallel execution DBWR_IO_SLAVES is not set Otherwise, derived from the values of PARALLEL_MAX_SERVERS, PARALLEL_THREADS_PER_CPU, CLUSTER_DATABASE_INSTANCES, DISPATCHERS, and DBWR_IO_SLAVES.
Parameter class	Static
Range of values	600 KB to at least 2 GB (actual maximum is operating system-specific)

LARGE_POOL_SIZE lets you specify the size (in bytes) of the large pool allocation heap. The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers. (Parallel execution allocates buffers out of the large pool only when PARALLEL_AUTOMATIC_TUNING is set to true.)

LICENSE_MAX_SESSIONS

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 to number of session licenses
Oracle9i Real Application Clusters	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.

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

LICENSE_MAX_USERS

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 to number of user licenses
Oracle9i Real Application Clusters	Multiple instances should have the same values. If different instances specify different values for this parameter, the value of the first instance to mount the database takes precedence.

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.

Restriction: You cannot reduce the limit on users below the current number of users created for the database.

LICENSE_SESSIONS_WARNING

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 to value of LICENSE_MAX_SESSIONS parameter
Oracle9i Real Application Clusters	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.

LOCAL_LISTENER

Parameter type	String
Syntax	LOCAL_LISTENER = network_name
Default value	(ADDRESS = (PROTOCOL=TCP)(HOST=)(PORT=1521))
Parameter class	Static

LOCAL_LISTENER specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.

LOCK_NAME_SPACE

Parameter type	String
Syntax	LOCK_NAME_SPACE = namespace
Default value	There is no default value.
Parameter class	Static
Range of values	Up to 8 alphanumeric characters. No special characters allowed.

LOCK_NAME_SPACE specifies the namespace that the distributed lock manager (DLM) uses to generate lock names. Consider setting this parameter if a standby or clone database has the same database name on the same cluster as the primary database.

LOCK_SGA

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space. This parameter is ignored on platforms that do not support it.

LOG_ARCHIVE_DEST

Parameter type	String
Syntax	LOG_ARCHIVE_DEST = filespec
Default value	Null
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any valid path or device name, except raw partitions
Oracle9i Real Application Clusters	Multiple instances can have different values.

Note: For Enterprise Edition users, this parameter has been deprecated in favor of the LOG_ARCHIVE_DEST_n parameters. If Oracle Enterprise Edition is not installed or it is installed, but you have not specified any LOG_ARCHIVE_DEST_n parameters, this parameter is valid.

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 value other than a null string. 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.

LOG_ARCHIVE_DEST_n

Parameter type	String
Syntax	LOG_ARCHIVE_DEST_[1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10] = "null_string" | ((SERVICE=service | LOCATION=location) [AFFIRM | NOAFFIRM] [ALTERNATE=destination | NOALTERNATE] [ARCH | LGWR] [DELAY[=minutes] | NODELAY] [DEPENDENCY=destination | NODEPENDENCY] [MANDATORY | OPTIONAL] [MAX_FAILURE=count | NOMAX_FAILURE] [QUOTA_SIZE=blocks | NOQUOTA_SIZE] [QUOTA_USED=blocks | NOQUOTA_USED] [REGISTER | NOREGISTER] [REGISTER=location_format] [REOPEN[=seconds] | NOREOPEN] [SYNC | ASYNC=blocks]] )
Default value	There is no default value.
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	Valid keyword definitions

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

The LOG_ARCHIVE_DEST_n parameters (where n = 1, 2, 3, ... 10) define up to ten archive log destinations. The parameter integer suffix is defined as the handle displayed by the V$ARCHIVE_DEST dynamic performance view.

LOG_ARCHIVE_DEST_STATE_n

Parameter type	String
Syntax	LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER}
Default value	ENABLE
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM

The LOG_ARCHIVE_DEST_STATE_n parameters (where n = 1, 2, 3, ... 10) specify the availability state of the corresponding destination. The parameter suffix (1 through 10) specifies one of the ten corresponding LOG_ARCHIVE_DEST_n destination parameters.

LOG_ARCHIVE_DUPLEX_DEST

Parameter type	String
Syntax	LOG_ARCHIVE_DUPLEX_DEST = filespec
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Either a null string or any valid path or device name, except raw partitions

Note: If you are using Oracle Enterprise Edition, this parameter is deprecated in favor of the LOG_ARCHIVE_DEST_n parameters. If Oracle Enterprise Edition is not installed or it is installed but you have not specified any LOG_ARCHIVE_DEST_n parameters, 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 (as specified in the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter).

LOG_ARCHIVE_FORMAT

Parameter type	String
Syntax	LOG_ARCHIVE_FORMAT = filename
Default value	Operating system-dependent
Parameter class	Static
Range of values	Any string that resolves to a valid filename
Oracle9i Real Application Clusters	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.

LOG_ARCHIVE_MAX_PROCESSES

Parameter type	Integer
Default value	1
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any integer from 1 to 10

LOG_ARCHIVE_MAX_PROCESSES specifies the number of archiver background processes (ARC0 through ARC9) Oracle initially invokes.

LOG_ARCHIVE_MIN_SUCCEED_DEST

Parameter type	Integer
Default value	1
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	1 to 10 if you are using LOG_ARCHIVE_DEST_n 1 or 2 if you are using 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.

LOG_ARCHIVE_START

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false
Oracle9i Real Application Clusters	Multiple instances can have different values.

LOG_ARCHIVE_START is applicable only when you use the redo log in ARCHIVELOG mode. It indicates whether archiving should be automatic or manual when the instance starts up.

LOG_ARCHIVE_TRACE

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0, 1, 2, 4, 8, 16, 32, 64, 128
Oracle9i Real Application Clusters	Multiple instances can have different values.

LOG_ARCHIVE_TRACE controls output generated by the archivelog process.

LOG_BUFFER

Parameter type	Integer
Default value	Maximum is 512 KB or 128 KB * CPU_COUNT, whichever is greater.
Parameter class	Static
Range of values	Operating system-dependent

LOG_BUFFER specifies the amount of memory, in bytes, that Oracle uses 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.

LOG_CHECKPOINT_INTERVAL

Parameter type	Integer
Default value	Operating system-dependent
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Unlimited
Oracle9i Real Application Clusters	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 can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks.

LOG_CHECKPOINT_TIMEOUT

Parameter type	Integer
Default value	Oracle Security Server: 900 seconds Oracle Security Server Enterprise Edition: 1800 seconds
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 to unlimited
Oracle9i Real Application Clusters	Multiple instances can have different values.

LOG_CHECKPOINT_TIMEOUT specifies the amount of time, in seconds, that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds.

LOG_CHECKPOINTS_TO_ALERT

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SYSTEM
Range of values	true | false

LOG_CHECKPOINTS_TO_ALERT lets you log your checkpoints to the alert file. Doing so is useful for determining whether checkpoints are occurring at the desired frequency.

LOG_FILE_NAME_CONVERT

Parameter type	String
Syntax	LOG_FILE_NAME_CONVERT = [(]'string1' , 'string2' , 'string3' , 'string4' , ...[)] where: string1 is the pattern of the primary database filename string2 is the pattern of the standby database filename string3 is the pattern of the primary database filename string4 is the pattern of the standby database filename You can use as many pairs of primary and standby replacement strings as required. You can use single or double quotation marks. The parentheses are optional. Following are example settings that are acceptable: LOG_FILE_NAME_CONVERT=('/dbs/t1/','/dbs/t1/s_ ','dbs/t2/ ','dbs/t2/s_')
Default value	None
Parameter class	Static
Range of values	Character strings

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. If you add a log file to the primary database, you must add a corresponding file to the standby database.

LOGMNR_MAX_PERSISTENT_SESSIONS

Parameter type	Integer
Default value	1
Parameter class	Static
Range of values	1 to LICENSE_MAX_SESSIONS

LOGMNR_MAX_PERSISTENT_SESSIONS enables you to specify the maximum number of persistent LogMiner mining sessions (which are LogMiner sessions that are backed up on disk) that are concurrently active when all sessions are mining redo logs generated by standalone instances. This pre-allocates 2*LOGMNR_MAX_PERSISTENT_SESSIONS MB of contiguous memory in the SGA for use by LogMiner.

MAX_COMMIT_PROPAGATION_DELAY

Parameter type	Integer
Default value	700
Parameter class	Static
Range of values	0 to 90000
Oracle9i Real Application Clusters	You must set this parameter for every instance, and multiple instances must have identical values.

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

MAX_COMMIT_PROPAGATION_DELAY is an Oracle9i Real Application Clusters parameter. This initialization parameter should not be changed except under a limited set of circumstances specific to the cluster database.

MAX_DISPATCHERS

Parameter type	Integer
Default value	5
Parameter class	Static
Range of values	5 or the number of dispatchers configured, whichever is greater

MAX_DISPATCHERS specifies the maximum number of dispatcher processes allowed to be running simultaneously. The default value applies only if dispatchers have been configured for the system.

MAX_DUMP_FILE_SIZE

Parameter type	String
Syntax	MAX_DUMP_FILE_SIZE = {integer [K | M] | UNLIMITED}
Default value	UNLIMITED
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	0 to unlimited, or UNLIMITED

MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert file). Change this limit if you are concerned that trace files may use too much space.

MAX_ENABLED_ROLES

Parameter type	Integer
Default value	20
Parameter class	Static
Range of values	0 to 148

MAX_ENABLED_ROLES specifies the maximum number of database roles that users can enable, including roles contained within other roles.

MTS Parameters

See "Shared Server Parameters"

MAX_SHARED_SERVERS

Parameter type	Integer
Default value	Derived from SHARED_SERVERS (either 20 or 2*SHARED_SERVERS)
Parameter class	Static
Range of values	Operating system-dependent

MAX_SHARED_SERVERS specifies the maximum number of shared server processes allowed to be running simultaneously. If artificial deadlocks occur too frequently on your system, you should increase the value of MAX_SHARED_SERVERS.

NLS_CALENDAR

Parameter type	String
Syntax	NLS_CALENDAR = "calendar_system"
Default value	None
Parameter class	Dynamic: ALTER SESSION
Range of values	Any valid calendar format name

NLS_CALENDAR specifies which calendar system Oracle uses. It can have one of the following values:

• Arabic Hijrah

• English Hijrah

• Gregorian

• Japanese Imperial

• Persian

• ROC Official (Republic of China)

• Thai Buddha

NLS_COMP

Parameter type	String
Syntax	NLS_COMP = {BINARY | ANSI}
Default value	BINARY
Parameter class	Dynamic: ALTER SESSION

Normally, comparisons in the WHERE clause of queries are binary unless you specify the NLSSORT function. By setting this parameter to ANSI, you indicate that comparisons in the WHERE clause of queries should use the linguistic sort specified in the NLS_SORT parameter. You must also define an index on the column for which you want linguistic sorts.

NLS_CURRENCY

Parameter type	String
Syntax	NLS_CURRENCY = currency_symbol
Default value	Derived from NLS_TERRITORY
Parameter class	Dynamic: ALTER SESSION
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.

NLS_DATE_FORMAT

Parameter type	String
Syntax	NLS_DATE_FORMAT = "format"
Default value	Derived from NLS_TERRITORY
Parameter class	Dynamic: ALTER SESSION
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.

NLS_DATE_LANGUAGE

Parameter type	String
Syntax	NLS_DATE_LANGUAGE = language
Default value	Derived from NLS_LANGUAGE
Parameter class	Dynamic: ALTER SESSION
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 (a.m., p.m., AD, BC) returned by the TO_DATE and TO_CHAR functions.

NLS_DUAL_CURRENCY

Parameter type	String
Syntax	NLS_DUAL_CURRENCY = currency_symbol
Default value	Derived from NLS_TERRITORY
Parameter class	Dynamic: ALTER SESSION
Range of values	Any valid format name up to 10 characters

NLS_DUAL_CURRENCY specifies the dual currency symbol (such as "Euro") for the territory. The default is the dual currency symbol defined in the territory of your current language environment.

NLS_ISO_CURRENCY

Parameter type	String
Syntax	NLS_ISO_CURRENCY = territory
Default value	Derived from NLS_TERRITORY
Parameter class	Dynamic : ALTER SESSION
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.

NLS_LANGUAGE

Parameter type	String
Syntax	NLS_LANGUAGE = language
Default value	Operating system-dependent, derived from the NLS_LANG environment variable
Parameter class	Dynamic: ALTER SESSION
Range of values	Any valid language name

NLS_LANGUAGE specifies the default language of the database. This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism. This parameter also determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT.

NLS_LENGTH_SEMANTICS

Parameter type	String
Syntax	NLS_LENGTH_SEMANTICS = string Example: NLS_LENGTH_SEMANTICS = 'CHAR'
Default value	BYTE
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	BYTE | CHAR

NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.

NLS_NCHAR_CONV_EXCP

Parameter type	String
Syntax	NLS_NCHAR_CONV_EXCP = {TRUE | FALSE}
Default value	FALSE
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM

NLS_NCHAR_CONV_EXCP determines whether data loss during an implicit or explicit character type conversion will report an error.

NLS_NUMERIC_CHARACTERS

Parameter type	String
Syntax	NLS_NUMERIC_CHARACTERS = "decimal_character group_separator"
Default value	Derived from NLS_TERRITORY
Parameter class	Dynamic: ALTER SESSION

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

NLS_SORT

Parameter type	String
Syntax	NLS_SORT = {BINARY | linguistic_definition}
Default value	Derived from NLS_LANGUAGE
Parameter class	Dynamic: ALTER SESSION
Range of values	BINARY or any valid linguistic definition name

NLS_SORT specifies the collating sequence for ORDER BY queries.

NLS_TERRITORY

Parameter type	String
Syntax	NLS_TERRITORY = territory
Default value	Operating system-dependent
Parameter class	Dynamic: ALTER SESSION
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.

NLS_TIMESTAMP_FORMAT

Parameter type	String
Syntax	NLS_TIMESTAMP_FORMAT = "format"
Default value	Derived from NLS_TERRITORY
Parameter class	Dynamic: ALTER SESSION
Range of values	Any valid datetime format mask

NLS_TIMESTAMP_FORMAT defines the default timestamp format to use with the TO_CHAR and TO_TIMESTAMP functions.

NLS_TIMESTAMP_TZ_FORMAT

Parameter type	String
Syntax	NLS_TIMESTAMP_TZ_FORMAT = "format"
Default value	Derived from NLS_TERRITORY
Parameter class	Dynamic: ALTER SESSION
Range of values	Any valid datetime format mask

NLS_TIMESTAMP_TZ_FORMAT defines the default timestamp with time zone format to use with the TO_CHAR and TO_TIMESTAMP_TZfunctions.

O7_DICTIONARY_ACCESSIBILITY

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

O7_DICTIONARY_ACCESSIBILITY is intended for use when you migrate from Oracle7 to Oracle Security Server. It controls restrictions on SYSTEM privileges. If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in "any schema" do not allow access to objects in SYS schema.

OBJECT_CACHE_MAX_SIZE_PERCENT

Parameter type	Integer
Default value	10
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM ... DEFERRED
Range of values	0 to operating system-dependent maximum

The object cache is a memory block on the client that allows applications to store entire objects and to navigate among them without round trips to the server. 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
Default value	102400 (100K)
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM ... DEFERRED
Range of values	10 KB to operating system-dependent maximum

The object cache is a memory block on the client that allows applications to store entire objects and to navigate among them without round trips to the server. OBJECT_CACHE_OPTIMAL_SIZE specifies (in bytes) the size to which the session object cache is reduced when the size of the cache exceeds the maximum size.

OPEN_CURSORS

Parameter type	Integer
Default value	50
Parameter class	Static
Range of values	1 to 4294967295 (4 GB -1)

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user.

OPEN_LINKS

Parameter type	Integer
Default value	4
Parameter class	Static
Range of values	0 to 255

OPEN_LINKS specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.

OPEN_LINKS_PER_INSTANCE

Parameter type	Integer
Default value	4
Parameter class	Static
Range of values	0 to 4294967295 (4 GB -1)
Oracle9i Real Application Clusters	Multiple instances can have different values.

OPEN_LINKS_PER_INSTANCE specifies the maximum number of migratable open connections globally for each database instance. 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 who created the connection is the same as the user who owns the transaction.

OPTIMIZER_FEATURES_ENABLE

Parameter type	String
Syntax	OPTIMIZER_FEATURES_ENABLE = release_number
Default value	9.0.0
Parameter class	Static
Range of values	8.0.0, 8.0.3, 8.0.4, 8.0.5, 8.0.6, 8.1.0, 8.1.3, 8.1.4, 8.1.5, 8.1.6, 8.1.7,9.0.0

OPTIMIZER_FEATURES_ENABLE lets you change the behavior of the Oracle optimizer based on a release number. For example, if you upgrade your database from release 8.0.5 to release 8.1.5, but you want to keep the release 8.0.5 optimizer behavior, you can do so by setting this parameter to 8.0.5. At another time, you can try the new enhancements introduced up to release 8.1.5 by setting the parameter to 8.1.5.

OPTIMIZER_INDEX_CACHING

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SESSION
Range of values	0 to 100

OPTIMIZER_INDEX_CACHING lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.

OPTIMIZER_INDEX_COST_ADJ

Parameter type	Integer
Default value	100
Parameter class	Dynamic: ALTER SESSION
Range of values	1 to 10000

OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly--that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.

OPTIMIZER_MAX_PERMUTATIONS

Parameter type	Integer
Default value	80000
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	4 to 232 (~4.3 billion)

OPTIMIZER_MAX_PERMUTATIONS restricts the number of permutations of the tables the optimizer will consider in queries with joins. Such a restriction ensures that the parse time for the query stays within acceptable limits. However, a slight risk exists that the optimizer will overlook a good plan it would otherwise have found.

OPTIMIZER_MODE

Parameter type	String
Syntax	OPTIMIZER_MODE = {first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule}
Default value	choose
Parameter class	Dynamic: ALTER SESSION

OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for the instance.

ORACLE_TRACE_COLLECTION_NAME

Parameter type	String
Syntax	ORACLE_TRACE_COLLECTION_NAME = collection_name
Default value	There is no default value.
Parameter class	Static
Range of values	Valid collection name up to 16 characters long (except for platforms that enforce 8-character file names)

A collection is data collected for events that occurred while an instrumented product was running. ORACLE_TRACE_COLLECTION_NAME specifies the Oracle Trace collection name for this instance. Oracle also uses this parameter in the output file names (collection definition file .cdf and data collection file .dat). If you set ORACLE_TRACE_ENABLE 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
Syntax	ORACLE_TRACE_COLLECTION_PATH = pathname
Default value	Operating system-specific
Parameter class	Static
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
Default value	5242880
Parameter class	Static
Range of values	0 to 4294967295

ORACLE_TRACE_COLLECTION_SIZE specifies (in bytes) the maximum size of the Oracle Trace collection file (.dat). Once the collection file reaches this maximum, the collection is disabled. A value of 0 means that the file has no size limit.

ORACLE_TRACE_ENABLE

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	true | false

To enable Oracle Trace collections for the server, set ORACLE_TRACE_ENABLE to true. This setting alone does not start an Oracle Trace collection, but it allows Oracle Trace to be used for the server.

ORACLE_TRACE_FACILITY_NAME

Parameter type	String
Syntax	ORACLE_TRACE_FACILITY_NAME = {ORACLED | ORACLEE | ORACLESM | ORACLEC}
Default value	ORACLED
Parameter class	Static

ORACLE_TRACE_FACILITY_NAME specifies the event set that Oracle Trace collects. The value of this parameter, followed by the .fdf extension, is the name of the Oracle Trace product definition file. That file must be located in the directory specified by the ORACLE_TRACE_FACILITY_PATH parameter. The product definition file contains definition information for all the events and data items that can be collected for products that use the Oracle Trace data collection API.

ORACLE_TRACE_FACILITY_PATH

Parameter type	String
Syntax	ORACLE_TRACE_FACILITY_PATH = pathname
Default value	Operating system-specific
Parameter class	Static
Range of values	Full directory pathname

ORACLE_TRACE_FACILITY_PATH specifies the directory pathname where Oracle Trace facility definition files are located. On Solaris, the default path is ORACLE_HOME/otrace/admin/fdf/. On NT, the default path is %OTRACE80%\ADMIN\FDF\.

OS_AUTHENT_PREFIX

Parameter type	String
Syntax	OS_AUTHENT_PREFIX = authentication_prefix
Default value	OPS$
Parameter class	Static

OS_AUTHENT_PREFIX specifies a prefix that Oracle uses to authenticate users attempting to connect to the server. Oracle concatenates the value of this parameter to the beginning of the user's operating system account name and password. When a connection request is attempted, Oracle compares the prefixed username with Oracle usernames in the database.

OS_ROLES

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

OS_ROLES determines whether Oracle or the operating system identifies and manages the roles of each username.

PARALLEL_ADAPTIVE_MULTI_USER

Parameter type	Boolean
Default value	Derived from the value of PARALLEL_AUTOMATIC_TUNING
Parameter class	Dynamic: ALTER SYSTEM
Range of values	true | false

PARALLEL_ADAPTIVE_MULTI_USER, when set to true, enables an adaptive algorithm designed to improve performance in multiuser environments that use parallel execution. The algorithm automatically reduces 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.

PARALLEL_AUTOMATIC_TUNING

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

Note: This parameter applies to parallel execution in exclusive mode as well as in the Oracle9i Real Application Clusters environment.

When PARALLEL_AUTOMATIC_TUNING is set to true, Oracle determines the default values for parameters that control parallel execution. In addition to setting this parameter, you must specify the PARALLEL clause for the target tables in the system. Oracle then tunes all subsequent parallel operations automatically.

PARALLEL_BROADCAST_ENABLED

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SESSION
Range of values	true | false

Note: This parameter refers to parallel execution, not to Oracle9i Real Application Clusters.

PARALLEL_BROADCAST_ENABLED lets you improve performance of hash and merge join operations in which a very large join result set is joined with a very small result set (size being measured in bytes, rather than number of rows).

PARALLEL_EXECUTION_MESSAGE_SIZE

Parameter type	Integer
Default value	Operating system-dependent
Parameter class	Static
Range of values	2148 to 65535 (64 KB - 1)
Oracle9i Real Application Clusters	Multiple instances must have the same value.

PARALLEL_EXECUTION_MESSAGE_SIZE specifies the size of messages for parallel execution (formerly referred to as parallel query, PDML, Parallel Recovery, replication).

PARALLEL_INSTANCE_GROUP

Parameter type	String
Syntax	PARALLEL_INSTANCE_GROUP = group_name
Default value	A group consisting of all instances currently active
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	Any group name specified in the INSTANCE_GROUPS parameter of any active instance
Oracle9i Real Application Clusters	Different instances can have different values.

PARALLEL_INSTANCE_GROUP is an Oracle9i Real Application Clusters parameter that you can specify in parallel mode only. Used in conjunction with the INSTANCE_GROUPS parameter, it lets you restrict parallel query operations to a limited number of instances.

PARALLEL_MAX_SERVERS

Parameter type	Integer
Default value	Derived from the values of CPU_COUNT, PARALLEL_AUTOMATIC_TUNING, and PARALLEL_ADAPTIVE_MULTI_USER
Parameter class	Static
Range of values	0 to 3599
Oracle9i Real Application Clusters	Multiple instances must have the same value.

Note: This parameter applies to parallel execution in exclusive mode as well as in the Oracle9i Real Application Clusters environment.

PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle increases the number of processes from the number created at instance startup up to this value.

PARALLEL_MIN_PERCENT

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SESSION
Range of values	0 to 100
Oracle9i Real Application Clusters	Multiple instances can have different values.

PARALLEL_MIN_PERCENT operates in conjunction with PARALLEL_MAX_SERVERS and PARALLEL_MIN_SERVERS. It lets you specify the minimum percentage of parallel execution processes (of the value of PARALLEL_MAX_SERVERS) required for parallel execution. Setting this parameter ensures that parallel operations will not execute sequentially unless adequate resources are available. The default value of 0 means that no minimum percentage of processes has been set.

PARALLEL_MIN_SERVERS

Parameter type	Integer
Default value	0
Parameter class	Static
Range of values	0 to value of PARALLEL_MAX_SERVERS
Oracle9i Real Application Clusters	Multiple instances can have different values.

Note: This parameter applies to parallel execution in exclusive mode as well as in the Oracle9i Real Application Clusters environment.

PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes Oracle creates when the instance is started.

PARALLEL_THREADS_PER_CPU

Parameter type	Integer
Default value	Operating system-dependent, usually 2
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any nonzero number

Note: This parameter applies to parallel execution in exclusive mode as well as in the Oracle9i Real Application Clusters environment.

PARALLEL_THREADS_PER_CPU specifies the default degree of parallelism for the instance and determines the parallel adaptive and load balancing algorithms. The parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution.

PARTITION_VIEW_ENABLED

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SESSION
Range of values	true | false

Note: Oracle Corporation recommends that you use partitioned tables (available starting with Oracle8) rather than partition views. Partition views are supported for backward compatibility only.

PARTITION_VIEW_ENABLED specifies whether the optimizer uses partition views. If you set this parameter to true, the optimizer prunes (or skips) unnecessary table accesses in a partition view and alters the way it computes statistics on a partition view from statistics on underlying tables.

PGA_AGGREGATE_TARGET

Parameter type	Big integer
Syntax	PGA_AGGREGATE_TARGET = integer [K | M | G]
Default value	0 (automatic memory management is turned OFF by default)
Parameter class	Dynamic: ALTER SYSTEM
Range of values	10 MB to 4000 GB

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance. You must set this parameter to enable the automatic sizing of SQL working areas used by memory-intensive SQL operators such as sort, group-by, hash-join, bitmap merge, and bitmap create.

PLSQL_COMPILER_FLAGS

Parameter type	String
Syntax	PLSQL_COMPILER_FLAGS = { [DEBUG | NON_DEBUG] [INTERPRETED | NORMAL] }
Default value	INTERPRETED, NON_DEBUG
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM

PLSQL_COMPILER_FLAGS is a parameter used by the PL/SQL compiler. It specifies a list of compiler flags as a comma-separated list of strings.

PLSQL_NATIVE_C_COMPILER

Parameter type	String
Syntax	PLSQL_NATIVE_C_COMPILER = pathname
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any valid path name

PLSQL_NATIVE_C_COMPILER specifies the full path name of a C compiler which is used to compile the generated C file into an object file.

PLSQL_NATIVE_LIBRARY_DIR

Parameter type	String
Syntax	PLSQL_NATIVE_LIBRARY_DIR = directory
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any valid directory path

PLSQL_NATIVE_LIBRARY_DIR is a parameter used by the PL/SQL compiler. It specifies the name of a directory where the shared objects produced by the native compiler are stored.

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 to 232-1 (max value represented by 32 bits)

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT specifies the number of subdirectories created by the database administrator in the directory specified by PLSQL_NATIVE_LIBRARY_DIR.

PLSQL_NATIVE_LINKER

Parameter type	String
Syntax	PLSQL_NATIVE_LINKER = pathname
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any valid path name

PLSQL_NATIVE_LINKER specifies the full path name of a linker such as ld in UNIX or GNU ld which is used to link the object file into a shared object or DLL.

PLSQL_NATIVE_MAKE_FILE_NAME

Parameter type	String
Syntax	PLSQL_NATIVE_MAKE_FILE_NAME = pathname
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any valid path name

PLSQL_NATIVE_MAKE_FILE_NAME specifies the full path name of a make file. The make utility (specified by PLSQL_NATIVE_MAKE_UTILITY) uses this make file to generate the shared object or DLL.

PLSQL_NATIVE_MAKE_UTILITY

Parameter type	String
Syntax	PLSQL_NATIVE_MAKE_UTILITY = pathname
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any valid path name

PLSQL_NATIVE_MAKE_UTILITY specifies the full path name of a make utility such as make in UNIX or gmake (GNU make). The make utility is needed to generate the shared object or DLL from the generated C source.

PLSQL_V2_COMPATIBILITY

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	true | false

PL/SQL Version 2 allows some abnormal behavior that Version 8 disallows. If you want to retain that behavior for backward compatibility, set PLSQL_V2_COMPATIBILITY to true. If you set it to false, PL/SQL Version 8 behavior is enforced and Version 2 behavior is not allowed.

PRE_PAGE_SGA

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

PRE_PAGE_SGA determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.

PROCESSES

Parameter type	Integer
Default value	Derived from PARALLEL_MAX_SERVERS
Parameter class	Static
Range of values	6 to operating system-dependent
Oracle9i Real Application Clusters	Multiple instances can have different values.

PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

QUERY_REWRITE_ENABLED

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	true | false
Oracle9i Real Application Clusters	Multiple instances can have different values.

QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally for the database.

QUERY_REWRITE_INTEGRITY

Parameter type	String
Syntax	QUERY_REWRITE_INTEGRITY = {stale_tolerated | trusted | enforced}
Default value	enforced
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Oracle9i Real Application Clusters	Multiple instances can have different values.

QUERY_REWRITE_INTEGRITY determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships.

RDBMS_SERVER_DN

Parameter type	X.500 Distinguished Name
Default value	There is no default value.
Parameter class	Static
Range of values	All X.500 Distinguished Name format values

RDBMS_SERVER_DN specifies the Distinguished Name (DN) of the Oracle server. It is used for retrieving Enterprise Roles from an enterprise directory service.

READ_ONLY_OPEN_DELAYED

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

READ_ONLY_OPEN_DELAYED determines when datafiles in read-only tablespaces are accessed.

RECOVERY_PARALLELISM

Parameter type	Integer
Default value	Operating system-dependent
Parameter class	Static
Range of values	Operating system-dependent, but cannot exceed PARALLEL_MAX_SERVERS

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

REMOTE_ARCHIVE_ENABLE

Parameter type	Boolean
Default value	true
Parameter class	Static
Range of values	true | false

REMOTE_ARCHIVE_ENABLE controls whether the archival of redo logs to remote destinations is permitted. The default is to allow archival to remote destinations.

REMOTE_DEPENDENCIES_MODE

Parameter type	String
Syntax	REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE}
Default value	TIMESTAMP
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM

REMOTE_DEPENDENCIES_MODE specifies how Oracle should handle dependencies upon remote PL/SQL stored procedures.

REMOTE_LISTENER

Parameter type	String
Syntax	REMOTE_LISTENER = network_name
Default value	There is no default value.
Parameter class	Static

REMOTE_LISTENER specifies a network name that resolves to an address or address list of Oracle Net remote listeners (that is, listeners that are not running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.

REMOTE_LOGIN_PASSWORDFILE

Parameter type	String
Syntax	REMOTE_LOGIN_PASSWORDFILE= {NONE | SHARED | EXCLUSIVE}
Default value	NONE
Parameter class	Static
Oracle9i Real Application Clusters	Multiple instances must have the same value.

REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file and how many databases can use the password file.

REMOTE_OS_AUTHENT

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

REMOTE_OS_AUTHENT specifies whether remote clients will be authenticated with the value of the OS_AUTHENT_PREFIX parameter.

REMOTE_OS_ROLES

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

REMOTE_OS_ROLES specifies whether operating system roles are allowed for remote clients. The default value, false, causes Oracle to identify and manage roles for remote clients.

REPLICATION_DEPENDENCY_TRACKING

Parameter type	Boolean
Default value	true
Parameter class	Static
Range of values	true | false

REPLICATION_DEPENDENCY_TRACKING enables or disables dependency tracking for read/write operations to the database. Dependency tracking is essential for propagating changes in a replicated environment in parallel.

RESOURCE_LIMIT

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SYSTEM
Range of values	true | false

RESOURCE_LIMIT determines whether resource limits are enforced in database profiles.

RESOURCE_MANAGER_PLAN

Parameter type	String
Syntax	RESOURCE_MANAGER_PLAN = plan_name
Default value	There is no default value.
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any valid character string

RESOURCE_MANAGER_PLAN specifies the top-level resource plan to use for an instance. The resource manager will load this top-level plan along with all its descendants (subplans, directives, and consumer groups). If you do not specify this parameter, the resource manager is off by default.

ROLLBACK_SEGMENTS

Parameter type	String
Syntax	ROLLBACK_SEGMENTS = (segment_name [, segment_name] ... )
Default value	The instance uses public rollback segments by default if you do not specify this parameter
Parameter class	Static
Range of values	Any rollback segment names listed in DBA_ROLLBACK_SEGS except SYSTEM
Oracle9i Real Application Clusters	Multiple instances must have different values.

ROLLBACK_SEGMENTS allocates one or more rollback segments by name to this instance. If you set this parameter, the 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 as TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT).

ROW_LOCKING

Parameter type	String
Syntax	ROW_LOCKING = {ALWAYS | DEFAULT | INTENT}
Default value	ALWAYS
Parameter class	Static
Oracle9i Real Application Clusters	You must set this parameter for every instance, and multiple instances must have the same value.

ROW_LOCKING specifies whether row locks are acquired during UPDATE operations.

SERIAL_REUSE

Parameter type	String
Syntax	SERIAL_REUSE = {DISABLE | SELECT | DML | PLSQL | ALL}
Default value	DISABLE
Parameter class	Static

SERIAL_REUSE specifies which types of cursors make use of the serial-reusable memory feature. This feature allocates private cursor memory in the SGA so that it can be reused (serially, not concurrently) by sessions executing the same cursor.

SERVICE_NAMES

Parameter type	String
Syntax	SERVICE_NAMES = db_service_name [, db_service_name [,...] ]
Default value	DB_NAME.DB_DOMAIN if defined
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any ASCII string or comma-separated list of string names

SERVICE_NAMES specifies one or more names for the database service to which this instance connects. You can specify multiple service names in order to distinguish among different uses of the same database.

SESSION_CACHED_CURSORS

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SESSION
Range of values	0 to operating system-dependent
Oracle9i Real Application Clusters	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 do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.

SESSION_MAX_OPEN_FILES

Parameter type	Integer
Default value	10
Parameter class	Static
Range of values	1 to either 50 or the value of MAX_OPEN_FILES defined at the operating system level, whichever is less

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

SESSIONS

Parameter type	Integer
Default value	Derived: (1.1 * PROCESSES) + 5
Parameter class	Static
Range of values	1 to 231

SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

SGA_MAX_SIZE

Parameter type	Big integer
Syntax	SGA_MAX_SIZE = integer [K | M | G]
Default value	Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.
Parameter class	Static
Range of values	0 to operating system-dependent

SGA_MAX_SIZE specifies the maximum size of SGA for the lifetime of the instance.

SHADOW_CORE_DUMP

Parameter type	String
Syntax	SHADOW_CORE_DUMP = {PARTIAL | FULL}
Default value	PARTIAL
Parameter class	Static

SHADOW_CORE_DUMP is primarily a UNIX parameter and is not useful on other platforms. It specifies whether Oracle includes the SGA in the core file for foreground (client) processes.

SHARED_MEMORY_ADDRESS

Parameter type	Integer
Default value	0
Parameter class	Static

SHARED_MEMORY_ADDRESS and HI_SHARED_MEMORY_ADDRESS specify the starting address at runtime of the system global area (SGA). This parameter is ignored on the many platforms that specify the SGA's starting address at linktime.

SHARED_POOL_RESERVED_SIZE

Parameter type	Big integer
Syntax	SHARED_POOL_RESERVED_SIZE = integer [K | M | G]
Default value	5% of the value of SHARED_POOL_SIZE
Parameter class	Static
Range of values	Minimum: value of SHARED_POOL_RESERVED_MIN_ALLOC Maximum: one half of the value of SHARED_POOL_SIZE (in bytes)

SHARED_POOL_RESERVED_SIZE specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory. You can use this parameter to avoid performance degradation in the shared pool in situations where pool fragmentation forces Oracle to search for and free chunks of unused pool to satisfy the current request.

SHARED_POOL_SIZE

Parameter type	Big integer
Syntax	SHARED_POOL_SIZE = integer [K | M | G]
Default value	If 64-bit, 64 MB, otherwise 16 MB
Parameter class	Dynamic: ALTER SYSTEM
Range of values	300 KB to operating system-dependent

SHARED_POOL_SIZE specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. If you set PARALLEL_AUTOMATIC_TUNING to false, Oracle also allocates parallel execution message buffers from the shared pool. Larger values improve performance in multi-user systems. Smaller values use less memory.

Shared Server Parameters

Beginning in Oracle9i, the multi-threaded server architecture is called shared server architecture. The old and new names of the MTS parameters are:

When you start your instance, Oracle creates shared server processes and dispatcher processes for the shared server architecture based on the values of the SHARED_SERVERS and DISPATCHERS initialization parameters. You can also set the SHARED_SERVERS and DISPATCHERS parameters with ALTER SYSTEM to perform one of the following operations while the instance is running:

• Create additional shared server processes by increasing the minimum number of shared server processes.

• Terminate existing shared server processes after their current calls finish processing.

• Create more dispatcher processes for a specific protocol, up to a maximum across all protocols specified by the initialization parameter MAX_DISPATCHERS.

• Terminate existing dispatcher processes for a specific protocol after their current user processes disconnect from the instance.

SHARED_SERVERS

Parameter type	Integer
Default value	If you are using shared server architecture, then the value is 1. If you are not using shared server architecture, then the value is 0.
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Operating system-dependent

SHARED_SERVERS specifies the number of server processes that you want to create when an instance is started up. If system load decreases, this minimum number of servers is maintained. Therefore, you should take care not to set SHARED_SERVERS too high at system startup.

SHARED_SERVER_SESSIONS

Parameter type	Integer
Default value	Derived: the lesser of CIRCUITS and SESSIONS - 5
Parameter class	Static
Range of values	0 to SESSIONS - 5

SHARED_SERVER_SESSIONS specifies the total number of shared server architecture user sessions to allow. Setting this parameter enables you to reserve user sessions for dedicated servers.

SORT_AREA_RETAINED_SIZE

Parameter type	Integer
Default value	Derived from SORT_AREA_SIZE
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM ... DEFERRED
Range of values	From the value equivalent of two database blocks to the value of SORT_AREA_SIZE

Note: Oracle does not recommend using the SORT_AREA_RETAINED_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_RETAINED_SIZE is retained for backward compatibility.

SORT_AREA_RETAINED_SIZE specifies (in bytes) the maximum amount of the user global area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer, which Oracle uses 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.

SORT_AREA_SIZE

Parameter type	Integer
Default value	65536
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM ... DEFERRED
Range of values	Minimum: the value equivalent of six database blocks Maximum: operating system-dependent

Note: Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. SORT_AREA_SIZE is retained for backward compatibility.

SORT_AREA_SIZE specifies in bytes the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.

SPFILE

Parameter type	String
Syntax	SPFILE = spfile_name
Default value	ORACLE_HOME/dbs/spfile.ora
Parameter class	Static (auto-resource)
Range of values	Any valid SPFILE
Oracle9i Real Application Clusters	Multiple instances should have the same value.

The value of this parameter is the name of the current server parameter file (SPFILE) in use. This parameter can be defined in a client side PFILE to indicate the name of the server parameter file to use.

SQL92_SECURITY

Parameter type	Boolean
Default value	false
Parameter class	Static
Range of values	true | false

The SQL92 standards specify that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in a WHERE or SET clause. SQL92_SECURITY lets you specify whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements.

SQL_TRACE

Parameter type	Boolean
Default value	false
Parameter class	Static
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. You can change the value using the DBMS_SYSTEM package.

STANDBY_ARCHIVE_DEST

Parameter type	String
Syntax	STANDBY_ARCHIVE_DEST = filespec
Default value	Operating system-specific
Parameter class	Dynamic: ALTER SYSTEM
Range of values	A valid path or device name other than RAW

STANDBY_ARCHIVE_DEST is relevant only for a standby database in managed recovery mode. It specifies the location of archive logs arriving from a primary database. Oracle uses STANDBY_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT to fabricate the fully qualified standby log filenames and stores the filenames in the standby control file.

STANDBY_FILE_MANAGEMENT

Parameter type	String
Syntax	STANDBY_FILE_MANAGEMENT = {MANUAL | AUTO}
Default value	MANUAL
Parameter class	Dynamic: ALTER SYSTEM

STANDBY_FILE_MANAGEMENT enables automatic standby file management. When set to AUTO, file management operations such as adding and deleting files are done automatically by Oracle on the standby database.

STAR_TRANSFORMATION_ENABLED

Parameter type	String
Syntax	STAR_TRANSFORMATION_ENABLED = {TEMP_DISABLE | TRUE | FALSE}
Default value	FALSE
Parameter class	Dynamic: ALTER SESSION

STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries.

TAPE_ASYNCH_IO

Parameter type	Boolean
Default value	true
Parameter class	Static
Range of values	true | false

TAPE_ASYNCH_IO controls whether I/O to sequential devices (for example, backup or restore of Oracle data to or from tape) is asynchronous--that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans. If your platform supports asynchronous I/O to sequential devices, Oracle Corporation recommends that you leave this parameter set to its default. However, if the asynchronous I/O implementation is not stable, you can set TAPE_ASYNCH_IO to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to sequential devices, this parameter has no effect.

THREAD

Parameter type	Integer
Default value	0
Parameter class	Static
Range of values	0 to the maximum number of enabled threads
Oracle9i Real Application Clusters	If specified, multiple instances must have different values.

THREAD is an Oracle9i Real Application Clusters parameter that specifies the number of the redo thread to be used by an instance.

TIMED_OS_STATISTICS

Parameter type	Integer
Default value	0
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	Unlimited

TIMED_OS_STATISTICS specifies the interval (in seconds) at which Oracle collects operating system statistics when a request is made from the client to the server or when a request completes.

TIMED_STATISTICS

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	true | false

TIMED_STATISTICS specifies whether or not statistics related to time are collected.

TRACE_ENABLED

Parameter type	Boolean
Default value	true
Parameter class	Dynamic: ALTER SYSTEM
Range of values	true | false
Oracle9i Real Application Clusters	You must set this parameter for every instance, and multiple instances must have the same value.

TRACE_ENABLED controls tracing of the execution history, or code path, of Oracle. Oracle Support Services uses this information for debugging.

TRACEFILE_IDENTIFIER

Parameter type	String
Syntax	TRACEFILE_IDENTIFIER = "traceid"
Default value	There is no default value.
Parameter class	Dynamic: ALTER SESSION
Range of values	Any characters that can occur as part of a file name on the customer platform

TRACEFILE_IDENTIFIER specifies a custom identifier that becomes part of the Oracle Trace file name. Such a custom identifier is used to identify a trace file simply from its name and without having to open it or view its contents.

TRANSACTION_AUDITING

Parameter type	Boolean
Default value	true
Parameter class	Dynamic: ALTER SYSTEM ... DEFERRED
Range of values	true | false

If TRANSACTION_AUDITING is true, Oracle generates a special redo record that contains the user logon name, username, the session ID, some operating system information, and client information. For each successive transaction, Oracle generates a record that contains only the session ID. These subsequent records link back to the first record, which also contains the session ID.

TRANSACTIONS_PER_ROLLBACK_SEGMENT

Parameter type	Integer
Default value	5
Parameter class	Static
Range of values	1 to operating system-dependent
Oracle9i Real Application Clusters	Multiple instances can have different values.

TRANSACTIONS_PER_ROLLBACK_SEGMENT specifies the number of concurrent transactions you expect each rollback segment to have to handle. 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.

UNDO_MANAGEMENT

Parameter type	String
Syntax	UNDO_MANAGEMENT = {MANUAL | AUTO}
Default value	MANUAL
Parameter class	Static
Oracle9i Real Application Clusters	Multiple instances must have the same value.

UNDO_MANAGEMENT specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.

UNDO_RETENTION

Parameter type	Integer
Default value	900
Parameter class	Dynamic: ALTER SYSTEM
Range of values	0 to 232-1 (max value represented by 32 bits)
Oracle9i Real Application Clusters	Multiple instances must have the same value.

UNDO_RETENTION specifies (in seconds) the amount of committed undo information to retain in the database. You can use UNDO_RETENTION to satisfy queries that require old undo information to rollback changes to produce older images of data blocks. You can set the value at instance startup.

UNDO_SUPPRESS_ERRORS

Parameter type	Boolean
Default value	false
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values	true | false

UNDO_SUPPRESS_ERRORS enables users to suppress errors while executing manual undo management mode operations (for example, ALTER ROLLBACK SEGMENT ONLINE) in automatic undo management mode. Setting this parameter enables users to use the undo tablespace feature before all application programs and scripts are converted to automatic undo management mode. For example, if you have a tool that uses SET TRANSACTION USE ROLLBACK SEGMENT statement, you can add the statement "ALTER SESSION SET UNDO_SUPPRESS_ERRORS = true" to the tool to suppress the ORA-30019 error.

UNDO_TABLESPACE

Parameter type	String
Syntax	UNDO_TABLESPACE = undoname
Default value	The first available undo tablespace in the database.
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Legal name of an existing undo tablespace
Oracle9i Real Application Clusters	Multiple instances can have different values.

UNDO_TABLESPACE specifies the undo tablespace to be used when an instance starts up. If this parameter is specified when the instance is in manual undo management mode, an error will occur and startup will fail.

USE_STORED_OUTLINES

Syntax: USE_STORED_OUTLINES = { TRUE | FALSE | category_name}

The USE_STORED_OUTLINES parameter determines whether the optimizer will use stored public outlines to generate execution plans. USE_STORED_OUTLINES is not an initialization parameter.

• TRUE causes the optimizer to use outlines stored in the DEFAULT category when compiling requests.

• FALSE specifies that the optimizer should not use stored outlines. This is the default.

• category_name causes the optimizer to use outlines stored in the category_name category when compiling requests.

USER_DUMP_DEST

Parameter type	String
Syntax	USER_DUMP_DEST = {pathname | directory}
Default value	Operating system-dependent
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Any valid local path, 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.

UTL_FILE_DIR

Parameter type	String
Syntax	UTL_FILE_DIR = pathname
Default value	There is no default value.
Parameter class	Static
Range of values	Any valid directory path

UTL_FILE_DIR lets you specify one or more directories that Oracle should use for PL/SQL file I/O. If you are specifying multiple directories, you must repeat the UTL_FILE_DIR parameter for each directory on separate lines of the initialization parameter file.

WORKAREA_SIZE_POLICY

Parameter type	String
Syntax	WORKAREA_SIZE_POLICY = {AUTO | MANUAL}
Default value	If PGA_AGGREGATE_TARGET is set, then AUTO If PGA_AGGREGATE_TARGET is not set, then MANUAL
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM

WORKAREA_SIZE_POLICY specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.

Examples

Archiving Redo Logs Manually Examples

The following statement manually archives the redo log file group with the log sequence number 4 in thread number 3:

ALTER SYSTEM ARCHIVE LOG THREAD 3 SEQUENCE 4; 

The following statement manually archives the redo log file group containing the redo log entry with the SCN 9356083:

ALTER SYSTEM ARCHIVE LOG CHANGE 9356083; 

The following statement manually archives the redo log file group containing a member named 'diskl:log6.log' to an archived redo log file in the location 'diska:[arch$]':

ALTER SYSTEM ARCHIVE LOG 
    LOGFILE 'diskl:log6.log' 
    TO 'diska:[arch$]'; 

Enabling Query Rewrite Example

This statement enables query rewrite in all sessions for all materialized views that have not been explicitly disabled:

ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE;

Restricting Session Logons Example

You may want to restrict logons if you are performing application maintenance and you want only application developers with RESTRICTED SESSION system privilege to log on. To restrict logons, issue the following statement:

ALTER SYSTEM
   ENABLE RESTRICTED SESSION; 

You can then terminate any existing sessions using the KILL SESSION clause of the ALTER SYSTEM statement.

After performing maintenance on your application, issue the following statement to allow any user with CREATE SESSION system privilege to log on:

ALTER SYSTEM
   DISABLE RESTRICTED SESSION; 

Clearing the Shared Pool Example

You might want to clear the shared pool before beginning performance analysis. To clear the shared pool, issue the following statement:

ALTER SYSTEM FLUSH SHARED_POOL;

Forcing a Checkpoint Example

The following statement forces a checkpoint:

ALTER SYSTEM CHECKPOINT; 

Enabling Resource Limits Example

This ALTER SYSTEM statement dynamically enables resource limits:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; 

Shared Server Examples

The following statement changes the minimum number of shared server processes to 25:

ALTER SYSTEM SET SHARED_SERVERS = 25; 

If there are currently fewer than 25 shared server processes, Oracle creates more. If there are currently more than 25, Oracle terminates some of them when they are finished processing their current calls if the load could be managed by the remaining 25.

The following statement dynamically changes the number of dispatcher processes for the TCP/IP protocol to 5 and the number of dispatcher processes for the DECNet protocol to 10:

ALTER SYSTEM 
   SET DISPATCHERS = 
      '(INDEX=0)(PROTOCOL=TCP)(DISPATCHERS=5)',
      '(INDEX=1)(PROTOCOL=DECNet)(DISPATCHERS=10)'; 

If there are currently fewer than 5 dispatcher processes for TCP, Oracle creates new ones. If there are currently more than 5, Oracle terminates some of them after the connected users disconnect.

If there are currently fewer than 10 dispatcher processes for DECNet, Oracle creates new ones. If there are currently more than 10, Oracle terminates some of them after the connected users disconnect.

If there are currently existing dispatchers for another protocol, the above statement does not affect the number of dispatchers for that protocol.

Changing Licensing Parameters Examples

The following statement dynamically changes the limit on sessions for your instance to 64 and the warning threshold for sessions on your instance to 54:

ALTER SYSTEM 
   SET LICENSE_MAX_SESSIONS = 64 
   LICENSE_SESSIONS_WARNING = 54; 

If the number of sessions reaches 54, Oracle writes a warning message to the ALERT file for each subsequent session. Also, users with RESTRICTED SESSION system privilege receive warning messages when they begin subsequent sessions.

If the number of sessions reaches 64, only users with RESTRICTED SESSION system privilege can begin new sessions until the number of sessions falls below 64 again.

The following statement dynamically disables the limit for sessions on your instance. After you issue the above statement, Oracle no longer limits the number of sessions on your instance.

ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 0; 

The following statement dynamically changes the limit on the number of users in the database to 200. After you issue the above statement, Oracle prevents the number of users in the database from exceeding 200.

ALTER SYSTEM SET LICENSE_MAX_USERS = 200; 

Forcing a Log Switch Example

You may want to force a log switch to drop or rename the current redo log file group or one of its members, because you cannot drop or rename a file while Oracle is writing to it. The forced log switch affects only your instance's redo log thread. The following statement forces a log switch:

ALTER SYSTEM
   SWITCH LOGFILE; 

Enabling Distributed Recovery Example

The following statement enables distributed recovery:

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

You may want to disable distributed recovery for demonstration or testing purposes.You can disable distributed recovery in both single-process and multiprocess mode with the following statement:

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY; 

When your demonstration or testing are complete, you can then enable distributed recovery again by issuing an ALTER SYSTEM statement with the ENABLE DISTRIBUTED RECOVERY clause.

Killing a Session Example

You may want to kill the session of a user that is holding resources needed by other users. The user receives an error message indicating that the session has been killed. That user can no longer make calls to the database without beginning a new session. Consider this data from the V$SESSION dynamic performance table:

SELECT sid, serial#, username
FROM v$session 

  SID   SERIAL# USERNAME
----- --------- ----------------
    1         1
    2         1
    3         1
    4         1 
    5         1 
    7         1 
    8        28 OPS$BQUIGLEY 
   10       211 OPS$SWIFT 
   11        39 OPS$OBRIEN 
   12        13 SYSTEM  
   13         8 SCOTT 

The following statement kills the session of the user scott using the SID and SERIAL# values from V$SESSION:

ALTER SYSTEM KILL SESSION '13, 8';

Disconnecting a Session Example

The following statement disconnects user scott's session, using the SID and SERIAL# values from V$SESSION:

ALTER SYSTEM DISCONNECT SESSION '13, 8' POST_TRANSACTION;