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.

Syntax

alter_system::=

archive_log_clause::=

end_session_clauses::=

alter_system_set_clause::=

alter_system_reset_clause::=

Semantics

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 on the THREAD clause

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, then 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, then Oracle performs a log switch. If you omit the THREAD parameter, then 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, then 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.

NOSWITCH

Specify NOSWITCH if you want to manually archive the current redo log file group without forcing a log switch. This setting is used primarily with standby databases to prevent data divergence when the primary database shuts down. Divergence implies the possibility of data loss in case of primary database failure.

You can use the NOSWITCH clause only when your instance has the database mounted but not open. If the database is open, then 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, then the specified redo log file group must be in the specified thread.

LOGFILE Clause n

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, then the specified redo log file group must be in the specified thread.

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

Restriction on the LOGFILE clause

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, then 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, then 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, then 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 on the START clause

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, then 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, then 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, then this clause has the same effect described for as KILL SESSION.
• 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, then the IMMEDIATE keyword is ignored.
  • If you do not specify POST_TRANSACTION, or you specify POST_TRANSACTION but the session has no ongoing transactions, then this clause has the same effect as described for KILL SESSION IMMEDIATE.

    See Also: "Disconnecting a Session: Example"

KILL SESSION Clause

The KILL SESSION clause lets you mark a session as terminated, 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, then Oracle waits for this activity to complete, marks the session as terminated, and then returns control to you. If the waiting lasts a minute, then 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 terminated 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 on SUSPEND and RESUME

• 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, then 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, then 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, then 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, then 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, then 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, then 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, then 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, then 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.

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

The ability to change initialization parameter values depends on whether you have started up the database with a traditional parameter file (pfile) or with a server parameter file (spfile). To determine whether you can change the value of a particular parameter, query the ISSYS_MODIFIABLE column of the V$PARAMETER dynamic performance view.

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

You must specify DEFERRED if the value of the ISSYS_MODIFIABLE column of V$PARAMETER for this parameter is DEFERRED. If the value of that column is IMMEDIATE, then the DEFERRED keyword in this clause is optional. If the value of that column is FALSE, then you cannot specify DEFERRED in this ALTER SYSTEM statement.

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), then 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, then BOTH is the default. If a parameter file was used to start up the database, then 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), then Oracle assumes the SID of the current instance.
• If the instance was started up with an spfile (server parameter file), then Oracle assumes SID = '*'.

If you specify an instance other than the current instance, then 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 environment. 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.

Initialization Parameters and ALTER SYSTEM

This section contains an alphabetical listing of all initialization parameters with brief descriptions only. The hyperlinks within the descriptions take you to the full descriptions in Oracle9i Database Reference.

ACTIVE_INSTANCE_COUNT

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

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

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

AUDIT_FILE_DEST specifies the directory where Oracle stores auditing files.

AUDIT_SYS_OPERATIONS

AUDIT_SYS_OPERATIONS enables or disables the auditing of operations issued by user SYS, and users connecting with SYSDBA or SYSOPER privileges. The audit records are written to the operating system's audit trail.

AUDIT_TRAIL

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

BACKGROUND_CORE_DUMP

BACKGROUND_CORE_DUMP specifies whether Oracle includes the SGA in the core file for Oracle background processes.

BACKGROUND_DUMP_DEST

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

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

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

BLANK_TRIMMING specifies the data assignment semantics of character datatypes.

BUFFER_POOL_KEEP

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

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

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

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

CLUSTER_DATABASE_INSTANCES

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

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

COMMIT_POINT_STRENGTH

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

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

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

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

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

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

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, then 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, then this setting takes effect in all sessions.

CURSOR_SHARING

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

CURSOR_SPACE_FOR_TIME

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

DB_nK_CACHE_SIZE (where n = 2, 4, 8, 16, 32) 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, then 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

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

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

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

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

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

DB_CACHE_SIZE

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

DB_CREATE_FILE_DEST sets the default location for Oracle-managed datafiles. This location is also used as the default for Oracle-managed control files and online redo logs if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

DB_CREATE_ONLINE_LOG_DEST_n

DB_CREATE_ONLINE_LOG_DEST_n (where n = 1, 2, 3, ... 5) sets the default location for Oracle-managed control files and online redo logs.

DB_DOMAIN

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

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

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

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

DB_KEEP_CACHE_SIZE specifies the size of 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

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

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

DB_WRITER_PROCESSES

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

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

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.

DG_BROKER_CONFIG_FILEn

DG_BROKER_CONFIG_FILEn (where n = 1, 2) specifies the names for the Data Guard broker configuration files.

DG_BROKER_START

DG_BROKER_START enables Oracle to determine whether or not the DMON (Data Guard broker) process should be started. DMON is a non-fatal Oracle background process and exists as long as the instance exists, whenever this parameter is set to true.

DISK_ASYNCH_IO

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

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_LOCK_TIMEOUT

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

DML_LOCKS

A DML lock is a lock obtained on a table that is undergoing a DML operation (insert, update, delete). DML_LOCKS specifies the maximum number of DML locks--one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.

DRS_START

DRS_START enables Oracle to determine whether or not the DMON (Data Guard broker) process should be started. DMON is a non-fatal Oracle background process and exists as long as the instance exists, whenever this parameter is set to true.

ENQUEUE_RESOURCES

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

EVENT

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

FAL_CLIENT

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

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

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

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

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.

FILE_MAPPING

FILE_MAPPING enables or disables file mapping. The FMON background process will be started to manage the mapping information when file mapping is enabled.

FILESYSTEMIO_OPTIONS

FILESYSTEMIO_OPTIONS specifies I/O operations for file system files.

FIXED_DATE

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

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

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

GLOBAL_NAMES

GLOBAL_NAMES specifies whether a database link is required to have the same name as the database to which it connects.

HASH_AREA_SIZE

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

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

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

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

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

IFILE = COMMON.ORA

INSTANCE_GROUPS

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

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

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

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

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

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

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

LARGE_POOL_SIZE

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

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

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 on LICENSE_MAX_USERS

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

LICENSE_SESSIONS_WARNING

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

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

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

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

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

   { null_string} |
     { LOCATION=path_name | SERVICE=service_name }
     [ { MANDATORY | OPTIONAL } ]
     [ REOPEN[=seconds] | NOREOPEN ]
     [ DELAY[=minutes] | NODELAY ]
     [ REGISTER[=template] | NOREGISTER ]
     [ TEMPLATE=template] | NOTEMPLATE ]
     [ ALTERNATE=destination | NOALTERNATE ]
     [ DEPENDENCY=destination | NODEPENDENCY ]
     [ MAX_FAILURE=count | NOMAX_FAILURE ]
     [ QUOTA_SIZE=blocks | NOQUOTA_SIZE ]
     [ QUOTA_USED=blocks | NOQUOTA_USED ]
     [ ARCH | LGWR ]
     [ SYNC[=PARALLEL|NOPARALLEL] | ASYNC[=blocks] ]
     [ AFFIRM | NOAFFIRM ]
     [ NET_TIMEOUT=seconds | NONET_TIMEOUT ]
   }

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

The LOG_ARCHIVE_DEST_STATE_n parameters (wheren = 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

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

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

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

LOG_ARCHIVE_MIN_SUCCEED_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

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

LOG_ARCHIVE_TRACE controls output generated by the archivelog process.

LOG_BUFFER

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

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

LOG_CHECKPOINT_TIMEOUT specifies (in seconds) the amount of time 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

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

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.

LOG_PARALLELISM

LOG_PARALLELISM specifies the level of concurrency for redo allocation within Oracle.

LOGMNR_MAX_PERSISTENT_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

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

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

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

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

MAX_ROLLBACK_SEGMENTS

MAX_ROLLBACK_SEGMENTS specifies the maximum size of the rollback segment cache in the SGA. The number specified signifies the maximum number of rollback segments that can be kept online (that is, status of ONLINE) simultaneously by one instance.

MTS Parameters

See "Shared Server Parameters".

MAX_SHARED_SERVERS

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

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

Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function. By setting NLS_COMP to ANSI, you indicate that comparisons in the WHERE clause and in PL/SQL blocks 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

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

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

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

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

NLS_ISO_CURRENCY specifies the string to use as the international currency symbol for the C number format element.

NLS_LANGUAGE

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

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

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

NLS_NUMERIC_CHARACTERS

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

NLS_SORT specifies the collating sequence for ORDER BY queries.

NLS_TERRITORY

NLS_TERRITORY specifies the name of the territory whose conventions are to be followed for day and week numbering.

NLS_TIMESTAMP_FORMAT

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

NLS_TIMESTAMP_TZ_FORMAT

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

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

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

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.

OLAP_PAGE_POOL_SIZE

OLAP_PAGE_POOL_SIZE specifies the size (in bytes) of the OLAP pool.

OPEN_CURSORS

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

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

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_DYNAMIC_SAMPLING