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

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

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

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.)
Real Application Clusters	You must set this parameter for every instance, and multiple instances must have the same value.

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]
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_SYS_OPERATIONS

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

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

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 = {partial | full}
Default value	partial
Parameter class	Static

BACKGROUND_CORE_DUMP 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

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

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

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

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

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

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_[2 | 4 | 8 | 16 | 32]K_CACHE_SIZE = integer [K | M | G]
Default value	0 (additional block size caches are not configured by default)
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Minimum: the granule size Maximum: operating system-dependent

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

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

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
Real Application Clusters	You must set this parameter for every instance, and multiple instances must have the same value.

DB_BLOCK_SIZE specifies the size (in bytes) of Oracle database blocks. Typical values are 2048 and 4096. The value for DB_BLOCK_SIZE in effect at 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	If STATISTICS_LEVEL is set to TYPICAL or ALL, then ON If STATISTICS_LEVEL is set to BASIC, then OFF
Parameter class	Dynamic: ALTER SYSTEM

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

Parameter type	Big integer
Syntax	DB_CACHE_SIZE = integer [K | M | G]
Default value	48 MB, rounded up to the nearest granule size
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 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

Parameter type	String
Syntax	DB_CREATE_ONLINE_LOG_DEST_[1 | 2 | 3 | 4 | 5] = directory
Default value	There is no default value.
Parameter class	Dynamic: ALTER SESSION, ALTER SYSTEM

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

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.
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 largest among the absolute file numbers of the datafiles in the database Maximum: operating system-dependent
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	0 (KEEP cache is not configured by default)
Parameter class	Dynamic: ALTER SYSTEM
Range of values	Minimum: the granule size Maximum: operating system-dependent

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

Parameter type	String
Syntax	DB_NAME = database_name
Default value	There is no default value.
Parameter class	Static
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_