Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-01 |
|
SQL Statements: ALTER SESSION to ALTER SYSTEM, 3 of 3
Use the ALTER
SYSTEM
statement to dynamically alter your Oracle instance. The settings stay in effect as long as the database is mounted.
You must have ALTER
SYSTEM
system privilege.
alter_system::=
end_session_clauses::=
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
.
Specify STOP
to disable automatic archiving of redo log file groups. You can disable automatic archiving only for the thread assigned to your instance.
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.
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.
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.
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.
GLOBAL
to perform this synchronization for all instances that have opened the database. This is the default.LOCAL
to perform this synchronization only for the local instance.Your instance should have the database open.
The end_session_clauses
give you several ways to end the current session.
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:
integer1
, specify the value of the SID
column.integer2
, specify the value of the SERIAL#
column.If system parameters are appropriately configured, then application failover will take effect.
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
.IMMEDIATE
setting disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.
POST_TRANSACTION
and the session has ongoing transactions, then the IMMEDIATE
keyword is ignored.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
.
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:
integer1
, specify the value of the SID
column.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.
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.
The DISTRIBUTED
RECOVERY
clause lets you enable or disable distributed recovery. To use this clause, your instance must have the database open.
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
.
Specify DISABLE
to disable distributed recovery.
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.
Specify ENABLE
to allows only users with RESTRICTED
SESSION
system privilege to log on to Oracle. Existing sessions are not terminated.
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.
The FLUSH
SHARED
POOL
clause lets you clear all data from the shared pool in the system global area (SGA). The shared pool stores
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.
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.
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:
The RESUME
clause lets you make the database available once again for queries and I/O.
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.
Specify QUIESCE
RESTRICTED
to put the database in the quiesced state. For all instances with the database open, this clause has the following effect:
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.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.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.
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.
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 'D
xxx
', 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.
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.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.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.
See Also:
Oracle9i Database Concepts and Oracle9i Net Services Administrator's Guide for information on the |
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:
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.
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.
See Also:
Oracle9i Database Reference for information on the |
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
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
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
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.
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.
SID
= '*'
if you want Oracle to change the value of the parameter for all instances.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:
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.
See Also:
Oracle9i Database Reference for information about the |
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
.
Specify the SID
clause to remove a previously specified setting of this parameter for your instance (that is, a previous ALTER
SYSTEM
SET
... SID
= 'sid'
statement). Your instance will assume the value of the parameter as specified in a previous or subsequent ALTER
SYSTEM
SET
... SID
= '*'
statement.
See Also:
Oracle9i Real Application Clusters Deployment and Performance for information on setting parameter values for an individual instance in a Real Application Clusters environment |
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
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.
Parameter type |
Integer |
Default value |
0 |
Parameter class |
Dynamic: |
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
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.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Static |
AUDIT_FILE_DEST
specifies the directory where Oracle stores auditing files.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
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.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Static |
AUDIT_TRAIL
enables or disables the automatic writing of rows to the audit trail.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Static |
BACKGROUND_CORE_DUMP
specifies whether Oracle includes the SGA in the core file for Oracle background processes.
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.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
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.
Parameter type |
Integer |
Default value |
|
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.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
BLANK_TRIMMING
specifies the data assignment semantics of character datatypes.
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
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.
Parameter type |
Integer |
Default value |
Derived: |
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.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
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.
Parameter type |
Integer |
Default value |
|
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.
Parameter type |
String |
Syntax |
|
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.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
|
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
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.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
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.
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.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
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.
Parameter type |
Integer |
Default value |
Set automatically by Oracle |
Parameter class |
Static |
Range of values |
0 to unlimited |
Caution: On most platforms, Oracle automatically sets the value of |
CPU_COUNT
specifies the number of CPUs available to Oracle. On single-CPU computers, the value of CPU_COUNT
is 1.
Parameter type |
Integer |
Default value |
|
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.
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.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
CURSOR_SHARING
determines what kind of SQL statements can share the same cursors.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
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_
(where n
K_CACHE_SIZE
n
= 2, 4, 8, 16, 32) specifies the size of the cache for the n
K
buffers. You can set this parameter only when DB_BLOCK_SIZE
has a value other than n
K
. 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
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.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
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.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
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
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
enables or disables statistics gathering used for predicting behavior with different cache sizes through the V$DB_CACHE_ADVICE
performance view.
Parameter type |
Big integer |
Syntax |
|
Default value |
48 MB, rounded up to the nearest granule size |
Parameter class |
Dynamic: |
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).
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
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.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
DB_CREATE_ONLINE_LOG_DEST_
(where n
n
= 1, 2, 3, ... 5) sets the default location for Oracle-managed control files and online redo logs.
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.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
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
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
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
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
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
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).
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
1 to 20 |
DB_WRITER_PROCESSES
is useful for systems that modify data heavily. It specifies the initial number of database writer processes for an instance.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
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.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
0 to operating system-dependent |
DBWR_IO_SLAVES
is relevant only on systems with only one database writer process (DBW0
). It specifies the number of I/O server processes used by the DBW0
process. The DBW0
process and its server processes always write to disk. By default, the value is 0
and I/O server processes are not used.
Parameter type |
String |
Syntax |
|
Default value |
Operating system-dependent |
Parameter class |
Dynamic: |
Range of values |
One filename |
DG_BROKER_CONFIG_FILE
(where n
n
= 1, 2) specifies the names for the Data Guard broker configuration files.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
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
.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
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
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)"
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
1 to unlimited |
DISTRIBUTED_LOCK_TIMEOUT
specifies the amount of time (in seconds) for distributed transactions to wait for locked resources.
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.
Note: You can set this parameter using |
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
Note: This parameter is deprecated in favor of the |
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
.
Parameter type |
Integer |
Default value |
Derived from |
Parameter class |
Static |
Range of values |
10 to unlimited |
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.
Note: You can set this parameter using |
Parameter type |
String |
Default value |
There is no default value. |
Parameter class |
Static |
EVENT
is a parameter used only to debug the system. Do not alter the value of this parameter except under the supervision of Oracle Support Services staff.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
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).
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
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
(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
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
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
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.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
FILE_MAPPING
enables or disables file mapping. The FMON background process will be started to manage the mapping information when file mapping is enabled.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
FILESYSTEMIO_OPTIONS
specifies I/O operations for file system files.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
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.
Note: Setting this parameter to any value other than the default will disable Cache Fusion processing in Oracle9i Real Application Clusters. |
GC_FILES_TO_LOCKS
is an Oracle9i Real Application Clusters parameter that has no effect on an instance running in exclusive mode. It controls the mapping of pre-release 9.0.1 parallel cache management (PCM) locks to datafiles.
Parameter type |
String |
Default value |
|
Parameter class |
Static |
Range of values |
Any integer value in MB |
GLOBAL_CONTEXT_POOL_SIZE
specifies the amount of memory to allocate in the SGA for storing and managing global application context.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
GLOBAL_NAMES
specifies whether a database link is required to have the same name as the database to which it connects.
Parameter type |
Integer |
Default value |
Derived: 2 * |
Parameter class |
Dynamic: |
Range of values |
0 to operating system-dependent |
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.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
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.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
HI_SHARED_MEMORY_ADDRESS
specifies the starting address at runtime of the system global area (SGA). It is ignored on platforms that specify the SGA's starting address at linktime.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
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.
Use IFILE
to embed another parameter file within the current parameter file. For example:
IFILE = COMMON.ORA
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.
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
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
.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
0 to 4 GB |
Java session space is the memory that holds Java state from one database call to another. JAVA_MAX_SESSIONSPACE_SIZE
specifies (in bytes) the maximum amount of session space made available to a Java program executing in the server. When a user's session-duration Java state attempts to exceed this amount, the Java virtual machine kills the session with an out-of-memory failure.
JAVA_POOL_SIZE
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.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
0 to 4 GB |
Java session space is the memory that holds Java state from one database call to another. JAVA_SOFT_SESSIONSPACE_LIMIT
specifies (in bytes) a soft limit on Java memory usage in a session, as a means to warn you if a user's session-duration Java state is using too much memory. When a user's session-duration Java state exceeds this size, Oracle generates a warning that goes into the trace files.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
0 to 1000 |
Real Application Clusters |
Multiple instances can have different values. |
JOB_QUEUE_PROCESSES
specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB
package.
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
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
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
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.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
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
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.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
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
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.
The LOG_ARCHIVE_DEST_
parameters (where n
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.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
The LOG_ARCHIVE_DEST_STATE_
parameters (wheren
n
= 1, 2, 3, ... 10) specify the availability state of the corresponding destination. The parameter suffix (1 through 10) specifies one of the ten corresponding LOG_ARCHIVE_DEST_
n
destination parameters.
LOG_ARCHIVE_DUPLEX_DEST
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
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.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
Any integer from 1 to 10 |
LOG_ARCHIVE_MAX_PROCESSES
specifies the number of archiver background processes (ARC0 through ARC9) Oracle initially invokes.
LOG_ARCHIVE_MIN_SUCCEED_DEST
defines the minimum number of destinations that must succeed in order for the online logfile to be available for reuse.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
Real Application Clusters |
Multiple instances can have different values. |
LOG_ARCHIVE_START
is applicable only when you use the redo log in ARCHIVELOG
mode. It indicates whether archiving should be automatic or manual when the instance starts up.
Parameter type |
Integer |
Default value |
0 |
Parameter class |
Dynamic: |
Range of values |
0, 1, 2, 4, 8, 16, 32, 64, 128 |
Real Application Clusters |
Multiple instances can have different values. |
LOG_ARCHIVE_TRACE
controls output generated by the archivelog process.
Parameter type |
Integer |
Default value |
512 KB or 128 KB * |
Parameter class |
Static |
Range of values |
Operating system-dependent |
LOG_BUFFER
specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
Unlimited |
Real Application Clusters |
Multiple instances can have different values. |
LOG_CHECKPOINT_INTERVAL
specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
Real Application Clusters |
Multiple instances can have different values. |
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.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
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
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.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
|
LOG_PARALLELISM
specifies the level of concurrency for redo allocation within Oracle.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
|
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.
Caution: Change this parameter only when it is absolutely necessary to see the most current version of the database when performing a query. |
MAX_COMMIT_PROPAGATION_DELAY
is an Oracle9i Real Application Clusters parameter. This initialization parameter should not be changed except under a limited set of circumstances specific to the cluster database.
Parameter type |
Integer |
Default value |
5 |
Parameter class |
Static |
Range of values |
5 or the number of dispatchers configured, whichever is greater |
MAX_DISPATCHERS
specifies the maximum number of dispatcher processes allowed to be running simultaneously. The default value applies only if dispatchers have been configured for the system.
MAX_DUMP_FILE_SIZE
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.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
0 to 148 |
MAX_ENABLED_ROLES
specifies the maximum number of database roles that users can enable, including roles contained within other roles.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
2 to 65535 |
MAX_ROLLBACK_SEGMENTS
specifies the maximum size of the rollback segment cache in the SGA. The number specified signifies the maximum number of rollback segments that can be kept online (that is, status of ONLINE
) simultaneously by one instance.
MTS Parameters
See "Shared Server Parameters".
Parameter type |
Integer |
Default value |
Derived from |
Parameter class |
Static |
Range of values |
Operating system-dependent |
MAX_SHARED_SERVERS
specifies the maximum number of shared server processes allowed to be running simultaneously. If artificial deadlocks occur too frequently on your system, you should increase the value of MAX_SHARED_SERVERS
.
Parameter type |
String |
Syntax |
|
Default value |
None |
Parameter class |
Dynamic: |
Range of values |
Any valid calendar format name |
NLS_CALENDAR
specifies which calendar system Oracle uses. It can have one of the following values:
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
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
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
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
.
Parameter type |
String |
Syntax |
|
Default value |
Derived from |
Parameter class |
Dynamic: |
Range of values |
Any valid |
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
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.
Parameter type |
String |
Syntax |
|
Default value |
Derived from |
Parameter class |
Dynamic : |
Range of values |
Any valid |
NLS_ISO_CURRENCY
specifies the string to use as the international currency symbol for the C number format element.
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
.
Parameter type |
String |
Syntax |
Example: |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
NLS_LENGTH_SEMANTICS
enables you to create CHAR
and VARCHAR2
columns using either byte or character length semantics. Existing columns are not affected.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
NLS_NCHAR_CONV_EXCP
determines whether data loss during an implicit or explicit character type conversion will report an error.
Parameter type |
String |
Syntax |
|
Default value |
Derived from |
Parameter class |
Dynamic: |
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
specifies the collating sequence for ORDER BY
queries.
Parameter type |
String |
Syntax |
|
Default value |
Operating system-dependent |
Parameter class |
Dynamic: |
Range of values |
Any valid territory name |
NLS_TERRITORY
specifies the name of the territory whose conventions are to be followed for day and week numbering.
Parameter type |
String |
Syntax |
|
Default value |
Derived from |
Parameter class |
Dynamic: |
Range of values |
Any valid datetime format mask |
NLS_TIMESTAMP_FORMAT
defines the default timestamp format to use with the TO_CHAR
and TO_TIMESTAMP
functions.
Parameter type |
String |
Syntax |
|
Default value |
Derived from |
Parameter class |
Dynamic: |
Range of values |
Any valid datetime format mask |
NLS_TIMESTAMP_TZ_FORMAT
defines the default timestamp with time zone format to use with the TO_CHAR
and TO_TIMESTAMP_TZ
functions.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
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.
Parameter type |
Integer |
Default value |
10 |
Parameter class |
Dynamic: |
Range of values |
0 to operating system-dependent maximum |
The object cache is a memory block on the client that allows applications to store entire objects and to navigate among them without round trips to the server. OBJECT_CACHE_MAX_SIZE_PERCENT
specifies the percentage of the optimal cache size that the session object cache can grow past the optimal size. The maximum size is equal to the optimal size plus the product of this percentage and the optimal size. When the cache size exceeds this maximum size, the system will attempt to shrink the cache to the optimal size.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
10 KB to operating system-dependent maximum |
The object cache is a memory block on the client that allows applications to store entire objects and to navigate among them without round trips to the server. OBJECT_CACHE_OPTIMAL_SIZE
specifies (in bytes) the size to which the session object cache is reduced when the size of the cache exceeds the maximum size.
Parameter type |
Integer |
Default value |
32 MB |
Parameter class |
Dynamic: |
Range of values |
32 MB to 2 GB |
OLAP_PAGE_POOL_SIZE
specifies the size (in bytes) of the OLAP pool.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
1 to 4294967295 (4 GB -1) |
OPEN_CURSORS
specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
0 to 255 |
OPEN_LINKS
specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
0 to 4294967295 (4 GB -1) |
Real Application Clusters |
Multiple instances can have different values. |
OPEN_LINKS_PER_INSTANCE
specifies the maximum number of migratable open connections globally for each database instance. XA transactions use migratable open connections so that the connections are cached after a transaction is committed. Another transaction can use the connection, provided the user who created the connection is the same as the user who owns the transaction.
OPTIMIZER_DYNAMIC_SAMPLING
controls the level of dynamic sampling performed by the optimizer.
OPTIMIZER_FEATURES_ENABLE
acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
0 to 100 |
OPTIMIZER_INDEX_CACHING
lets you adjust the behavior of cost-based optimization to favor nested loops joins and IN-list iterators.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
1 to 10000 |
OPTIMIZER_INDEX_COST_ADJ
lets you tune optimizer behavior for access path selection to be more or less index friendly--that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
OPTIMIZER_MAX_PERMUTATIONS
restricts the number of permutations of the tables the optimizer will consider in queries with joins. Such a restriction ensures that the parse time for the query stays within acceptable limits. However, a slight risk exists that the optimizer will overlook a good plan it would otherwise have found.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
OPTIMIZER_MODE
establishes the default behavior for choosing an optimization approach for the instance.
A collection is data collected for events that occurred while an instrumented product was running. ORACLE_TRACE_COLLECTION_NAME
specifies the Oracle Trace collection name for this instance. Oracle also uses this parameter in the output file names (collection definition file .cdf and data collection file .dat). If you set ORACLE_TRACE_ENABLE
to true
, setting this value to a non-null string will start a default Oracle Trace collection that will run until this value is set to null again.
Parameter type |
String |
Syntax |
|
Default value |
Operating system-specific |
Parameter class |
Static |
Range of values |
Full directory pathname |
ORACLE_TRACE_COLLECTION_PATH
specifies the directory pathname where the Oracle Trace collection definition (.cdf) and data collection (.dat) files are located. If you accept the default, the Oracle Trace .cdf and .dat files will be located in ORACLE_HOME
/otrace/admin/cdf
.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
0 to 4294967295 |
ORACLE_TRACE_COLLECTION_SIZE
specifies (in bytes) the maximum size of the Oracle Trace collection file (.dat). Once the collection file reaches this maximum, the collection is disabled. A value of 0
means that the file has no size limit.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
To enable Oracle Trace collections for the server, set ORACLE_TRACE_ENABLE
to true
. This setting alone does not start an Oracle Trace collection, but it allows Oracle Trace to be used for the server.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Static |
ORACLE_TRACE_FACILITY_NAME
specifies the event set that Oracle Trace collects. The value of this parameter, followed by the .fdf extension, is the name of the Oracle Trace product definition file. That file must be located in the directory specified by the ORACLE_TRACE_FACILITY_PATH
parameter. The product definition file contains definition information for all the events and data items that can be collected for products that use the Oracle Trace data collection API.
Parameter type |
String |
Syntax |
|
Default value |
Operating system-specific |
Parameter class |
Static |
Range of values |
Full directory pathname |
ORACLE_TRACE_FACILITY_PATH
specifies the directory pathname where Oracle Trace facility definition files are located. On Solaris, the default path is ORACLE_HOME
/otrace/admin/fdf/
. On NT, the default path is %OTRACE80%\ADMIN\FDF\
.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Static |
OS_AUTHENT_PREFIX
specifies a prefix that Oracle uses to authenticate users attempting to connect to the server. Oracle concatenates the value of this parameter to the beginning of the user's operating system account name and password. When a connection request is attempted, Oracle compares the prefixed username with Oracle usernames in the database.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
OS_ROLES
determines whether Oracle or the operating system identifies and manages the roles of each username.
Parameter type |
Boolean |
Default value |
Derived from the value of |
Parameter class |
Dynamic: |
Range of values |
|
PARALLEL_ADAPTIVE_MULTI_USER
, when set to true
, enables an adaptive algorithm designed to improve performance in multiuser environments that use parallel execution. The algorithm automatically reduces the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction factor.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
Note: This parameter applies to parallel execution in exclusive mode as well as in the Oracle9i Real Application Clusters environment. |
When PARALLEL_AUTOMATIC_TUNING
is set to true
, Oracle determines the default values for parameters that control parallel execution. In addition to setting this parameter, you must specify the PARALLEL
clause for the target tables in the system. Oracle then tunes all subsequent parallel operations automatically.
Parameter type |
Integer |
Default value |
Operating system-dependent |
Parameter class |
Static |
Range of values |
2148 to 65535 (64 KB - 1) |
Real Application Clusters |
Multiple instances must have the same value. |
PARALLEL_EXECUTION_MESSAGE_SIZE
specifies the size of messages for parallel execution (formerly referred to as parallel query, PDML, Parallel Recovery, replication).
PARALLEL_INSTANCE_GROUP
is an Oracle9i Real Application Clusters parameter that you can specify in parallel mode only. Used in conjunction with the INSTANCE_GROUPS
parameter, it lets you restrict parallel query operations to a limited number of instances.
Note: This parameter applies to parallel execution in exclusive mode as well as in the Oracle9i Real Application Clusters environment. |
PARALLEL_MAX_SERVERS
specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle increases the number of processes from the number created at instance startup up to this value.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
0 to 100 |
Real Application Clusters |
Multiple instances can have different values. |
PARALLEL_MIN_PERCENT
operates in conjunction with PARALLEL_MAX_SERVERS
and PARALLEL_MIN_SERVERS
. It lets you specify the minimum percentage of parallel execution processes (of the value of PARALLEL_MAX_SERVERS
) required for parallel execution. Setting this parameter ensures that parallel operations will not execute sequentially unless adequate resources are available. The default value of 0
means that no minimum percentage of processes has been set.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
0 to value of |
Real Application Clusters |
Multiple instances can have different values. |
Note: This parameter applies to parallel execution in exclusive mode as well as in the Oracle9i Real Application Clusters environment. |
PARALLEL_MIN_SERVERS
specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes Oracle creates when the instance is started.
Parameter type |
Integer |
Default value |
Operating system-dependent, usually 2 |
Parameter class |
Dynamic: |
Range of values |
Any nonzero number |
Note: This parameter applies to parallel execution in exclusive mode as well as in the Oracle9i Real Application Clusters environment. |
PARALLEL_THREADS_PER_CPU
specifies the default degree of parallelism for the instance and determines the parallel adaptive and load balancing algorithms. The parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
Note: Oracle Corporation recommends that you use partitioned tables (available starting with Oracle8) rather than partition views. Partition views are supported for backward compatibility only. |
PARTITION_VIEW_ENABLED
specifies whether the optimizer uses partition views. If you set this parameter to true
, the optimizer prunes (or skips) unnecessary table accesses in a partition view and alters the way it computes statistics on a partition view from statistics on underlying tables.
PGA_AGGREGATE_TARGET
specifies the target aggregate PGA memory available to all server processes attached to the instance. You must set this parameter to enable the automatic sizing of SQL working areas used by memory-intensive SQL operators such as sort, group-by, hash-join, bitmap merge, and bitmap create.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
PLSQL_COMPILER_FLAGS
is a parameter used by the PL/SQL compiler. It specifies a list of compiler flags as a comma-separated list of strings.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
Range of values |
Any valid path name |
PLSQL_NATIVE_C_COMPILER
specifies the full path name of a C compiler which is used to compile the generated C file into an object file.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
Range of values |
Any valid directory path |
PLSQL_NATIVE_LIBRARY_DIR
is a parameter used by the PL/SQL compiler. It specifies the name of a directory where the shared objects produced by the native compiler are stored.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
specifies the number of subdirectories created by the database administrator in the directory specified by PLSQL_NATIVE_LIBRARY_DIR
.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
Range of values |
Any valid path name |
PLSQL_NATIVE_LINKER
specifies the full path name of a linker such as ld in UNIX or GNU ld which is used to link the object file into a shared object or DLL.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
Range of values |
Any valid path name |
PLSQL_NATIVE_MAKE_FILE_NAME
specifies the full path name of a make file. The make utility (specified by PLSQL_NATIVE_MAKE_UTILITY
) uses this make file to generate the shared object or DLL.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
Range of values |
Any valid path name |
PLSQL_NATIVE_MAKE_UTILITY
specifies the full path name of a make utility such as make in UNIX or gmake (GNU make). The make utility is needed to generate the shared object or DLL from the generated C source.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
PL/SQL Version 2 allows some abnormal behavior that Version 8 disallows. If you want to retain that behavior for backward compatibility, set PLSQL_V2_COMPATIBILITY
to true
. If you set it to false
, PL/SQL Version 8 behavior is enforced and Version 2 behavior is not allowed.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
PRE_PAGE_SGA
determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.
PROCESSES
specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.
QUERY_REWRITE_ENABLED
allows you to enable or disable query rewriting globally for the database.
QUERY_REWRITE_INTEGRITY
determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships.
Parameter type |
X.500 Distinguished Name |
Default value |
There is no default value. |
Parameter class |
Static |
Range of values |
All X.500 Distinguished Name format values |
RDBMS_SERVER_DN
specifies the Distinguished Name (DN) of the Oracle server. It is used for retrieving Enterprise Roles from an enterprise directory service.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
READ_ONLY_OPEN_DELAYED
determines when datafiles in read-only tablespaces are accessed.
Parameter type |
Integer |
Default value |
Operating system-dependent |
Parameter class |
Static |
Range of values |
Operating system-dependent, but cannot exceed |
RECOVERY_PARALLELISM
specifies the number of processes to participate in instance or crash recovery. A value of 0
or 1
indicates that recovery is to be performed serially by one process.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Static |
REMOTE_ARCHIVE_ENABLE
enables or disables the sending of redo archival to remote destinations and the receipt of remotely archived redo.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
REMOTE_DEPENDENCIES_MODE
specifies how Oracle should handle dependencies upon remote PL/SQL stored procedures.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
REMOTE_LISTENER
specifies a network name that resolves to an address or address list of Oracle Net remote listeners (that is, listeners that are not running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA
file or other address repository as configured for your system.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Static |
Real Application Clusters |
Multiple instances must have the same value. |
REMOTE_LOGIN_PASSWORDFILE
specifies whether Oracle checks for a password file and how many databases can use the password file.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
REMOTE_OS_AUTHENT
specifies whether remote clients will be authenticated with the value of the OS_AUTHENT_PREFIX
parameter.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
REMOTE_OS_ROLES
specifies whether operating system roles are allowed for remote clients. The default value, false
, causes Oracle to identify and manage roles for remote clients.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
REPLICATION_DEPENDENCY_TRACKING
enables or disables dependency tracking for read/write operations to the database. Dependency tracking is essential for propagating changes in a replicated environment in parallel.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
RESOURCE_LIMIT
determines whether resource limits are enforced in database profiles.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Dynamic: |
Range of values |
Any valid character string |
RESOURCE_MANAGER_PLAN
specifies the top-level resource plan to use for an instance. The resource manager will load this top-level plan along with all its descendants (subplans, directives, and consumer groups). If you do not specify this parameter, the resource manager is off by default.
ROLLBACK_SEGMENTS
allocates one or more rollback segments by name to this instance. If you set this parameter, the instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance (calculated as TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT
).
ROW_LOCKING
specifies whether row locks are acquired during UPDATE
operations.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Static |
SERIAL_REUSE
specifies which types of cursors make use of the serial-reusable memory feature. This feature allocates private cursor memory in the SGA so that it can be reused (serially, not concurrently) by sessions executing the same cursor.
SERVICE_NAMES
specifies one or more names for the database service to which this instance connects. You can specify multiple service names in order to distinguish among different uses of the same database.
Parameter type |
Integer |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
0 to operating system-dependent |
Real Application Clusters |
Multiple instances can have different values. |
SESSION_CACHED_CURSORS
lets you specify the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
1 to either 50 or the value of |
SESSION_MAX_OPEN_FILES
specifies the maximum number of BFILEs that can be opened in any session. Once this number is reached, subsequent attempts to open more files in the session by using DBMS_LOB.FILEOPEN()
or OCILobFileOpen()
will fail. The maximum value for this parameter depends on the equivalent parameter defined for the underlying operating system.
Parameter type |
Integer |
Default value |
Derived: (1.1 * |
Parameter class |
Static |
Range of values |
1 to 231 |
SESSIONS
specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.
SGA_MAX_SIZE
specifies the maximum size of SGA for the lifetime of the instance.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Static |
SHADOW_CORE_DUMP
specifies whether Oracle includes the SGA in the core file for foreground (client) processes.
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
SHARED_MEMORY_ADDRESS
and HI_SHARED_MEMORY_ADDRESS
specify the starting address at runtime of the system global area (SGA). This parameter is ignored on the many platforms that specify the SGA's starting address at linktime.
SHARED_POOL_RESERVED_SIZE
specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory.
SHARED_POOL_SIZE
specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. If you set PARALLEL_AUTOMATIC_TUNING
to false
, then Oracle also allocates parallel execution message buffers from the shared pool. Larger values improve performance in multi-user systems. Smaller values use less memory.
Beginning in Oracle9i, the multi-threaded server architecture is called shared server architecture.
When you start your instance, Oracle creates shared server processes and dispatcher processes for the shared server architecture based on the values of the SHARED_SERVERS
and DISPATCHERS
initialization parameters. You can also set the SHARED_SERVERS
and DISPATCHERS
parameters with ALTER
SYSTEM
to perform one of the following operations while the instance is running:
MAX_DISPATCHERS
.SHARED_SERVERS
specifies the number of server processes that you want to create when an instance is started up. If system load decreases, this minimum number of servers is maintained. Therefore, you should take care not to set SHARED_SERVERS
too high at system startup.
Parameter type |
Integer |
Default value |
Derived: the lesser of |
Parameter class |
Static |
Range of values |
0 to |
SHARED_SERVER_SESSIONS
specifies the total number of shared server architecture user sessions to allow. Setting this parameter enables you to reserve user sessions for dedicated servers.
SORT_AREA_RETAINED_SIZE
specifies (in bytes) the maximum amount of the user global area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer, which Oracle uses to maintain a portion of the sort in memory. This memory is released back to the UGA, not to the operating system, after the last row is fetched from the sort space.
SORT_AREA_SIZE
specifies in bytes the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE
parameter. After the last row is returned, Oracle releases the remainder of the memory.
The value of this parameter is the name of the current server parameter file (SPFILE
) in use. This parameter can be defined in a client side PFILE to indicate the name of the server parameter file to use.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
The SQL92 standards specify that security administrators should be able to require that users have SELECT
privilege on a table when executing an UPDATE
or DELETE
statement that references table column values in a WHERE
or SET
clause. SQL92_SECURITY
lets you specify whether users must have been granted the SELECT
object privilege in order to execute such UPDATE
or DELETE
statements.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
The value of SQL_TRACE
disables or enables the SQL trace facility. Setting this parameter to true
provides information on tuning that you can use to improve performance. You can change the value using the DBMS_SYSTEM
package.
Parameter type |
String |
Syntax |
|
Default value |
Operating system-specific |
Parameter class |
Dynamic: |
Range of values |
A valid path or device name other than |
STANDBY_ARCHIVE_DEST
is relevant only for a standby database in managed recovery mode. It specifies the location of archive logs arriving from a primary database. Oracle uses STANDBY_ARCHIVE_DEST
and LOG_ARCHIVE_FORMAT
to fabricate the fully qualified standby log filenames and stores the filenames in the standby control file.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
STANDBY_FILE_MANAGEMENT
enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
STAR_TRANSFORMATION_ENABLED
determines whether a cost-based query transformation will be applied to star queries.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Dynamic: |
STATISTICS_LEVEL
sets the statistics collection level of the database.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
TAPE_ASYNCH_IO
controls whether I/O to sequential devices (for example, backup or restore of Oracle data to or from tape) is asynchronous--that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans. If your platform supports asynchronous I/O to sequential devices, Oracle Corporation recommends that you leave this parameter set to its default. However, if the asynchronous I/O implementation is not stable, you can set TAPE_ASYNCH_IO
to false
to disable asynchronous I/O. If your platform does not support asynchronous I/O to sequential devices, this parameter has no effect.
THREAD
is an Oracle9i Real Application Clusters parameter that specifies the number of the redo thread to be used by an instance.
TIMED_OS_STATISTICS
specifies the interval (in seconds) at which Oracle collects operating system statistics when a request is made from the client to the server or when a request completes.
TIMED_STATISTICS
specifies whether or not statistics related to time are collected.
TRACE_ENABLED
controls tracing of the execution history, or code path, of Oracle. Oracle Support Services uses this information for debugging.
TRACEFILE_IDENTIFIER
specifies a custom identifier that becomes part of the Oracle Trace file name. Such a custom identifier is used to identify a trace file simply from its name and without having to open it or view its contents.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
If TRANSACTION_AUDITING
is true
, Oracle generates a special redo record that contains the user logon name, username, the session ID, some operating system information, and client information. For each successive transaction, Oracle generates a record that contains only the session ID. These subsequent records link back to the first record, which also contains the session ID.
Parameter type |
Integer |
Default value |
Derived: (1.1 * |
Parameter class |
Static |
Range of values |
4 to 232 |
Real Application Clusters |
Multiple instances can have different values. |
TRANSACTIONS
specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. The default value is greater than SESSIONS
(and, in turn, PROCESSES
) to allow for recursive transactions.
Note: You can set this parameter using |
Parameter type |
Integer |
Default value |
|
Parameter class |
Static |
Range of values |
1 to operating system-dependent |
Real Application Clusters |
Multiple instances can have different values. |
TRANSACTIONS_PER_ROLLBACK_SEGMENT
specifies the number of concurrent transactions you expect each rollback segment to have to handle. The minimum number of rollback segments acquired at startup is TRANSACTIONS
divided by the value for this parameter. For example, if TRANSACTIONS
is 101 and this parameter is 10, then the minimum number of rollback segments acquired would be the ratio 101/10, rounded up to 11.
Parameter type |
String |
Syntax |
|
Default value |
|
Parameter class |
Static |
Real Application Clusters |
Multiple instances must have the same value. |
UNDO_MANAGEMENT
specifies which undo space management mode the system should use. When set to AUTO
, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.
UNDO_RETENTION
specifies (in seconds) the amount of committed undo information to retain in the database. You can use UNDO_RETENTION
to satisfy queries that require old undo information to rollback changes to produce older images of data blocks. You can set the value at instance startup.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Dynamic: |
Range of values |
|
UNDO_SUPPRESS_ERRORS
enables users to suppress errors while executing manual undo management mode operations (for example, ALTER ROLLBACK SEGMENT ONLINE
) in automatic undo management mode. Setting this parameter enables users to use the undo tablespace feature before all application programs and scripts are converted to automatic undo management mode. For example, if you have a tool that uses SET TRANSACTION USE ROLLBACK SEGMENT
statement, you can add the statement "ALTER SESSION SET UNDO_SUPPRESS_ERRORS = true
" to the tool to suppress the ORA-30019 error.
UNDO_TABLESPACE
specifies the undo tablespace to be used when an instance starts up. If this parameter is specified when the instance is in manual undo management mode, an error will occur and startup will fail.
Parameter type |
Boolean |
Default value |
|
Parameter class |
Static |
Range of values |
|
USE_INDIRECT_DATA_BUFFERS
controls how the system global area (SGA) uses memory. It enables or disables the use of the extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory. On platforms that do not support this much physical memory, this parameter is ignored.
Syntax: USE_STORED_OUTLINES
= { TRUE
| FALSE
| category_name
}
The USE_STORED_OUTLINES
parameter determines whether the optimizer will use stored public outlines to generate execution plans. USE_STORED_OUTLINES
is not an initialization parameter.
TRUE
causes the optimizer to use outlines stored in the DEFAULT
category when compiling requests.FALSE
specifies that the optimizer should not use stored outlines. This is the default.category_name
causes the optimizer to use outlines stored in the category_name
category when compiling requests.USER_DUMP_DEST
specifies the pathname for a directory where the server will write debugging trace files on behalf of a user process.
Parameter type |
String |
Syntax |
|
Default value |
There is no default value. |
Parameter class |
Static |
Range of values |
Any valid directory path |
UTL_FILE_DIR
lets you specify one or more directories that Oracle should use for PL/SQL file I/O. If you are specifying multiple directories, you must repeat the UTL_FILE_DIR
parameter for each directory on separate lines of the initialization parameter file.
WORKAREA_SIZE_POLICY
specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.
The following statement manually archives the redo log file group with the log sequence number 4 in thread number 3:
ALTER SYSTEM ARCHIVE LOG THREAD 3 SEQUENCE 4;
The following statement manually archives the redo log file group containing the redo log entry with the SCN 9356083:
ALTER SYSTEM ARCHIVE LOG CHANGE 9356083;
The following statement manually archives the redo log file group containing a member named 'diskl:log6.log
' to an archived redo log file in the location 'diska:[arch$
]':
ALTER SYSTEM ARCHIVE LOG LOGFILE 'diskl:log6.log' TO 'diska:[arch$]';
This statement enables query rewrite in all sessions for all materialized views that have not been explicitly disabled:
ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE;
You may want to restrict logons if you are performing application maintenance and you want only application developers with RESTRICTED
SESSION
system privilege to log on. To restrict logons, issue the following statement:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
You can then terminate any existing sessions using the KILL
SESSION
clause of the ALTER
SYSTEM
statement.
After performing maintenance on your application, issue the following statement to allow any user with CREATE SESSION
system privilege to log on:
ALTER SYSTEM DISABLE RESTRICTED SESSION;
You might want to clear the shared pool before beginning performance analysis. To clear the shared pool, issue the following statement:
ALTER SYSTEM FLUSH SHARED_POOL;
The following statement forces a checkpoint:
ALTER SYSTEM CHECKPOINT;
This ALTER
SYSTEM
statement dynamically enables resource limits:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
The following statement changes the minimum number of shared server processes to 25:
ALTER SYSTEM SET SHARED_SERVERS = 25;
If there are currently fewer than 25 shared server processes, then Oracle creates more. If there are currently more than 25, then Oracle terminates some of them when they are finished processing their current calls if the load could be managed by the remaining 25.
The following statement dynamically changes the number of dispatcher processes for the TCP/IP protocol to 5 and the number of dispatcher processes for the DECNet protocol to 10:
ALTER SYSTEM SET DISPATCHERS = '(INDEX=0)(PROTOCOL=TCP)(DISPATCHERS=5)', '(INDEX=1)(PROTOCOL=DECNet)(DISPATCHERS=10)';
If there are currently fewer than 5 dispatcher processes for TCP, then Oracle creates new ones. If there are currently more than 5, then Oracle terminates some of them after the connected users disconnect.
If there are currently fewer than 10 dispatcher processes for DECNet, then Oracle creates new ones. If there are currently more than 10, then Oracle terminates some of them after the connected users disconnect.
If there are currently existing dispatchers for another protocol, then the preceding statement does not affect the number of dispatchers for that protocol.
The following statement dynamically changes the limit on sessions for your instance to 64 and the warning threshold for sessions on your instance to 54:
ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 64 LICENSE_SESSIONS_WARNING = 54;
If the number of sessions reaches 54, then Oracle writes a warning message to the ALERT
file for each subsequent session. Also, users with RESTRICTED
SESSION
system privilege receive warning messages when they begin subsequent sessions.
If the number of sessions reaches 64, then only users with RESTRICTED
SESSION
system privilege can begin new sessions until the number of sessions falls below 64 again.
The following statement dynamically disables the limit for sessions on your instance. After you issue the preceding statement, Oracle no longer limits the number of sessions on your instance.
ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 0;
The following statement dynamically changes the limit on the number of users in the database to 200. After you issue the preceding statement, Oracle prevents the number of users in the database from exceeding 200.
ALTER SYSTEM SET LICENSE_MAX_USERS = 200;
You may want to force a log switch to drop or rename the current redo log file group or one of its members, because you cannot drop or rename a file while Oracle is writing to it. The forced log switch affects only your instance's redo log thread. The following statement forces a log switch:
ALTER SYSTEM SWITCH LOGFILE;
The following statement enables distributed recovery:
ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
You may want to disable distributed recovery for demonstration or testing purposes.You can disable distributed recovery in both single-process and multiprocess mode with the following statement:
ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;
When your demonstration or testing is complete, you can then enable distributed recovery again by issuing an ALTER
SYSTEM
statement with the ENABLE
DISTRIBUTED
RECOVERY
clause.
You may want to kill the session of a user that is holding resources needed by other users. The user receives an error message indicating that the session has been killed. That user can no longer make calls to the database without beginning a new session. Consider this data from the V$SESSION
dynamic performance table:
SELECT sid, serial#, username
FROM v$session;
SID SERIAL# USERNAME ----- --------- ---------------- 1 1 2 1 3 1 4 1 5 1 7 1 8 28 OPS$BQUIGLEY 10 211 OPS$SWIFT 11 39 OPS$OBRIEN 12 13 SYSTEM 13 8 SCOTT
The following statement kills the session of the user scott
using the SID
and SERIAL#
values from V$SESSION
:
ALTER SYSTEM KILL SESSION '13, 8';
The following statement disconnects user scott
's session, using the SID
and SERIAL#
values from V$SESSION
:
ALTER SYSTEM DISCONNECT SESSION '13, 8' POST_TRANSACTION;
|
![]() Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|