■ ANSI Compliance
BLANK_TRIMMING
BLANK_TRIMMING specifies the data assignment semantics of character datatypes.
Values:
■ TRUE
Allows the data assignment of a source character string or variable to a destination
character column or variable even though the source length is longer than the
destination length. In this case, however, the additional length over the destination
length is all blanks. This value complies with the semantics of SQL92 Transitional
Level and above.
■ FALSE
Disallows the data assignment if the source length is longer than the destination
length and reverts to SQL92 Entry Level semantics.
■ Backup and Restore
BACKUP_TAPE_IO_SLAVES
BACKUP_TAPE_IO_SLAVES specifies whether I/O server processes (also called
slaves) are used by 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.
Note: You cannot perform duplexed backups unless you enable this
parameter. Otherwise, Oracle returns an error. When this parameter is
enabled, Recovery Manager will configure as many server processes
as needed for the number of backup copies requested.
RECYCLEBIN
TAPE_ASYNCH_IO
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 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.
■ BFILEs
SESSION_MAX_OPEN_FILES
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.
■ Buffer Cache and I/O
DB_nK_CACHE_SIZE
DB_BLOCK_BUFFERS
DB_BLOCK_SIZE
DB_CACHE_ADVICE
DB_CACHE_SIZE
DB_FILE_MULTIBLOCK_READ_COUNT
DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to
minimize I/O during table scans. It specifies the maximum number of blocks read in
one I/O operation during a sequential scan. The total number of I/Os needed to
perform a full table scan depends on such factors as the size of the table, the
multiblock read count, and whether parallel execution is being utilized for the
operation.
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_WRITER_PROCESSES
Default value : 1 or CPU_COUNT / 8, whichever is greater
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.
DBWR_IO_SLAVES
DBWR_IO_SLAVES is relevant only on systems with only one database writer process
(DBW0). It specifies the number of I/O server processes used by the DBW0 process. The
DBW0 process and its server processes always write to disk. By default, the value is 0 and I/O server processes are not used.
If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server processes
used by the ARCH and LGWR processes is set to 4. However, the number of I/O server
processes used by Recovery Manager is set to 4 only if asynchronous I/O is disabled
(either your platform does not support asynchronous I/O or disk_asynch_io is set
to false).
Typically, I/O server processes are used to simulate asynchronous I/O on platforms
that do not support asynchronous I/O or that implement it inefficiently. However, you
can use I/O server processes even when asynchronous I/O is being used. In that case
the I/O server processes will use asynchronous I/O.
I/O server processes are also useful in database environments with very large I/O
throughput, even if asynchronous I/O is enabled.
DISK_ASYNCH_IO
DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is
asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans). If your platform supports asynchronous I/O to disk, Oracle 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.
If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES to
a value other than its default of zero in order to simulate asynchronous I/O.
FILESYSTEMIO_OPTIONS
Syntax FILESYSTEMIO_OPTIONS = { none | setall | directIO | asynch }
FILESYSTEMIO_OPTIONS specifies I/O operations for file system files.
READ_ONLY_OPEN_DELAYED
READ_ONLY_OPEN_DELAYED determines when datafiles in read-only tablespaces are
accessed.
Values:
■ true
The datafiles are accessed for the first time only when an attempt is made to read
data stored within them.
■ false
The datafiles are accessed at database open time.
USE_INDIRECT_DATA_BUFFERS
■ Cursors and Library Cache
CURSOR_SHARING
CURSOR_SHARING determines what kind of SQL statements can share the same
cursors.
Values:
■ FORCE
Forces statements that may differ in some literals, but are otherwise identical, to
share a cursor, unless the literals affect the meaning of the statement.
■ SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to
share a cursor, unless the literals affect either the meaning of the statement or the
degree to which the plan is optimized.
■ EXACT
Only allows statements with identical text to share the same cursor.
Note: Forcing cursor sharing among similar (but not identical)
statements can have unexpected results in some DSS applications, or
applications that use stored outlines.
CURSOR_SPACE_FOR_TIME
CURSOR_SPACE_FOR_TIME lets you use more space for cursors in order to save time.
It affects both the shared SQL area and the client's private SQL area.
Values:
■ TRUE
Shared SQL areas are kept pinned in the shared pool. As a result, shared SQL areas
are not aged out of the pool as long as an open cursor references them. Because
each active cursor's SQL area is present in memory, execution is faster. However,
the shared SQL areas never leave memory while they are in use. Therefore, you
should set this parameter to TRUE only when the shared pool is large enough to
hold all open cursors simultaneously.
In addition, a setting of TRUE retains the private SQL area allocated for each cursor
between executions instead of discarding it after cursor execution, saving cursor
allocation and initialization time
■ FALSE
Shared SQL areas can be deallocated from the library cache to make room for new
SQL statements.
OPEN_CURSORS
OPEN_CURSORS specifies the maximum number of open cursors (handles to private
SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.
It is important to set the value of OPEN_CURSORS high enough to prevent your
application from running out of open cursors. The number will vary from one
application to another. Assuming that a session does not open the number of cursors
specified by OPEN_CURSORS, there is no added overhead to setting this value higher
than actually needed.
SESSION_CACHED_CURSORS
SESSION_CACHED_CURSORS specifies 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.
This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL
uses to avoid having to reparse as statements are re-executed by a user.
■ Database/Instance Identification
DB_DOMAIN
In a distributed database system, DB_DOMAIN specifies the logical location of the
database within the network structure. You should set this parameter if this database is or ever will be part of a distributed system. The value consists of the extension components of a global database name, consisting of valid identifiers (any
alphanumeric ASCII characters), separated by periods. Oracle recommends that you
specify DB_DOMAIN as a unique string for all databases in a domain.
This parameter allows one department to create a database without worrying that it
might have the same name as a database created by another department. If one sales
department's DB_DOMAIN is JAPAN.ACME.COM, then their SALES database
(SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with
DB_NAME = SALES but with DB_DOMAIN = US.ACME.COM.
If you omit the domains from the name of a database link, Oracle expands the name by
qualifying the database with the domain of your local database as it currently exists in the data dictionary, and then stores the link name in the data dictionary. The characters valid in a database domain name are: alphanumeric characters, underscore (_), and number sign (#).
DB_NAME
INSTANCE_NAME
■ Diagnostics and Statistics
BACKGROUND_CORE_DUMP
BACKGROUND_DUMP_DEST
CORE_DUMP_DEST
DB_BLOCK_CHECKING
DB_BLOCK_CHECKING controls whether or not Oracle performs block checking for
database blocks. The checking that is performed depends on the value you supply, as
follows:
■ OFF - no block checking is performed for blocks in the user tablespaces. However,
semantic block checking for SYSTEM tablespace blocks is always turned on.
■ LOW - basic block header checks are performed after block contents change in
memory (for example, after UPDATE or INSERT statements, on-disk reads, or
inter-instance block transfers in RAC)
■ MEDIUM - all LOW checks are performed, as well as semantic block checking for all
non-index-organized table blocks
■ FULL - all LOW and MEDIUM checks are performed, as well as semantic checks for
index blocks (that is, blocks of subordinate objects that can actually be dropped
and reconstructed when faced with corruption) Oracle checks a block by going through the data in the block, making sure it is logically self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload and the parameter value. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable. For backward compatibility the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.
DB_BLOCK_CHECKSUM
DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a
checksum (a number calculated from all the bytes stored in the block) and store it in
the cache header of every data block when writing it to disk. Checksums are verified
when a block is read - only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum. In FULL mode, Oracle also verifies the checksum before a change application from update/delete statements and recomputes it after the change is applied. In addition, Oracle gives every log block a checksum before writing it to the current log.
If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM
tablespace, but not for user tablespaces. Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk. Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. In the FULL mode it causes 4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL. For backward compatibility we preserve use of TRUE (implying TYPICAL) and FALSE (implying OFF) values.
EVENT
EVENT is a parameter used only to debug the system. Do not alter the value of this
parameter except under the supervision of Oracle Support Services staff.
MAX_DUMP_FILE_SIZE
MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert
file). Change this limit if you are concerned that trace files may use too much space.
SHADOW_CORE_DUMP
STATISTICS_LEVEL
STATISTICS_LEVEL specifies the level of collection for database and operating
system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.
The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The
default value should be adequate for most environments.
When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added
to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.
Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many
of the important statistics required by Oracle Database features and functionality,
including:
■ Automatic Workload Repository (AWR) Snapshots
■ Automatic Database Diagnostic Monitor (ADDM)
■ All server-generated alerts
■ Automatic SGA Memory Management
■ Automatic optimizer statistics collection
■ Object level statistics
■ End to End Application Tracing (V$CLIENT_STATS)
■ Database time distribution statistics (V$SESS_TIME_MODEL
and V$SYS_TIME_MODEL)
■ Service level statistics
■ Buffer cache advisory
■ MTTR advisory
■ Shared pool sizing advisory
■ Segment level statistics
■ PGA Target advisory
■ Timed statistics
■ Monitoring of statistics
Note: Oracle strongly recommends that you do not disable these
important features and functionality.
TIMED_OS_STATISTICS
TIMED_OS_STATISTICS specifies (in seconds) the interval at which Oracle collects
operating system statistics when a request is made from the client to the server or
when a request completes.
■ On dedicated servers, Oracle collects operating system statistics at user logon and
after each subsequent client invocation through the OCI into the Oracle server as a
remote procedure call message.
■ On shared servers, Oracle collects statistics when client calls to Oracle are
processed.
A value of zero specifies that operating system statistics are not gathered. To collect statistics, set a value meaningful for your application and site needs.
Note: Gathering operating system statistics is very expensive. Oracle
recommends that you set this parameter in an ALTER SYSTEM statement rather than in the initialization parameter file, and that you reset the value to zero as soon as the needed statistics have been gathered.
TIMED_STATISTICS
TIMED_STATISTICS specifies whether or not statistics related to time are collected.
Values:
■ true
The statistics are collected and stored in trace files or displayed in the
V$SESSTATS and V$SYSSTATS dynamic performance views.
■ false
The value of all time-related statistics is set to zero. This setting lets Oracle avoid the overhead of requesting the time from the operating system. Normally, TIMED_
STATISTICS should be false.
On some systems with very fast timer access, Oracle might enable timing even if this
parameter is set to false. On these systems, setting the parameter to true can
sometimes produce more accurate statistics for long-running operations.
TRACE_ENABLED
TRACE_ENABLED controls tracing of the execution history, or code path, of Oracle.
Oracle Support Services uses this information for debugging.
When TRACE_ENABLED is set to true, Oracle records information in specific files
when errors occur. Oracle records this information for all instances, even if only one instance terminates. This allows Oracle to retain diagnostics for an entire cluster. Although the overhead incurred from this processing is not excessive, you can improve performance by setting TRACE_ENABLED to false. You might do this, for
example, to meet high-end benchmark requirements. However, if you leave this
parameter set to false, you may lose valuable diagnostic information. Therefore,
always set TRACE_ENABLED to true to trace system problems and to reduce
diagnostic efforts in the event of unexplained instance failures.
TRACEFILE_IDENTIFIER
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.
Each time this parameter is dynamically modified, the next trace dump will be written
to a trace file which has the new parameter value embedded in its name. Trace file
continuity information is automatically added to both the old and new trace files to
indicate that these trace files belong to the same process.
This parameter can only be used to change the name of the foreground process' trace
file; the background processes continue to have their trace files named in the regular format. For foreground processes, the TRACEID column of the V$PROCESS view
contains the current value of the TRACEFILE_IDENTIFIER parameter. When this
parameter value is set, the trace file name has the following format:
sid_ora_pid_traceid.trc
USER_DUMP_DEST
■ Distributed, Replication
COMMIT_POINT_STRENGTH
DISTRIBUTED_LOCK_TIMEOUT
DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time (in seconds) for
distributed transactions to wait for locked resources.
GLOBAL_NAMES
GLOBAL_NAMES specifies whether a database link is required to have the same name
as the database to which it connects. If the value of GLOBAL_NAMES is false, then no check is performed. If you use or plan to use distributed processing, then Oracle recommends that you set this parameter to true to ensure the use of consistent naming conventions for databases and links in a networked environment.
HS_AUTOREGISTER
HS_AUTOREGISTER enables or disables automatic self-registration of Heterogeneous
Services (HS) agents. When enabled, information is uploaded into the server's data
dictionary to describe a previously unknown agent class or a new agent version.
Oracle recommends that you set this parameter to true. Oracle incurs less overhead
when establishing subsequent connections through the same agent if self-registered
information is available in the server's data dictionary.
OPEN_LINKS
OPEN_LINKS specifies the maximum number of concurrent open connections to
remote databases in one session. These connections include database links, as well as
external procedures and cartridges, each of which uses a separate process.
Oracle counts one open link for the following:
■ For each user that references a public or private database link
■ For each external procedure or cartridge connection when it is executed for the
first time
Both types of connections close when the session ends. You can also close a database
link connection explicitly by issuing an ALTER SESSION CLOSE DATABASE LINK
statement.
You should set this parameter to allow for the external procedure and cartridge
connections expected during the session plus the number of databases referred to in
typical distributed transactions (that is, a single SQL statement that references multiple databases), so that all the databases can be open to execute the statement. For example,
if queries alternately access databases A, B, and C, and OPEN_LINKS is set to 2, time
will be lost waiting while one connection is broken and another made. Increase the
value if many different databases are accessed over time.
OPEN_LINKS_PER_INSTANCE
OPEN_LINKS_PER_INSTANCE specifies the maximum number of migratable open
connections globally for each database instance. XA transactions use migratable open
connections so that the connections are cached after a transaction is committed.
Another transaction can use the connection, provided the user who created the
connection is the same as the user who owns the transaction.
OPEN_LINKS_PER_INSTANCE is different from OPEN_LINKS, which indicates the
number of connections from a session. The OPEN_LINKS parameter is not applicable
to XA applications.
REPLICATION_DEPENDENCY_TRACKING
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.
Values:
■ TRUE
Enables dependency tracking.
■ FALSE
Allows read/write operations to the database to run faster, but does not produce
dependency information for Oracle to perform parallel propagation.
Note: Do not specify this value unless you are sure that your application will not perform any read/write operations to the replicated tables.
■ File Locations, Names, and Sizes
AUDIT_FILE_DEST
BACKGROUND_CORE_DUMP
BACKGROUND_DUMP_DEST
CONTROL_FILES
CORE_DUMP_DEST
DB_CREATE_FILE_DEST
DB_CREATE_FILE_DEST specifies the default location for Oracle-managed datafiles.
This location is also used as the default location for Oracle-managed control files and online redo logs if none of the DB_CREATE_ONLINE_LOG_DEST_n initialization
parameters are specified.
DB_CREATE_ONLINE_LOG_DEST_n
Syntax DB_CREATE_ONLINE_LOG_DEST_[1 | 2 | 3 | 4 | 5] =
directory | disk group
DB_CREATE_ONLINE_LOG_DEST_n (where n = 1, 2, 3, ... 5) specifies the default
location for Oracle-managed control files and online redo logs. If more than one DB_
CREATE_ONLINE_LOG_DEST_n parameter is specified, then the control file or online
redo log is multiplexed across the locations of the other DB_CREATE_ONLINE_LOG_
DEST_n parameters. One member of each online redo log is created in each location,
and one control file is created in each location. Specifying at least two parameters provides greater fault tolerance for the control files and online redo logs if one of the locations should fail.
DB_FILES
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
FILE_MAPPING
FILE_MAPPING enables or disables file mapping. The FMON background process will
be started to manage the mapping information when file mapping is enabled.
IFILE
Use IFILE to embed another parameter file within the current parameter file. For
example:
IFILE = COMMON.ORA
You can have up to three levels of nesting. In this example, the file COMMON.ORA could contain a second IFILE parameter for the file COMMON2.ORA, which could contain a third IFILE parameter for the file GCPARMS.ORA. You can also include multiple
parameter files in one parameter file by listing IFILE several times with different
values:
IFILE = DBPARMS.ORA
IFILE = GCPARMS.ORA
IFILE = LOGPARMS.ORA
LOG_ARCHIVE_DEST_n
SPFILE
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. When the default server parameter file is used by the server, the value of SPFILE is internally set by the server.
The SPFILE resides in the ORACLE_HOME/dbs directory; however, users can place it
anywhere on their machine as long as it is specified in an initialization parameter file.
■ Globalization
NLS_CALENDAR
NLS_CALENDAR specifies which calendar system Oracle uses. It can have one of the
following values:
■ Arabic Hijrah
■ English Hijrah
■ Gregorian
■ Japanese Imperial
■ Persian
■ ROC Official (Republic of China)
■ Thai Buddha
For example, suppose NLS_CALENDAR is set to "Japanese Imperial", the date format is
"E YY-MM-DD". ("E" is the date format element for the abbreviated era name.) If the
date is May 15, 1997, then the SYSDATE is displayed as follows:
SELECT SYSDATE FROM DUAL;
SYSDATE
--------
H 09-05-15
NLS_COMP
Syntax NLS_COMP = { BINARY | LINGUISTIC | ANSI }
Values:
■ BINARY
Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless
you specify the NLSSORT function.
■ LINGUISTIC
Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks
should use the linguistic sort specified in the NLS_SORT parameter. To improve
the performance, you can also define a linguistic index on the column for which
you want linguistic comparisons.
■ ANSI
A setting of ANSI is for backwards compatibility; in general, you should set NLS_
COMP to LINGUISTIC
NLS_CURRENCY
NLS_CURRENCY specifies the string to use as the local currency symbol for the L
number format element. The default value of this parameter is determined by NLS_
TERRITORY.
NLS_DATE_FORMAT
NLS_DATE_FORMAT specifies the default date format to use with the TO_CHAR and
TO_DATE functions. The default value of this parameter is determined by
NLS_TERRITORY.
The value of this parameter can be any valid date format mask, and the value must be
surrounded by double quotation marks. For example:
NLS_DATE_FORMAT = "MM/DD/YYYY"
NLS_DATE_LANGUAGE
NLS_DATE_LANGUAGE specifies the language to use for the spelling of day and month
names and date abbreviations (a.m., p.m., AD, BC) returned by the TO_DATE and TO_
CHAR functions.
NLS_DUAL_CURRENCY
NLS_DUAL_CURRENCY specifies the dual currency symbol (such as "Euro") for the
territory. The default is the dual currency symbol defined in the territory of your
current language environment.
NLS_ISO_CURRENCY
NLS_ISO_CURRENCY specifies the string to use as the international currency symbol
for the C number format element. Local currency symbols can be ambiguous. For example, a dollar sign ($) can refer to U.S. dollars or Australian dollars. ISO Specification 4217 1987-07-15 defines unique "international" currency symbols for the currencies of specific territories or countries.
NLS_LANGUAGE
NLS_LANGUAGE specifies the default language of the database. This language is used
for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the
default sorting mechanism. This parameter also determines the default values of the
parameters NLS_DATE_LANGUAGE and NLS_SORT.
NLS_LENGTH_SEMANTICS
NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using
either byte or character length semantics. Existing columns are not affected.
NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may
be required to use byte semantics in order to maintain compatibility with existing
applications.
NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data
dictionary always uses byte semantics.
NLS_NCHAR_CONV_EXCP
NLS_NCHAR_CONV_EXCP determines whether an error is reported when there is data
loss during an implicit or explicit character type conversion between
NCHAR/NVARCHAR and CHAR/VARCHAR2. The default value results in no error
being reported.
NLS_NUMERIC_CHARACTERS
NLS_NUMERIC_CHARACTERS specifies the characters to use as the group separator
and decimal character. It overrides those characters defined implicitly by NLS_
TERRITORY. The group separator separates integer groups (that is, thousands,
millions, billions, and so on). The decimal separates the integer portion of a number
from the decimal portion.You can specify any character as the decimal or group separator. The two characters specified must be single-byte and must be different from each other. The characters cannot be any numeric character or any of the following characters: plus (+), minus sign (-), less than sign (<), greater than sign (>). Either character can be a space.
For example, if you wish to specify a comma as the decimal character and a space as
the group separator, you would set this parameter as follows:
NLS_NUMERIC_CHARACTERS = ", "
NLS_SORT
■ If the value is BINARY, then the collating sequence for ORDER BY queries is based
on the numeric value of characters (a binary sort that requires less system
overhead).
■ If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE
parameter also support a linguistic sort with the same name.
NLS_TERRITORY
NLS_TERRITORY specifies the name of the territory whose conventions are to be
followed for day and week numbering.
This parameter also establishes the default date format, the default decimal character and group separator, and the default ISO and local currency symbols.
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_FORMAT defines the default timestamp format to use with the TO_
CHAR and TO_TIMESTAMP functions.
The value must be surrounded by quotation marks as follows:
NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'
You can specify the value of NLS_TIMESTAMP_FORMAT by setting it in the
initialization parameter file. You can specify its value for a client as a client
environment variable. You can also alter the value of NLS_TIMESTAMP_FORMAT by changing its value in the initialization parameter and then restarting the instance. To alter the value during a session use the ALTER SESSION SET statement.
NLS_TIMESTAMP_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT defines the default timestamp with time zone format
to use with the TO_CHAR and TO_TIMESTAMP_TZfunctions.
The value must be surrounded by quotation marks as follows:
NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
You can specify the value of NLS_TIMESTAMP_TZ_FORMAT by setting it in the
initialization parameter file. You can specify its value for a client as a client
environment variable.
You can also alter the value of NLS_TIMESTAMP_TZ_FORMAT by changing its value in
the initialization parameter and then restarting the instance. To alter the value during a session use the ALTER SESSION SET statement.
■ Java
JAVA_MAX_SESSIONSPACE_SIZE
JAVA_POOL_SIZE
JAVA_SOFT_SESSIONSPACE_LIMIT
■ Job Queues
JOB_QUEUE_PROCESSES
JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be
created for the execution of jobs. It specifies the number of job queue processes per
instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced
queuing uses job queues for message propagation. You can create user job requests
through the DBMS_JOB package.
Some job queue requests are created automatically. An example is refresh support for
materialized views. If you wish to have your materialized views updated
automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.
■ License Limits
LICENSE_MAX_SESSIONS
LICENSE_MAX_SESSIONS specifies the maximum number of concurrent user sessions
allowed. When this limit is reached, only users with the RESTRICTED SESSION
privilege can connect to the database. Users who are not able to connect receive a
warning message indicating that the system has reached maximum capacity.
A zero value indicates that concurrent usage (session) licensing is not enforced. If you set this parameter to a nonzero number, you might also want to set LICENSE_
SESSIONS_WARNING.
Do not enable both concurrent usage licensing and user licensing. Set either LICENSE_
MAX_SESSIONS or LICENSE_MAX_USERS to zero.
LICENSE_MAX_USERS
LICENSE_MAX_USERS specifies the maximum number of users you can create in the
database. When you reach this limit, you cannot create more users. You can, however,
increase the limit.
Do not enable both concurrent usage (session) licensing and user licensing. Set either LICENSE_MAX_SESSIONS or LICENSE_MAX_USERS to zero.
LICENSE_SESSIONS_WARNING
LICENSE_SESSIONS_WARNING specifies a warning limit on the number of concurrent
user sessions. When this limit is reached, additional users can connect, but Oracle
writes a message in the alert file for each new connection. Users with RESTRICTED
SESSION privilege who connect after the limit is reached receive a warning message
stating that the system is nearing its maximum capacity.
■ Memory
LARGE_POOL_SIZE
LARGE_POOL_SIZE specifies (in bytes) the size 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 SGA_TARGET is set.
Caution: When Oracle derives a default value, it adds 250K for each session for the shared server if DISPATCHERS is configured. The final derived value also includes a port-specific amount of memory for backup I/O buffers. The total derived default value can either be too large to allocate or can cause performance problems. In that case, set LARGE_POOL_SIZE to a number sufficiently small so that the database can start.
SHARED_POOL_RESERVED_SIZE
SHARED_POOL_RESERVED_SIZE specifies (in bytes) the shared pool space that is
reserved for large contiguous requests for shared pool memory.
You can use this parameter to avoid performance degradation in the shared pool in
situations where pool fragmentation forces Oracle to search for and free chunks of
unused pool to satisfy the current request.
SHARED_POOL_SIZE
Default value If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is
specified, then the user-specified value indicates a minimum value for the memory pool. If SGA_TARGET is not set (32-bit platforms): 32 M, rounded up to the
nearest granule size. If SGA_TARGET is not set (64-bit platforms): 84 M, rounded up to the nearest granule size.
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.
You can monitor utilization of the shared pool by querying the view V$SGASTAT.
■ Miscellaneous
AQ_TM_PROCESSES
AQ_TM_PROCESSES enables time monitoring of queue messages. The times can be
used in messages that specify delay and expiration properties. Values from 1 to 10 specify the number of queue monitor processes created to monitor the messages. If AQ_TM_PROCESSES is not specified or is set to 0, then the queue monitor is not created.
COMPATIBLE
FIXED_DATE
FIXED_DATE enables you to set a constant date that SYSDATE will always return
instead of the current date. To undo a fixed date setting, specify FIXED_DATE=NONE.
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.
■ Networking
LOCAL_LISTENER
Syntax LOCAL_LISTENER = network_name
Default value - (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))
where hostname is the network name of the local host.
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.
REMOTE_LISTENER
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.
SERVICE_NAMES
SERVICE_NAMES specifies one or more names by which clients can connect to the
instance. The instance registers its service names with the listener. When a client
requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance.
You can specify multiple service names in order to distinguish among different uses of the same database.
For example:
SERVICE_NAMES = sales.acme.com, widgetsales.acme.com
■ Objects and LOBs
OBJECT_CACHE_MAX_SIZE_PERCENT
Default value 10
The object cache is a memory block on the client that allows applications to store
entire objects and to navigate among them without round trips to the server. OBJECT_
CACHE_MAX_SIZE_PERCENT specifies the percentage of the optimal cache size that
the session object cache can grow past the optimal size. The maximum size is equal to
the optimal size plus the product of this percentage and the optimal size. When the
cache size exceeds this maximum size, the system will attempt to shrink the cache to
the optimal size.
OBJECT_CACHE_OPTIMAL_SIZE
The object cache is a memory block on the client that allows applications to store
entire objects and to navigate among them without round trips to the server. OBJECT_
CACHE_OPTIMAL_SIZE specifies (in bytes) the size to which the session object cache
is reduced when the size of the cache exceeds the maximum size.
■ OLAP
OLAP_PAGE_POOL_SIZE
■ Optimizer
OPTIMIZER_DYNAMIC_SAMPLING
Default value If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2
If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1
If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0
OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed
by the optimizer.
OPTIMIZER_FEATURES_ENABLE
Syntax OPTIMIZER_FEATURES_ENABLE = { 8.0.0 | 8.0.3 | 8.0.4
| 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 |
8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 |
10.0.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.2.0.1}
OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series
of optimizer features based on an Oracle release number.
For example, if you upgrade your database from release 8.0.6 to release 9.2, but you
want to keep the release 8.0.6 optimizer behavior, you can do so by setting this
parameter to 8.0.6. At a later time, you can try the enhancements introduced in
releases up to and including release 9.2 by setting the parameter to 9.2.0.
OPTIMIZER_INDEX_CACHING
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. The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.
You can modify the optimizer's assumptions about index caching for nested loops
joins and IN-list iterators by setting this parameter to a value between 0 and 100 to
indicate the percentage of the index blocks the optimizer should assume are in the
cache. Setting this parameter to a higher value makes nested loops joins and IN-list
iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.
OPTIMIZER_INDEX_COST_ADJ
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.
The default for this parameter is 100 percent, at which the optimizer evaluates index
access paths at the regular cost. Any other value makes the optimizer evaluate the
access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
OPTIMIZER_MODE
Syntax OPTIMIZER_MODE =
{ first_rows_[1 | 10 | 100 | 1000] | first_rows |all_rows }
Default value all_rows
Values:
■ first_rows_n
The optimizer uses a cost-based approach and optimizes with a goal of best
response time to return the first n rows (where n = 1, 10, 100, 1000).
■ first_rows
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
■ all_rows
The optimizer uses a cost-based approach for all SQL statements in the session and
optimizes with a goal of best throughput (minimum resource use to complete the
entire statement).
OPTIMIZER_SECURE_VIEW_MERGING
OPTIMIZER_SECURE_VIEW_MERGING allows you to enable or disable view merging
globally for the database.
FALSE
Oracle does not use view merging or predicate move-around.
■ TRUE
Oracle assesses the query, considering all transformations, and chooses the
method with the lowest cost. To take advantage of query rewrite for a particular query, you must disable the OPTIMIZER_SECURE_VIEW_MERGING parameter.
QUERY_REWRITE_ENABLED
QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally
for the database.
Values:
■ false
Oracle does not use rewrite.
■ true
Oracle costs the query with rewrite and without rewrite and chooses the method
with the lower cost.
■ force
Oracle always uses rewrite and does not evaluate the cost before doing so. Use
force when you know that the query will always benefit from rewrite and when
reduction in compile time is important. To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.
QUERY_REWRITE_INTEGRITY
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.
Values:
■ enforced
Oracle enforces and guarantees consistency and integrity.
■ trusted
Oracle allows rewrites using relationships that have been declared, but that are not
enforced by Oracle.
■ stale_tolerated
Oracle allows rewrites using unenforced relationships. Materialized views are
eligible for rewrite even if they are known to be inconsistent with the underlying
detail data.
STAR_TRANSFORMATION_ENABLED
■ Parallel Execution
PARALLEL_ADAPTIVE_MULTI_USER
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.
The algorithm assumes that the system has been tuned for optimal performance in a
single-user environment.
Tables and hints use the default degree of parallelism.
PARALLEL_EXECUTION_MESSAGE_SIZE
PARALLEL_EXECUTION_MESSAGE_SIZE specifies the size of messages for parallel
execution (formerly referred to as parallel query, PDML, Parallel Recovery,
replication).
On most platforms, the default value is 2148bytes if PARALLEL_AUTOMATIC_TUNING
is set to false, and 4096 bytes if PARALLEL_AUTOMATIC_TUNING is set to true.
The default value is adequate for most applications. Larger values require a larger
shared pool. Larger values result in better performance at the cost of higher memory
use. For this reason, replication gets no benefit from increasing the size.
PARALLEL_MAX_SERVERS
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.
If you set this parameter too low, some queries may not have a parallel execution
process available to them during query processing. If you set it too high, memory
resource shortages may occur during peak periods, which can degrade performance.
PARALLEL_MIN_PERCENT
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.
Consider the following settings:
PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10
If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.
You can use this parameter in conjunction with PARALLEL_ADAPTIVE_MULTI_USER.
In a multi-user environment, an individual user or application can set PARALLEL_
MIN_PERCENT to a minimum value until sufficient resources are available on the
system and an acceptable degree of parallelism is returned.
PARALLEL_MIN_SERVERS
PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution
processes for the instance. This value is the number of parallel execution processes
Oracle creates when the instance is started.
PARALLEL_THREADS_PER_CPU
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.
The default is platform-dependent and is adequate in most cases. You should decrease
the value of this parameter if the machine appears to be overloaded when a
representative parallel query is executed. You should increase the value if the system is I/O bound.
■ PL/SQL
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
PLSQL_V2_COMPATIBILITY
REMOTE_DEPENDENCIES_MODE
UTL_FILE_DIR
■ PL/SQL Compiler
PLSQL_CCFLAGS
PLSQL_CODE_TYPE
PLSQL_DEBUG
PLSQL_OPTIMIZE_LEVEL
PLSQL_WARNINGS
NLS_LENGTH_SEMANTICS
■ SGA Memory
DB_nK_CACHE_SIZE
DB_CACHE_ADVICE
DB_CACHE_SIZE
HI_SHARED_MEMORY_ADDRESS
HI_SHARED_MEMORY_ADDRESS specifies the starting address at runtime of the system
global area (SGA). It is ignored on platforms that specify the SGA's starting address at linktime.
On 64-bit platforms, use HI_SHARED_MEMORY_ADDRESS to specify the high-order 32
bits of a 64-bit address. Use SHARED_MEMORY_ADDRESS to specify the low-order 32
bits of the address (see "SHARED_MEMORY_ADDRESS" on page 1-133). If both
parameters are 0 or unspecified, the SGA address defaults to a platform-specific
location.
JAVA_POOL_SIZE
LOCK_SGA
OLAP_PAGE_POOL_SIZE
PRE_PAGE_SGA
SGA_MAX_SIZE
SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.
SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is
specified, then the following memory pools are automatically sized:
■ Buffer cache (DB_CACHE_SIZE)
■ Shared pool (SHARED_POOL_SIZE)
■ Large pool (LARGE_POOL_SIZE)
■ Java pool (JAVA_POOL_SIZE)
■ Streams pool (STREAMS_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero values, then those
values are used as minimum levels by Automatic Shared Memory Management. You
would set minimum values if an application component needs a minimum amount of
memory to function properly.
The following pools are manually sized components and are not affected by
Automatic Shared Memory Management:
■ Log buffer
■ Other buffer caches, such as KEEP, RECYCLE, and other block sizes
■ Fixed SGA and other internal allocations
The memory allocated to these pools is deducted from the total available for SGA_
TARGET when Automatic Shared Memory Management computes the values of the
automatically tuned memory pools.
SGA_TARGET
SHARED_MEMORY_ADDRESS
■ Real Application Clusters
ACTIVE_INSTANCE_COUNT
ACTIVE_INSTANCE_COUNT enables you to designate one instance in a two-instance
cluster as the primary instance and the other instance as the secondary instance. This parameter has no functionality in a cluster with more than two instances.
When you set this parameter to 1, the first instance you start up becomes the primary
instance and accepts client connections. The second instance starts up as a secondary
instance and can accept client connections only if the first instance fails. In such an event, the secondary instance becomes the primary instance.
When the failed instance can once again be started up, it starts up as the secondary
instance, and will not accept client connections unless the current primary instance
fails.
CLUSTER_DATABASE
CLUSTER_DATABASE is a Real Application Clusters parameter that specifies whether
or not Real Application Clusters is enabled.
CLUSTER_DATABASE_INSTANCES
CLUSTER_DATABASE_INSTANCES is a 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 Real Application Clusters environment. A proper setting for this parameter can improve memory use.
Oracle uses the value of this parameter to compute the default value of the LARGE_
POOL_SIZE parameter when the PARALLEL_AUTOMATIC_TUNING parameter is set to
true. Note that the PARALLEL_AUTOMATIC_TUNIG parameter has been deprecated.
CLUSTER_INTERCONNECTS
GC_FILES_TO_LOCKS
Syntax GC_FILES_TO_LOCKS =
'{file_list=lock_count[!blocks][EACH][:...]}'
Spaces are not allowed within the quotation marks.
■ file_list
One or more datafiles listed by their file numbers, or ranges of file numbers, with
comma separators:
filenumber[-filenumber][,filenumber[-filenumber]]...
To find the correspondence between filenames and file numbers, query the FILE_
NAME and FILE_ID columns of the DBA_DATA_FILES data dictionary view.
■ lock_count
The number of PCM locks assigned to file_list. By default these locks are
fixed. If you set lock_count to 0, then Oracle uses fine-grain locking for these
files and takes locks as needed from the pool of releasable locks.
■ blocks
Specifies the number of contiguous blocks covered by one lock. The default is
noncontiguous blocks.
■ EACH
Indicates that each datafile in file_list is assigned a separate set of lock_
count PCM locks.
GCS_SERVER_PROCESSES
Default value If CLUSTER_DATABASE is set to true, then 2
If CLUSTER_DATABASE is set to false, then 0
GCS_SERVER_PROCESSES specifies the initial number of server processes in Global
Cache Service to serve the inter-instance traffic among Real Application Clusters
instances.
INSTANCE_GROUPS
INSTANCE_GROUPS is a Real Application Clusters parameter that you can specify
only in parallel mode. Used in conjunction with the PARALLEL_INSTANCE_GROUP
parameter, it lets you restrict parallel query operations to a limited number of
instances.
INSTANCE_NUMBER
MAX_COMMIT_PROPAGATION_DELAY
MAX_COMMIT_PROPAGATION_DELAY is a Real Application Clusters parameter. This
initialization parameter should not be changed except under a limited set of
circumstances specific to the cluster database.
PARALLEL_INSTANCE_GROUP
PARALLEL_INSTANCE_GROUP is a 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.
This parameter identifies the parallel instance group Oracle will use for spawning
parallel execution processes. Parallel operations will spawn parallel execution
processes only on instances that specify a matching group in their INSTANCE_GROUPS
parameter. If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a group that does not exist, then the operation runs serially. No parallelism is used.
THREAD
THREAD is a Real Application Clusters parameter that specifies the number of the redo
thread to be used by an instance. When you create a database, Oracle creates and enables thread 1 as a public thread (one that can be used by any instance). You must create and enable subsequent threads using the ADD LOGFILE THREAD clause and ENABLE THREAD clause of the ALTER DATABASE statement. The number of threads you create is limited by the MAXINSTANCES parameter specified in the CREATE DATABASE statement.
In exclusive mode, thread 1 is the default thread. However, you can specify THREAD
for an instance running in exclusive mode if you want to use the redo log files in a
thread other than thread 1.
In parallel mode, you can specify any available redo thread number, as long as that
thread number is enabled and is not in use by another instance.
A value of zero specifies that this instance can use any available, enabled public
thread.
■ Redo Logs, Archiving, and Recovery
CONTROL_FILE_RECORD_KEEP_TIME
CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before
a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed.
Note: This parameter applies only to records in the control file that
are circularly reusable (such as archive log records and various
backup records). It does not apply to records such as datafile,
tablespace, and redo thread records, which are never reused unless the
corresponding object is dropped from the tablespace.
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
FAST_START_MTTR_TARGET
LOG_ARCHIVE_CONFIG
Syntax LOG_ARCHIVE_CONFIG =
{
[ SEND | NOSEND ]
[ RECEIVE | NORECEIVE ]
[ DG_CONFIG=(remote_db_unique_name1 [, ... remote_db_
unique_name9) | NODG_CONFIG ]
}
Default value - 'SEND, RECEIVE, NODG_CONFIG'
■ SEND
Enables the sending of redo logs to remote destinations
■ NOSEND
Disables the sending of redo logs to remote destinations
■ RECEIVE
Enables the receipt of remotely archived redo logs
■ NORECEIVE
Disables the receipt of remotely archived redo logs
■ DG_CONFIG
Specifies a list of up to 9 unique database names (defined with the DB_UNIQUE_
NAME initialization parameter) for all of the databases in the Data Guard
configuration.
■ NODG_CONFIG
Eliminates the list of service provider names previously specified with the DG_
CONFIG option.
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
LOG_ARCHIVE_DUPLEX_DEST
LOG_ARCHIVE_DUPLEX_DEST is similar to the initialization parameter LOG_
ARCHIVE_DEST. This parameter specifies a second archive destination: the duplex
archive destination. This duplex archive destination can be either a must-succeed or a best-effort archive destination, depending on how many archive destinations must
succeed (as specified in the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter).
The default setting of a null string ("") or (' ') indicates that a duplex archive
destination does not exist.
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_FORMAT is applicable only if you are using the redo log in
ARCHIVELOG mode. Use a text string and variables to specify the default filename
format when archiving redo log files. The string generated from this format is
appended to the string specified in the LOG_ARCHIVE_DEST parameter.
The following variables can be used in the format:
%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files
across multiple incarnations of the database
Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros. An example of specifying the archive redo log filename format follows:
LOG_ARCHIVE_FORMAT = 'log%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES
Range of values 1 to 30
LOG_ARCHIVE_MIN_SUCCEED_DEST
Range of values 1 to 10 if you are using LOG_ARCHIVE_DEST_n
1 or 2 if you are using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
LOG_ARCHIVE_TRACE
Range of values 0, 1, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024, 2048, 4096, 8192
The valid values have the following meanings:
■ 0: Disable archivelog tracing (this is the default)
■ 1: Track archival of redo log file
■ 2: Track archival status of each archivelog destination
■ 4: Track archival operational phase
■ 8: Track archivelog destination activity
■ 16: Track detailed archivelog destination activity
■ 32: Track archivelog destination parameter modifications
■ 64: Track ARCn process state activity
■ 128: Track FAL (fetch archived log) server related activities
■ 256: Track RFS Logical Client
■ 512: Track LGWR redo shipping network activity
■ 1024: Track RFS Physical Client
■ 2048: Track RFS/ARCn Ping Heartbeat
■ 4096: Track Real Time Apply
■ 8192: Track Redo Apply (Media Recovery or Physical Standby)
LOG_BUFFER
Default value 512 KB or 128 KB * CPU_COUNT, whichever is greater
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.
In general, larger values for LOG_BUFFER reduce redo log file I/O, particularly if
transactions are long or numerous. In a busy system, a value 65536 or higher is
reasonable.
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the
number of redo log file blocks that can exist between an incremental checkpoint and
the last block written to the redo log. This number refers to physical operating system blocks, not database blocks. Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. Therefore, if the value exceeds the actual redo log file size, checkpoints occur only when switching logs. Checkpoint frequency is one of the factors that influence the time required for the database to recover from an unexpected failure.
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINT_TIMEOUT specifies (in seconds) the amount of time that has
passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds. Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set.
LOG_CHECKPOINTS_TO_ALERT
LOG_CHECKPOINTS_TO_ALERT lets you log your checkpoints to the alert file. Doing
so is useful for determining whether checkpoints are occurring at the desired
frequency.
RECOVERY_PARALLELISM
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.
■ Resource Manager
RESOURCE_LIMIT
RESOURCE_LIMIT determines whether resource limits are enforced in database
profiles.
Values:
■ TRUE
Enables the enforcement of resource limits
■ FALSE
Disables the enforcement of resource limits
RESOURCE_MANAGER_PLAN
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.
You can change the setting of this parameter using the ALTER SYSTEM statement to
turn on the resource manager (if it was previously off) or to turn off the resource
manager or change the current plan (if it was previously on). If you specify a plan that does not exist in the data dictionary, Oracle returns an error message.
■ Security and Auditing
AUDIT_FILE_DEST
AUDIT_SYS_OPERATIONS
AUDIT_SYSLOG_LEVEL
AUDIT_SYSLOG_LEVEL enables OS audit logs to be written to the system via the
SYSLOG utility if the AUDIT_TRAIL parameter is set to os.
The value of facility can be any of the following: USER, LOCAL0-LOCAL7, SYSLOG,
DAEMON, KERN, MAIL, AUTH, LPR, NEWS, UUCP or CRON.
The value of level can be any of the following: NOTICE, INFO, DEBUG, WARNING, ERR,
CRIT, ALERT, EMERG . If you use this parameter, it is best to assign a file corresponding to every facility.level combination (especially kern.emerg) in syslog.conf . Sometimes these are assigned to print to the console in the default syslog.conf file. This can become annoying and will be useless as audit logs.
Also, if you use this parameter, it is best to set the maximum length of syslog messages in the system to 512 bytes.
AUDIT_TRAIL
AUDIT_TRAIL enables or disables database auditing.
Values:
■ none
Disables database auditing.
■ os
Enables database auditing and directs all audit records to the operating system's
audit trail.
■ db
Enables database auditing and directs all audit records to the database audit trail
(the SYS.AUD$ table).
■ db,extended
Enables database auditing and directs all audit records to the database audit trail
(the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB
columns of the SYS.AUD$ table.
■ xml
Enables database auditing and writes all audit records to XML format OS files.
■ xml,extended
Enables database auditing and prints all columns of the audit trail, including
SqlText and SqlBind values.
You can use the SQL statement AUDIT to set auditing options regardless of the setting
of this parameter.
O7_DICTIONARY_ACCESSIBILITY
O7_DICTIONARY_ACCESSIBILITY 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 the SYS schema.
For example, if O7_DICTIONARY_ACCESSIBILITY is set to false, then the SELECT
ANY TABLE privilege allows access to views or tables in any schema except the SYS
schema (data dictionary tables cannot be accessed). The system privilege EXECUTE
ANY PROCEDURE allows access on the procedures in any schema except the SYS
schema. If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges. The following roles, which can be granted to the database administrator, also allow access to dictionary objects:
■ SELECT_CATALOG_ROLE
■ EXECUTE_CATALOG_ROLE
■ DELETE_CATALOG_ROLE
OS_AUTHENT_PREFIX
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.
The default value of this parameter is OPS$ for backward compatibility with previous
versions. However, you might prefer to set the prefix value to "" (a null string), thereby eliminating the addition of any prefix to operating system account names.
OS_ROLES
Values:
■ TRUE
The operating system completely manages the role grants for all database
usernames. When a user attempts to create a session, the username's security
domain is initialized using the roles identified by the operating system. A user can
subsequently enable as many roles identified by the operating system as specified
by the parameter MAX_ENABLED_ROLES.
Revocation by Oracle of roles granted by the operating system is ignored, as are
any roles previously granted by Oracle.
■ FALSE
Oracle identifies and manages the roles.
RDBMS_SERVER_DN
REMOTE_LOGIN_PASSWORDFILE
REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file.
Values:
■ NONE
Oracle ignores any password file. Therefore, privileged users must be
authenticated by the operating system.
■ SHARED
One or more databases can use the password file. The password file can contain
SYS as well as non-SYS users.
Note:-
The value EXCLUSIVE is supported for backward
compatibility. It now has the same behavior as the value SHARED.
REMOTE_OS_AUTHENT
REMOTE_OS_AUTHENT specifies whether remote clients will be authenticated with the
value of the OS_AUTHENT_PREFIX parameter.
REMOTE_OS_ROLES
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.
SQL92_SECURITY
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 specifies whether users must have been granted the SELECT object
privilege in order to execute such UPDATE or DELETE statements.
■ Sessions and Processes
CPU_COUNT
PROCESSES
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.
The default values of the SESSIONS and TRANSACTIONS parameters are derived from
this parameter. Therefore, if you change the value of PROCESSES, you should evaluate
whether to adjust the values of those derived parameters.
SESSIONS
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.
Oracle uses the default value of this parameter as its minimum. Values between 1 and
the default do not trigger errors, but Oracle ignores them and uses the default instead.
The default values of the ENQUEUE_RESOURCES and TRANSACTIONS parameters are
derived from SESSIONS. Therefore, if you increase the value of SESSIONS, you
should consider whether to adjust the values of ENQUEUE_RESOURCES and
TRANSACTIONS as well. (Note that ENQUEUE_RESOURCES is obsolete as of Oracle
Database 10g release 2 (10.2).)
In a shared server environment, the value of PROCESSES can be quite small. Therefore,
Oracle recommends that you adjust the value of SESSIONS to approximately 1.1 *
total number of connections.
■ Shared Server Architecture
CIRCUITS
DISPATCHERS
Syntax DISPATCHERS = 'dispatch_clause'
dispatch_clause::=
(PROTOCOL = protocol) |
(ADDRESS = address) |
(DESCRIPTION = description )
[options_clause]
options_clause::=
(DISPATCHERS = integer |
SESSIONS = integer |
CONNECTIONS = integer |
TICKS = seconds |
POOL = {1 | ON | YES | TRUE | BOTH |
({IN | OUT} = ticks) | 0 | OFF | NO | FALSE |
ticks} |
MULTIPLEX = {1 | ON | YES | TRUE |
0 | OFF | NO | FALSE | BOTH | IN | OUT} |
LISTENER = tnsname |
SERVICE = service |
INDEX = integer)
Default value If SHARED_SERVERS is greater than 0, then DISPATCHERS defaults to
'(PROTOCOL=tcp)', causing 1 TCP/IP dispatcher to be created.
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)' Attributes may be specified using the full attribute name or any substring beginning with the first 3 characters. For example, SESSIONS can be specified as SES, SESS, SESSI, and so on.
Specify only one of the following attributes: PROTOCOL, ADDRESS, or DESCRIPTION.
If you specify either ADDRESS or DESCRIPTION, then you can specify additional
network attributes. Doing so supports multi-homed hosts.
MAX_DISPATCHERS
MAX_DISPATCHERS specifies the maximum number of dispatcher processes allowed
to be running simultaneously. It can be overridden by the DISPATCHERS parameter
and is maintained for backward compatibility with older releases.
MAX_SHARED_SERVERS
MAX_SHARED_SERVERS specifies the maximum number of shared server processes
allowed to be running simultaneously. Setting this parameter enables you to reserve
process slots for other processes, such as dedicated servers.
When you want to reduce the range of shared servers, you can reduce MAX_SHARED_
SERVERS before reducing SHARED_SERVERS. If MAX_SHARED_SERVERS is lower than
SHARED_SERVERS, then the number of shared servers will not vary but will remain at
the constant level specified by SHARED_SERVERS. If MAX_SHARED_SERVERS is not
specified, then a shared server process may be spawned as long as the number of free
process slots is greater than 1 / 8 the maximum number of processes, or 2 if
PROCESSES is less than 24.
SHARED_SERVER_SESSIONS
SHARED_SERVER_SESSIONS specifies the total number of shared server sessions to
allow. Setting this parameter enables you to reserve user sessions for dedicated
servers.
SHARED_SERVERS
SHARED_SERVERS specifies the number of server processes that you want to create
when an instance is started. If system load decreases, then this minimum number of
servers is maintained. Therefore, you should take care not to set SHARED_SERVERS
too high at system startup.
■ Standby Database
ARCHIVE_LAG_TARGET
ARCHIVE_LAG_TARGET limits the amount of data that can be lost and effectively
increases the availability of the standby database by forcing a log switch after the
specified amount of time elapses.
A 0 value disables the time-based thread advance feature; otherwise, the value
represents the number of seconds. Values larger than 7200 seconds are not of much use
in maintaining a reasonable lag in the standby database. The typical, or recommended
value is 1800 (30 minutes). Extremely low values can result in frequent log switches,
which could degrade performance; such values can also make the archiver process too
busy to archive the continuously generated logs.
DB_FILE_NAME_CONVERT
DB_UNIQUE_NAME
DB_UNIQUE_NAME specifies a globally unique name for the database. Databases with
the same DB_NAME within the same DB_DOMAIN (for example, copies of a database
created for reporting or a physical standby) must have a unique DB_UNIQUE_NAME.
Every database's DB_UNIQUE_NAME must be unique within the enterprise.
The value of DB_UNIQUE_NAME can be up to 30 characters and is case insensitive. The
following characters are valid in a database name: alphanumeric characters,
underscore (_), number sign (#), and dollar sign ($).
DG_BROKER_CONFIG_FILEn
DG_BROKER_CONFIG_FILEn (where n = 1, 2) specifies the names for the Data Guard
broker configuration files. Every database that is part of a Data Guard broker configuration has two broker configuration files, which contain entries that describe the state and properties of the configuration (such as the sites and databases that are part of the configuration, the roles and properties of each of the databases, and the state of each of the elements of the configuration). Two files are provided so as to always maintain the last known good state of the configuration.
If DG_BROKER_CONFIG_FILEn is not explicitly defined, then it is set to an operating
system-specific default value at instance startup. The parameter can only be altered
when the DMON (Data Guard broker) process is not running.
DG_BROKER_START
DG_BROKER_START enables Oracle to determine whether or not the Data Guard
broker (DMON) 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.
If the site is never going to be configured in a Data Guard broker configuration, then you can leave the parameter unspecified and accept the default value of false. If the site is part of a Data Guard broker configuration, then automatic startup of the DMON process is simplified by setting DG_BROKER_START to true in the initialization parameter file.
FAL_CLIENT
FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL
service, configured through the FAL_SERVER parameter, to refer to the FAL client. The
value is an Oracle Net service name, which is assumed to be configured properly on
the FAL server system to point to the FAL client (standby database).
Given the dependency of FAL_CLIENT on FAL_SERVER, the two parameters should
be configured or changed at the same time.
FAL_SERVER
FAL_SERVER specifies the FAL (fetch archive log) server for a standby database. The
value is an Oracle Net service name, which is assumed to be configured properly on
the standby database system to point to the desired FAL server.
LOG_FILE_NAME_CONVERT
STANDBY_ARCHIVE_DEST
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. You can see the value of this parameter by querying the V$ARCHIVE_DEST data dictionary view
STANDBY_FILE_MANAGEMENT
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.
Values:
■ MANUAL
disables automatic standby file management
■ AUTO
enables automatic standby file management
Setting STANDBY_FILE_MANAGEMENT to AUTO causes Oracle to automatically create
files on the standby database and, in some cases, overwrite existing files. Care must be taken when setting STANDBY_FILE_MANAGEMENT and DB_FILE_NAME_CONVERT so
that existing standby files will not be accidentally overwritten.
If the standby database is on the same system as the primary database, then ensure
that the primary and standby systems do not point to the same files.
■ Temporary Sort Space
BITMAP_MERGE_AREA_SIZE
CREATE_BITMAP_AREA_SIZE
HASH_AREA_SIZE
PGA_AGGREGATE_TARGET
PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all
server processes attached to the instance.
Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically
setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL
working areas used by memory-intensive SQL operators (such as sort, group-by,
hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero
value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.
Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_
POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_
AREA_SIZE parameters.
Oracle attempts to keep the amount of private memory below the target specified by
this parameter by adapting the size of the work areas to private memory. When
increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.
When setting this parameter, you should examine the total memory on your system
that is available to the Oracle instance and subtract the SGA. You can assign the
remaining memory to PGA_AGGREGATE_TARGET.
SORT_AREA_RETAINED_SIZE
SORT_AREA_SIZE
WORKAREA_SIZE_POLICY
■ Transactions
COMMIT_WRITE
COMMIT_WRITE is an advanced parameter used to control how redo for transaction
commits is written to the redo logs. The IMMEDIATE and BATCH options control how
redo is batched by Log Writer. The WAIT and NOWAIT options control when the redo
for a commit is flushed to the redo logs.
DML_LOCKS
A DML lock is a lock obtained on a table that is undergoing a DML operation (insert,
update, delete). DML_LOCKS specifies the maximum number of DML locks—one for
each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.
The default value assumes an average of four tables referenced for each transaction.
For some systems, this value may not be enough.
Enqueues are shared memory structures that serialize access to database resources. If
you set the value of DML_LOCKS to 0, enqueues are disabled and performance is
slightly increased. However, you should be aware of the following restrictions when
you set you DML_LOCKS to 0:
■ You cannot use DROP TABLE, CREATE INDEX statements.
■ You cannot use explicit lock statements such as LOCK TABLE IN EXCLUSIVE
MODE.
■ Enterprise Manager cannot run on any instances for which DML_LOCKS is set to 0
Oracle holds more locks during parallel DML than during serial execution. Therefore,
if your database supports a lot of parallel DML, you may need to increase the value of this parameter.
FAST_START_PARALLEL_ROLLBACK
FAST_START_PARALLEL_ROLLBACK determines the maximum number of processes
that can exist for performing parallel rollback. This parameter is useful on systems in which some or all of the transactions are long running.
Values:
■ FALSE indicates that parallel rollback is disabled
■ LOW limits the number of rollback processes to 2 * CPU_COUNT
■ HIGH limits the number of rollback processes to 4 * CPU_COUNT
TRANSACTIONS
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.
■ Undo Management
RESUMABLE_TIMEOUT
RESUMABLE_TIMEOUT enables or disables resumable statements and specifies
resumable timeout at the system level.
ROLLBACK_SEGMENTS
Syntax ROLLBACK_SEGMENTS =
(segment_name [, segment_name] ... )
Default value If you do not specify this parameter, the instance uses public rollback
segments by default, unless the UNDO_MANAGEMENT initialization
parameter is set to AUTO. In that case, the ROLLBACK_SEGMENTS
parameter is ignored and automatic undo management is used.
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).
You cannot change the value of this parameter dynamically, but you can change its
value and then restart the instance. Although this parameter usually specifies private rollback segments, it can also specify public rollback segments if they are not already in use.
To find the name, segment ID number, and status of each rollback segment in the
database, query the data dictionary view DBA_ROLLBACK_SEGS.
When UNDO_MANAGEMENT is set to AUTO, ROLLBACK_SEGMENTS is ignored.
TRANSACTIONS_PER_ROLLBACK_SEGMENT
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.
You can acquire more rollback segments by naming them in the parameter
ROLLBACK_SEGMENTS.
UNDO_MANAGEMENT
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
UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention.
For AUTOEXTEND undo tablespaces, the system retains undo for at least the time
specified in this parameter, and automatically tunes the undo retention period to
satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the
system automatically tunes for the maximum possible undo retention period, based on
undo tablespace size and usage history, and ignores UNDO_RETENTION unless
retention guarantee is enabled.
The setting of this parameter should account for any flashback requirements of the
system. Automatic tuning of undo retention is not supported for LOBs. The
RETENTION value for LOB columns is set to the value of the UNDO_RETENTION
parameter.
The UNDO_RETENTION parameter can only be honored if the current undo tablespace
has enough space. If an active transaction requires undo space and the undo
tablespace does not have available space, then the system starts reusing unexpired
undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.
The amount of time for which undo is retained for the Oracle Database for the current
undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of
the V$UNDOSTAT dynamic performance view.
UNDO_TABLESPACE
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, then an error will occur and startup will fail.
If the UNDO_TABLESPACE parameter is omitted, the first available undo tablespace in
the database is chosen. If no undo tablespace is available, the instance will start
without an undo tablespace. In such cases, user transactions will be executed using the SYSTEM rollback segment. You should avoid running in this mode under normal
circumstances.
You can replace an undo tablespace with another undo tablespace while the instance is
running.
Others:-
UTL_FILE_DIR
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.
All users can read or write to all files specified by this parameter. Therefore all
PL/SQL users must be trusted with the information in the directories specified by this parameter.
FILEIO_NETWORK_ADAPTERS
FILEIO_NETWORK_ADAPTERS specifies a list of network adapters that can be used to
access the disk storage. On platforms where the database files reside in network
attached storage, this parameter provides the storage access library the list of network adapters that can be used to access the storage.
The netrwork adapter name is a fully qualified address name of the network card that
can be accessed through the host name database or using the Network Information
Service. The components of the adapter name are separated by periods. For example,
the following is a fully qualified adapter name:
ib1.oracle.com
SKIP_UNUSABLE_INDEXES
SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of tables with
unusable indexes or index partitions.
Values:
■ true
Disables error reporting of indexes and index partitions marked UNUSABLE. This
setting allows all operations (inserts, deletes, updates, and selects) on tables with
unusable indexes or index partitions.
Note: If an index is used to enforce a UNIQUE constraint on a table,
then allowing insert and update operations on the table might violate
the constraint. Therefore, this setting does not disable error reporting
for unusable indexes that are unique.
■ false
Enables error reporting of indexes marked UNUSABLE. This setting does not
allow inserts, deletes, and updates on tables with unusable indexes or index
partitions.
SQL_TRACE
SQL_TRACE enables or disables 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.
Caution: Using this initialization parameter to enable the SQL trace
facility for the entire instance can have a severe performance impact.
Enable the facility for specific sessions using the ALTER SESSION
statement. If you must enable the facility on an entire production
environment, then you can minimize performance impact by:
■ Maintaining at least 25% idle CPU capacity
■ Maintaining adequate disk space for the USER_DUMP_DEST
location
■ Striping disk space over sufficient disks
Note: This parameter is deprecated. Oracle recommends that you
use the DBMS_MONITOR and DBMS_SESSION packages instead. SQL_
TRACE is retained for backward compatibility only.
SQLTUNE_CATEGORY
SQLTUNE_CATEGORY specifies the category name for use by sessions to qualify the
lookup of SQL profiles during SQL compilation.
Asm Instance Parameters
ASM_DISKGROUPS
ASM_DISKGROUPS specifies a list of names of disk groups to be mounted by an
Automatic Storage Management instance at instance startup or when an ALTER
DISKGROUP ALL MOUNT statement is issued.
Automatic Storage Management (ASM) automatically adds a disk group to this
parameter when the disk group is successfully created or mounted, and automatically
removes a disk group from this parameter when the disk group is dropped or
dismounted.
Issuing the ALTER DISKGROUP...ALL MOUNT or ALTER DISKGROUP...ALL
DISMOUNT command does not affect the value of this parameter.
ASM_DISKSTRING
ASM_DISKSTRING specifies an operating system-dependent value used by Automatic
Storage Management to limit the set of disks considered for discovery. When a new
disk is added to a disk group, each Automatic Storage Management instance that has
the disk group mounted must be able to discover the new disk using the value of
ASM_DISKSTRING.
In most cases, the default value will be sufficient. Using a more restrictive value may reduce the time required for Automatic Storage Management to perform discovery,
and thus improve disk group mount time or the time for adding a disk to a disk group.
A "?" at the beginning of the string gets expanded to the Oracle home directory.
Depending on the operating system, wildcard characters can be used.
It may be necessary to dynamically change ASM_DISKSTRING before adding a disk so
that the new disk will be discovered.
An attempt to dynamically modify ASM_DISKSTRING will be rejected and the old
value retained if the new value cannot be used to discover a disk that is in a disk
group that is already mounted.
ASM_POWER_LIMIT
ASM_POWER_LIMIT specifies the maximum power on an Automatic Storage
Management instance for disk rebalancing. The higher the limit, the faster rebalancing will complete. Lower values will take longer, but consume fewer processing and I/O resources.
If the POWER clause of a rebalance operation is not specified, then the default power
will be the value of ASM_POWER_LIMIT.
BLANK_TRIMMING
BLANK_TRIMMING specifies the data assignment semantics of character datatypes.
Values:
■ TRUE
Allows the data assignment of a source character string or variable to a destination
character column or variable even though the source length is longer than the
destination length. In this case, however, the additional length over the destination
length is all blanks. This value complies with the semantics of SQL92 Transitional
Level and above.
■ FALSE
Disallows the data assignment if the source length is longer than the destination
length and reverts to SQL92 Entry Level semantics.
■ Backup and Restore
BACKUP_TAPE_IO_SLAVES
BACKUP_TAPE_IO_SLAVES specifies whether I/O server processes (also called
slaves) are used by 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.
Note: You cannot perform duplexed backups unless you enable this
parameter. Otherwise, Oracle returns an error. When this parameter is
enabled, Recovery Manager will configure as many server processes
as needed for the number of backup copies requested.
RECYCLEBIN
TAPE_ASYNCH_IO
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 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.
■ BFILEs
SESSION_MAX_OPEN_FILES
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.
■ Buffer Cache and I/O
DB_nK_CACHE_SIZE
DB_BLOCK_BUFFERS
DB_BLOCK_SIZE
DB_CACHE_ADVICE
DB_CACHE_SIZE
DB_FILE_MULTIBLOCK_READ_COUNT
DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to
minimize I/O during table scans. It specifies the maximum number of blocks read in
one I/O operation during a sequential scan. The total number of I/Os needed to
perform a full table scan depends on such factors as the size of the table, the
multiblock read count, and whether parallel execution is being utilized for the
operation.
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_WRITER_PROCESSES
Default value : 1 or CPU_COUNT / 8, whichever is greater
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.
DBWR_IO_SLAVES
DBWR_IO_SLAVES is relevant only on systems with only one database writer process
(DBW0). It specifies the number of I/O server processes used by the DBW0 process. The
DBW0 process and its server processes always write to disk. By default, the value is 0 and I/O server processes are not used.
If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server processes
used by the ARCH and LGWR processes is set to 4. However, the number of I/O server
processes used by Recovery Manager is set to 4 only if asynchronous I/O is disabled
(either your platform does not support asynchronous I/O or disk_asynch_io is set
to false).
Typically, I/O server processes are used to simulate asynchronous I/O on platforms
that do not support asynchronous I/O or that implement it inefficiently. However, you
can use I/O server processes even when asynchronous I/O is being used. In that case
the I/O server processes will use asynchronous I/O.
I/O server processes are also useful in database environments with very large I/O
throughput, even if asynchronous I/O is enabled.
DISK_ASYNCH_IO
DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is
asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans). If your platform supports asynchronous I/O to disk, Oracle 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.
If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES to
a value other than its default of zero in order to simulate asynchronous I/O.
FILESYSTEMIO_OPTIONS
Syntax FILESYSTEMIO_OPTIONS = { none | setall | directIO | asynch }
FILESYSTEMIO_OPTIONS specifies I/O operations for file system files.
READ_ONLY_OPEN_DELAYED
READ_ONLY_OPEN_DELAYED determines when datafiles in read-only tablespaces are
accessed.
Values:
■ true
The datafiles are accessed for the first time only when an attempt is made to read
data stored within them.
■ false
The datafiles are accessed at database open time.
USE_INDIRECT_DATA_BUFFERS
■ Cursors and Library Cache
CURSOR_SHARING
CURSOR_SHARING determines what kind of SQL statements can share the same
cursors.
Values:
■ FORCE
Forces statements that may differ in some literals, but are otherwise identical, to
share a cursor, unless the literals affect the meaning of the statement.
■ SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to
share a cursor, unless the literals affect either the meaning of the statement or the
degree to which the plan is optimized.
■ EXACT
Only allows statements with identical text to share the same cursor.
Note: Forcing cursor sharing among similar (but not identical)
statements can have unexpected results in some DSS applications, or
applications that use stored outlines.
CURSOR_SPACE_FOR_TIME
CURSOR_SPACE_FOR_TIME lets you use more space for cursors in order to save time.
It affects both the shared SQL area and the client's private SQL area.
Values:
■ TRUE
Shared SQL areas are kept pinned in the shared pool. As a result, shared SQL areas
are not aged out of the pool as long as an open cursor references them. Because
each active cursor's SQL area is present in memory, execution is faster. However,
the shared SQL areas never leave memory while they are in use. Therefore, you
should set this parameter to TRUE only when the shared pool is large enough to
hold all open cursors simultaneously.
In addition, a setting of TRUE retains the private SQL area allocated for each cursor
between executions instead of discarding it after cursor execution, saving cursor
allocation and initialization time
■ FALSE
Shared SQL areas can be deallocated from the library cache to make room for new
SQL statements.
OPEN_CURSORS
OPEN_CURSORS specifies the maximum number of open cursors (handles to private
SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.
It is important to set the value of OPEN_CURSORS high enough to prevent your
application from running out of open cursors. The number will vary from one
application to another. Assuming that a session does not open the number of cursors
specified by OPEN_CURSORS, there is no added overhead to setting this value higher
than actually needed.
SESSION_CACHED_CURSORS
SESSION_CACHED_CURSORS specifies 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.
This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL
uses to avoid having to reparse as statements are re-executed by a user.
■ Database/Instance Identification
DB_DOMAIN
In a distributed database system, DB_DOMAIN specifies the logical location of the
database within the network structure. You should set this parameter if this database is or ever will be part of a distributed system. The value consists of the extension components of a global database name, consisting of valid identifiers (any
alphanumeric ASCII characters), separated by periods. Oracle recommends that you
specify DB_DOMAIN as a unique string for all databases in a domain.
This parameter allows one department to create a database without worrying that it
might have the same name as a database created by another department. If one sales
department's DB_DOMAIN is JAPAN.ACME.COM, then their SALES database
(SALES.JAPAN.ACME.COM) is uniquely distinguished from another database with
DB_NAME = SALES but with DB_DOMAIN = US.ACME.COM.
If you omit the domains from the name of a database link, Oracle expands the name by
qualifying the database with the domain of your local database as it currently exists in the data dictionary, and then stores the link name in the data dictionary. The characters valid in a database domain name are: alphanumeric characters, underscore (_), and number sign (#).
DB_NAME
INSTANCE_NAME
■ Diagnostics and Statistics
BACKGROUND_CORE_DUMP
BACKGROUND_DUMP_DEST
CORE_DUMP_DEST
DB_BLOCK_CHECKING
DB_BLOCK_CHECKING controls whether or not Oracle performs block checking for
database blocks. The checking that is performed depends on the value you supply, as
follows:
■ OFF - no block checking is performed for blocks in the user tablespaces. However,
semantic block checking for SYSTEM tablespace blocks is always turned on.
■ LOW - basic block header checks are performed after block contents change in
memory (for example, after UPDATE or INSERT statements, on-disk reads, or
inter-instance block transfers in RAC)
■ MEDIUM - all LOW checks are performed, as well as semantic block checking for all
non-index-organized table blocks
■ FULL - all LOW and MEDIUM checks are performed, as well as semantic checks for
index blocks (that is, blocks of subordinate objects that can actually be dropped
and reconstructed when faced with corruption) Oracle checks a block by going through the data in the block, making sure it is logically self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload and the parameter value. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable. For backward compatibility the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.
DB_BLOCK_CHECKSUM
DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a
checksum (a number calculated from all the bytes stored in the block) and store it in
the cache header of every data block when writing it to disk. Checksums are verified
when a block is read - only if this parameter is TYPICAL or FULL and the last write of the block stored a checksum. In FULL mode, Oracle also verifies the checksum before a change application from update/delete statements and recomputes it after the change is applied. In addition, Oracle gives every log block a checksum before writing it to the current log.
If this parameter is set to OFF, DBWn calculates checksums only for the SYSTEM
tablespace, but not for user tablespaces. Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. If set to FULL, DB_BLOCK_CHECKSUM also catches in-memory corruptions and stops them from making it to the disk. Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. In the FULL mode it causes 4% to 5% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL. For backward compatibility we preserve use of TRUE (implying TYPICAL) and FALSE (implying OFF) values.
EVENT
EVENT is a parameter used only to debug the system. Do not alter the value of this
parameter except under the supervision of Oracle Support Services staff.
MAX_DUMP_FILE_SIZE
MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert
file). Change this limit if you are concerned that trace files may use too much space.
SHADOW_CORE_DUMP
STATISTICS_LEVEL
STATISTICS_LEVEL specifies the level of collection for database and operating
system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.
The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The
default value should be adequate for most environments.
When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added
to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.
Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many
of the important statistics required by Oracle Database features and functionality,
including:
■ Automatic Workload Repository (AWR) Snapshots
■ Automatic Database Diagnostic Monitor (ADDM)
■ All server-generated alerts
■ Automatic SGA Memory Management
■ Automatic optimizer statistics collection
■ Object level statistics
■ End to End Application Tracing (V$CLIENT_STATS)
■ Database time distribution statistics (V$SESS_TIME_MODEL
and V$SYS_TIME_MODEL)
■ Service level statistics
■ Buffer cache advisory
■ MTTR advisory
■ Shared pool sizing advisory
■ Segment level statistics
■ PGA Target advisory
■ Timed statistics
■ Monitoring of statistics
Note: Oracle strongly recommends that you do not disable these
important features and functionality.
TIMED_OS_STATISTICS
TIMED_OS_STATISTICS specifies (in seconds) the interval at which Oracle collects
operating system statistics when a request is made from the client to the server or
when a request completes.
■ On dedicated servers, Oracle collects operating system statistics at user logon and
after each subsequent client invocation through the OCI into the Oracle server as a
remote procedure call message.
■ On shared servers, Oracle collects statistics when client calls to Oracle are
processed.
A value of zero specifies that operating system statistics are not gathered. To collect statistics, set a value meaningful for your application and site needs.
Note: Gathering operating system statistics is very expensive. Oracle
recommends that you set this parameter in an ALTER SYSTEM statement rather than in the initialization parameter file, and that you reset the value to zero as soon as the needed statistics have been gathered.
TIMED_STATISTICS
TIMED_STATISTICS specifies whether or not statistics related to time are collected.
Values:
■ true
The statistics are collected and stored in trace files or displayed in the
V$SESSTATS and V$SYSSTATS dynamic performance views.
■ false
The value of all time-related statistics is set to zero. This setting lets Oracle avoid the overhead of requesting the time from the operating system. Normally, TIMED_
STATISTICS should be false.
On some systems with very fast timer access, Oracle might enable timing even if this
parameter is set to false. On these systems, setting the parameter to true can
sometimes produce more accurate statistics for long-running operations.
TRACE_ENABLED
TRACE_ENABLED controls tracing of the execution history, or code path, of Oracle.
Oracle Support Services uses this information for debugging.
When TRACE_ENABLED is set to true, Oracle records information in specific files
when errors occur. Oracle records this information for all instances, even if only one instance terminates. This allows Oracle to retain diagnostics for an entire cluster. Although the overhead incurred from this processing is not excessive, you can improve performance by setting TRACE_ENABLED to false. You might do this, for
example, to meet high-end benchmark requirements. However, if you leave this
parameter set to false, you may lose valuable diagnostic information. Therefore,
always set TRACE_ENABLED to true to trace system problems and to reduce
diagnostic efforts in the event of unexplained instance failures.
TRACEFILE_IDENTIFIER
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.
Each time this parameter is dynamically modified, the next trace dump will be written
to a trace file which has the new parameter value embedded in its name. Trace file
continuity information is automatically added to both the old and new trace files to
indicate that these trace files belong to the same process.
This parameter can only be used to change the name of the foreground process' trace
file; the background processes continue to have their trace files named in the regular format. For foreground processes, the TRACEID column of the V$PROCESS view
contains the current value of the TRACEFILE_IDENTIFIER parameter. When this
parameter value is set, the trace file name has the following format:
sid_ora_pid_traceid.trc
USER_DUMP_DEST
■ Distributed, Replication
COMMIT_POINT_STRENGTH
DISTRIBUTED_LOCK_TIMEOUT
DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time (in seconds) for
distributed transactions to wait for locked resources.
GLOBAL_NAMES
GLOBAL_NAMES specifies whether a database link is required to have the same name
as the database to which it connects. If the value of GLOBAL_NAMES is false, then no check is performed. If you use or plan to use distributed processing, then Oracle recommends that you set this parameter to true to ensure the use of consistent naming conventions for databases and links in a networked environment.
HS_AUTOREGISTER
HS_AUTOREGISTER enables or disables automatic self-registration of Heterogeneous
Services (HS) agents. When enabled, information is uploaded into the server's data
dictionary to describe a previously unknown agent class or a new agent version.
Oracle recommends that you set this parameter to true. Oracle incurs less overhead
when establishing subsequent connections through the same agent if self-registered
information is available in the server's data dictionary.
OPEN_LINKS
OPEN_LINKS specifies the maximum number of concurrent open connections to
remote databases in one session. These connections include database links, as well as
external procedures and cartridges, each of which uses a separate process.
Oracle counts one open link for the following:
■ For each user that references a public or private database link
■ For each external procedure or cartridge connection when it is executed for the
first time
Both types of connections close when the session ends. You can also close a database
link connection explicitly by issuing an ALTER SESSION CLOSE DATABASE LINK
statement.
You should set this parameter to allow for the external procedure and cartridge
connections expected during the session plus the number of databases referred to in
typical distributed transactions (that is, a single SQL statement that references multiple databases), so that all the databases can be open to execute the statement. For example,
if queries alternately access databases A, B, and C, and OPEN_LINKS is set to 2, time
will be lost waiting while one connection is broken and another made. Increase the
value if many different databases are accessed over time.
OPEN_LINKS_PER_INSTANCE
OPEN_LINKS_PER_INSTANCE specifies the maximum number of migratable open
connections globally for each database instance. XA transactions use migratable open
connections so that the connections are cached after a transaction is committed.
Another transaction can use the connection, provided the user who created the
connection is the same as the user who owns the transaction.
OPEN_LINKS_PER_INSTANCE is different from OPEN_LINKS, which indicates the
number of connections from a session. The OPEN_LINKS parameter is not applicable
to XA applications.
REPLICATION_DEPENDENCY_TRACKING
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.
Values:
■ TRUE
Enables dependency tracking.
■ FALSE
Allows read/write operations to the database to run faster, but does not produce
dependency information for Oracle to perform parallel propagation.
Note: Do not specify this value unless you are sure that your application will not perform any read/write operations to the replicated tables.
■ File Locations, Names, and Sizes
AUDIT_FILE_DEST
BACKGROUND_CORE_DUMP
BACKGROUND_DUMP_DEST
CONTROL_FILES
CORE_DUMP_DEST
DB_CREATE_FILE_DEST
DB_CREATE_FILE_DEST specifies the default location for Oracle-managed datafiles.
This location is also used as the default location for Oracle-managed control files and online redo logs if none of the DB_CREATE_ONLINE_LOG_DEST_n initialization
parameters are specified.
DB_CREATE_ONLINE_LOG_DEST_n
Syntax DB_CREATE_ONLINE_LOG_DEST_[1 | 2 | 3 | 4 | 5] =
directory | disk group
DB_CREATE_ONLINE_LOG_DEST_n (where n = 1, 2, 3, ... 5) specifies the default
location for Oracle-managed control files and online redo logs. If more than one DB_
CREATE_ONLINE_LOG_DEST_n parameter is specified, then the control file or online
redo log is multiplexed across the locations of the other DB_CREATE_ONLINE_LOG_
DEST_n parameters. One member of each online redo log is created in each location,
and one control file is created in each location. Specifying at least two parameters provides greater fault tolerance for the control files and online redo logs if one of the locations should fail.
DB_FILES
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
FILE_MAPPING
FILE_MAPPING enables or disables file mapping. The FMON background process will
be started to manage the mapping information when file mapping is enabled.
IFILE
Use IFILE to embed another parameter file within the current parameter file. For
example:
IFILE = COMMON.ORA
You can have up to three levels of nesting. In this example, the file COMMON.ORA could contain a second IFILE parameter for the file COMMON2.ORA, which could contain a third IFILE parameter for the file GCPARMS.ORA. You can also include multiple
parameter files in one parameter file by listing IFILE several times with different
values:
IFILE = DBPARMS.ORA
IFILE = GCPARMS.ORA
IFILE = LOGPARMS.ORA
LOG_ARCHIVE_DEST_n
SPFILE
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. When the default server parameter file is used by the server, the value of SPFILE is internally set by the server.
The SPFILE resides in the ORACLE_HOME/dbs directory; however, users can place it
anywhere on their machine as long as it is specified in an initialization parameter file.
■ Globalization
NLS_CALENDAR
NLS_CALENDAR specifies which calendar system Oracle uses. It can have one of the
following values:
■ Arabic Hijrah
■ English Hijrah
■ Gregorian
■ Japanese Imperial
■ Persian
■ ROC Official (Republic of China)
■ Thai Buddha
For example, suppose NLS_CALENDAR is set to "Japanese Imperial", the date format is
"E YY-MM-DD". ("E" is the date format element for the abbreviated era name.) If the
date is May 15, 1997, then the SYSDATE is displayed as follows:
SELECT SYSDATE FROM DUAL;
SYSDATE
--------
H 09-05-15
NLS_COMP
Syntax NLS_COMP = { BINARY | LINGUISTIC | ANSI }
Values:
■ BINARY
Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless
you specify the NLSSORT function.
■ LINGUISTIC
Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks
should use the linguistic sort specified in the NLS_SORT parameter. To improve
the performance, you can also define a linguistic index on the column for which
you want linguistic comparisons.
■ ANSI
A setting of ANSI is for backwards compatibility; in general, you should set NLS_
COMP to LINGUISTIC
NLS_CURRENCY
NLS_CURRENCY specifies the string to use as the local currency symbol for the L
number format element. The default value of this parameter is determined by NLS_
TERRITORY.
NLS_DATE_FORMAT
NLS_DATE_FORMAT specifies the default date format to use with the TO_CHAR and
TO_DATE functions. The default value of this parameter is determined by
NLS_TERRITORY.
The value of this parameter can be any valid date format mask, and the value must be
surrounded by double quotation marks. For example:
NLS_DATE_FORMAT = "MM/DD/YYYY"
NLS_DATE_LANGUAGE
NLS_DATE_LANGUAGE specifies the language to use for the spelling of day and month
names and date abbreviations (a.m., p.m., AD, BC) returned by the TO_DATE and TO_
CHAR functions.
NLS_DUAL_CURRENCY
NLS_DUAL_CURRENCY specifies the dual currency symbol (such as "Euro") for the
territory. The default is the dual currency symbol defined in the territory of your
current language environment.
NLS_ISO_CURRENCY
NLS_ISO_CURRENCY specifies the string to use as the international currency symbol
for the C number format element. Local currency symbols can be ambiguous. For example, a dollar sign ($) can refer to U.S. dollars or Australian dollars. ISO Specification 4217 1987-07-15 defines unique "international" currency symbols for the currencies of specific territories or countries.
NLS_LANGUAGE
NLS_LANGUAGE specifies the default language of the database. This language is used
for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the
default sorting mechanism. This parameter also determines the default values of the
parameters NLS_DATE_LANGUAGE and NLS_SORT.
NLS_LENGTH_SEMANTICS
NLS_LENGTH_SEMANTICS enables you to create CHAR and VARCHAR2 columns using
either byte or character length semantics. Existing columns are not affected.
NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. You may
be required to use byte semantics in order to maintain compatibility with existing
applications.
NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data
dictionary always uses byte semantics.
NLS_NCHAR_CONV_EXCP
NLS_NCHAR_CONV_EXCP determines whether an error is reported when there is data
loss during an implicit or explicit character type conversion between
NCHAR/NVARCHAR and CHAR/VARCHAR2. The default value results in no error
being reported.
NLS_NUMERIC_CHARACTERS
NLS_NUMERIC_CHARACTERS specifies the characters to use as the group separator
and decimal character. It overrides those characters defined implicitly by NLS_
TERRITORY. The group separator separates integer groups (that is, thousands,
millions, billions, and so on). The decimal separates the integer portion of a number
from the decimal portion.You can specify any character as the decimal or group separator. The two characters specified must be single-byte and must be different from each other. The characters cannot be any numeric character or any of the following characters: plus (+), minus sign (-), less than sign (<), greater than sign (>). Either character can be a space.
For example, if you wish to specify a comma as the decimal character and a space as
the group separator, you would set this parameter as follows:
NLS_NUMERIC_CHARACTERS = ", "
NLS_SORT
■ If the value is BINARY, then the collating sequence for ORDER BY queries is based
on the numeric value of characters (a binary sort that requires less system
overhead).
■ If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE
parameter also support a linguistic sort with the same name.
NLS_TERRITORY
NLS_TERRITORY specifies the name of the territory whose conventions are to be
followed for day and week numbering.
This parameter also establishes the default date format, the default decimal character and group separator, and the default ISO and local currency symbols.
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_FORMAT defines the default timestamp format to use with the TO_
CHAR and TO_TIMESTAMP functions.
The value must be surrounded by quotation marks as follows:
NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'
You can specify the value of NLS_TIMESTAMP_FORMAT by setting it in the
initialization parameter file. You can specify its value for a client as a client
environment variable. You can also alter the value of NLS_TIMESTAMP_FORMAT by changing its value in the initialization parameter and then restarting the instance. To alter the value during a session use the ALTER SESSION SET statement.
NLS_TIMESTAMP_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT defines the default timestamp with time zone format
to use with the TO_CHAR and TO_TIMESTAMP_TZfunctions.
The value must be surrounded by quotation marks as follows:
NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
You can specify the value of NLS_TIMESTAMP_TZ_FORMAT by setting it in the
initialization parameter file. You can specify its value for a client as a client
environment variable.
You can also alter the value of NLS_TIMESTAMP_TZ_FORMAT by changing its value in
the initialization parameter and then restarting the instance. To alter the value during a session use the ALTER SESSION SET statement.
■ Java
JAVA_MAX_SESSIONSPACE_SIZE
JAVA_POOL_SIZE
JAVA_SOFT_SESSIONSPACE_LIMIT
■ Job Queues
JOB_QUEUE_PROCESSES
JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be
created for the execution of jobs. It specifies the number of job queue processes per
instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced
queuing uses job queues for message propagation. You can create user job requests
through the DBMS_JOB package.
Some job queue requests are created automatically. An example is refresh support for
materialized views. If you wish to have your materialized views updated
automatically, you must set JOB_QUEUE_PROCESSES to a value of one or higher.
■ License Limits
LICENSE_MAX_SESSIONS
LICENSE_MAX_SESSIONS specifies the maximum number of concurrent user sessions
allowed. When this limit is reached, only users with the RESTRICTED SESSION
privilege can connect to the database. Users who are not able to connect receive a
warning message indicating that the system has reached maximum capacity.
A zero value indicates that concurrent usage (session) licensing is not enforced. If you set this parameter to a nonzero number, you might also want to set LICENSE_
SESSIONS_WARNING.
Do not enable both concurrent usage licensing and user licensing. Set either LICENSE_
MAX_SESSIONS or LICENSE_MAX_USERS to zero.
LICENSE_MAX_USERS
LICENSE_MAX_USERS specifies the maximum number of users you can create in the
database. When you reach this limit, you cannot create more users. You can, however,
increase the limit.
Do not enable both concurrent usage (session) licensing and user licensing. Set either LICENSE_MAX_SESSIONS or LICENSE_MAX_USERS to zero.
LICENSE_SESSIONS_WARNING
LICENSE_SESSIONS_WARNING specifies a warning limit on the number of concurrent
user sessions. When this limit is reached, additional users can connect, but Oracle
writes a message in the alert file for each new connection. Users with RESTRICTED
SESSION privilege who connect after the limit is reached receive a warning message
stating that the system is nearing its maximum capacity.
■ Memory
LARGE_POOL_SIZE
LARGE_POOL_SIZE specifies (in bytes) the size 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 SGA_TARGET is set.
Caution: When Oracle derives a default value, it adds 250K for each session for the shared server if DISPATCHERS is configured. The final derived value also includes a port-specific amount of memory for backup I/O buffers. The total derived default value can either be too large to allocate or can cause performance problems. In that case, set LARGE_POOL_SIZE to a number sufficiently small so that the database can start.
SHARED_POOL_RESERVED_SIZE
SHARED_POOL_RESERVED_SIZE specifies (in bytes) the shared pool space that is
reserved for large contiguous requests for shared pool memory.
You can use this parameter to avoid performance degradation in the shared pool in
situations where pool fragmentation forces Oracle to search for and free chunks of
unused pool to satisfy the current request.
SHARED_POOL_SIZE
Default value If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is
specified, then the user-specified value indicates a minimum value for the memory pool. If SGA_TARGET is not set (32-bit platforms): 32 M, rounded up to the
nearest granule size. If SGA_TARGET is not set (64-bit platforms): 84 M, rounded up to the nearest granule size.
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.
You can monitor utilization of the shared pool by querying the view V$SGASTAT.
■ Miscellaneous
AQ_TM_PROCESSES
AQ_TM_PROCESSES enables time monitoring of queue messages. The times can be
used in messages that specify delay and expiration properties. Values from 1 to 10 specify the number of queue monitor processes created to monitor the messages. If AQ_TM_PROCESSES is not specified or is set to 0, then the queue monitor is not created.
COMPATIBLE
FIXED_DATE
FIXED_DATE enables you to set a constant date that SYSDATE will always return
instead of the current date. To undo a fixed date setting, specify FIXED_DATE=NONE.
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.
■ Networking
LOCAL_LISTENER
Syntax LOCAL_LISTENER = network_name
Default value - (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))
where hostname is the network name of the local host.
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.
REMOTE_LISTENER
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.
SERVICE_NAMES
SERVICE_NAMES specifies one or more names by which clients can connect to the
instance. The instance registers its service names with the listener. When a client
requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance.
You can specify multiple service names in order to distinguish among different uses of the same database.
For example:
SERVICE_NAMES = sales.acme.com, widgetsales.acme.com
■ Objects and LOBs
OBJECT_CACHE_MAX_SIZE_PERCENT
Default value 10
The object cache is a memory block on the client that allows applications to store
entire objects and to navigate among them without round trips to the server. OBJECT_
CACHE_MAX_SIZE_PERCENT specifies the percentage of the optimal cache size that
the session object cache can grow past the optimal size. The maximum size is equal to
the optimal size plus the product of this percentage and the optimal size. When the
cache size exceeds this maximum size, the system will attempt to shrink the cache to
the optimal size.
OBJECT_CACHE_OPTIMAL_SIZE
The object cache is a memory block on the client that allows applications to store
entire objects and to navigate among them without round trips to the server. OBJECT_
CACHE_OPTIMAL_SIZE specifies (in bytes) the size to which the session object cache
is reduced when the size of the cache exceeds the maximum size.
■ OLAP
OLAP_PAGE_POOL_SIZE
■ Optimizer
OPTIMIZER_DYNAMIC_SAMPLING
Default value If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2
If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1
If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0
OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed
by the optimizer.
OPTIMIZER_FEATURES_ENABLE
Syntax OPTIMIZER_FEATURES_ENABLE = { 8.0.0 | 8.0.3 | 8.0.4
| 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 |
8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 |
10.0.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.2.0.1}
OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series
of optimizer features based on an Oracle release number.
For example, if you upgrade your database from release 8.0.6 to release 9.2, but you
want to keep the release 8.0.6 optimizer behavior, you can do so by setting this
parameter to 8.0.6. At a later time, you can try the enhancements introduced in
releases up to and including release 9.2 by setting the parameter to 9.2.0.
OPTIMIZER_INDEX_CACHING
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. The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users.
You can modify the optimizer's assumptions about index caching for nested loops
joins and IN-list iterators by setting this parameter to a value between 0 and 100 to
indicate the percentage of the index blocks the optimizer should assume are in the
cache. Setting this parameter to a higher value makes nested loops joins and IN-list
iterators look less expensive to the optimizer. As a result, it will be more likely to pick nested loops joins over hash or sort-merge joins and to pick indexes using IN-list iterators over other indexes or full table scans. The default for this parameter is 0, which results in default optimizer behavior.
OPTIMIZER_INDEX_COST_ADJ
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.
The default for this parameter is 100 percent, at which the optimizer evaluates index
access paths at the regular cost. Any other value makes the optimizer evaluate the
access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
OPTIMIZER_MODE
Syntax OPTIMIZER_MODE =
{ first_rows_[1 | 10 | 100 | 1000] | first_rows |all_rows }
Default value all_rows
Values:
■ first_rows_n
The optimizer uses a cost-based approach and optimizes with a goal of best
response time to return the first n rows (where n = 1, 10, 100, 1000).
■ first_rows
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
■ all_rows
The optimizer uses a cost-based approach for all SQL statements in the session and
optimizes with a goal of best throughput (minimum resource use to complete the
entire statement).
OPTIMIZER_SECURE_VIEW_MERGING
OPTIMIZER_SECURE_VIEW_MERGING allows you to enable or disable view merging
globally for the database.
FALSE
Oracle does not use view merging or predicate move-around.
■ TRUE
Oracle assesses the query, considering all transformations, and chooses the
method with the lowest cost. To take advantage of query rewrite for a particular query, you must disable the OPTIMIZER_SECURE_VIEW_MERGING parameter.
QUERY_REWRITE_ENABLED
QUERY_REWRITE_ENABLED allows you to enable or disable query rewriting globally
for the database.
Values:
■ false
Oracle does not use rewrite.
■ true
Oracle costs the query with rewrite and without rewrite and chooses the method
with the lower cost.
■ force
Oracle always uses rewrite and does not evaluate the cost before doing so. Use
force when you know that the query will always benefit from rewrite and when
reduction in compile time is important. To take advantage of query rewrite for a particular materialized view, you must enable query rewrite for that materialized view, and you must enable cost-based optimization.
QUERY_REWRITE_INTEGRITY
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.
Values:
■ enforced
Oracle enforces and guarantees consistency and integrity.
■ trusted
Oracle allows rewrites using relationships that have been declared, but that are not
enforced by Oracle.
■ stale_tolerated
Oracle allows rewrites using unenforced relationships. Materialized views are
eligible for rewrite even if they are known to be inconsistent with the underlying
detail data.
STAR_TRANSFORMATION_ENABLED
■ Parallel Execution
PARALLEL_ADAPTIVE_MULTI_USER
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.
The algorithm assumes that the system has been tuned for optimal performance in a
single-user environment.
Tables and hints use the default degree of parallelism.
PARALLEL_EXECUTION_MESSAGE_SIZE
PARALLEL_EXECUTION_MESSAGE_SIZE specifies the size of messages for parallel
execution (formerly referred to as parallel query, PDML, Parallel Recovery,
replication).
On most platforms, the default value is 2148bytes if PARALLEL_AUTOMATIC_TUNING
is set to false, and 4096 bytes if PARALLEL_AUTOMATIC_TUNING is set to true.
The default value is adequate for most applications. Larger values require a larger
shared pool. Larger values result in better performance at the cost of higher memory
use. For this reason, replication gets no benefit from increasing the size.
PARALLEL_MAX_SERVERS
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.
If you set this parameter too low, some queries may not have a parallel execution
process available to them during query processing. If you set it too high, memory
resource shortages may occur during peak periods, which can degrade performance.
PARALLEL_MIN_PERCENT
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.
Consider the following settings:
PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10
If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.
You can use this parameter in conjunction with PARALLEL_ADAPTIVE_MULTI_USER.
In a multi-user environment, an individual user or application can set PARALLEL_
MIN_PERCENT to a minimum value until sufficient resources are available on the
system and an acceptable degree of parallelism is returned.
PARALLEL_MIN_SERVERS
PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution
processes for the instance. This value is the number of parallel execution processes
Oracle creates when the instance is started.
PARALLEL_THREADS_PER_CPU
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.
The default is platform-dependent and is adequate in most cases. You should decrease
the value of this parameter if the machine appears to be overloaded when a
representative parallel query is executed. You should increase the value if the system is I/O bound.
■ PL/SQL
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
PLSQL_V2_COMPATIBILITY
REMOTE_DEPENDENCIES_MODE
UTL_FILE_DIR
■ PL/SQL Compiler
PLSQL_CCFLAGS
PLSQL_CODE_TYPE
PLSQL_DEBUG
PLSQL_OPTIMIZE_LEVEL
PLSQL_WARNINGS
NLS_LENGTH_SEMANTICS
■ SGA Memory
DB_nK_CACHE_SIZE
DB_CACHE_ADVICE
DB_CACHE_SIZE
HI_SHARED_MEMORY_ADDRESS
HI_SHARED_MEMORY_ADDRESS specifies the starting address at runtime of the system
global area (SGA). It is ignored on platforms that specify the SGA's starting address at linktime.
On 64-bit platforms, use HI_SHARED_MEMORY_ADDRESS to specify the high-order 32
bits of a 64-bit address. Use SHARED_MEMORY_ADDRESS to specify the low-order 32
bits of the address (see "SHARED_MEMORY_ADDRESS" on page 1-133). If both
parameters are 0 or unspecified, the SGA address defaults to a platform-specific
location.
JAVA_POOL_SIZE
LOCK_SGA
OLAP_PAGE_POOL_SIZE
PRE_PAGE_SGA
SGA_MAX_SIZE
SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance.
SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is
specified, then the following memory pools are automatically sized:
■ Buffer cache (DB_CACHE_SIZE)
■ Shared pool (SHARED_POOL_SIZE)
■ Large pool (LARGE_POOL_SIZE)
■ Java pool (JAVA_POOL_SIZE)
■ Streams pool (STREAMS_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero values, then those
values are used as minimum levels by Automatic Shared Memory Management. You
would set minimum values if an application component needs a minimum amount of
memory to function properly.
The following pools are manually sized components and are not affected by
Automatic Shared Memory Management:
■ Log buffer
■ Other buffer caches, such as KEEP, RECYCLE, and other block sizes
■ Fixed SGA and other internal allocations
The memory allocated to these pools is deducted from the total available for SGA_
TARGET when Automatic Shared Memory Management computes the values of the
automatically tuned memory pools.
SGA_TARGET
SHARED_MEMORY_ADDRESS
■ Real Application Clusters
ACTIVE_INSTANCE_COUNT
ACTIVE_INSTANCE_COUNT enables you to designate one instance in a two-instance
cluster as the primary instance and the other instance as the secondary instance. This parameter has no functionality in a cluster with more than two instances.
When you set this parameter to 1, the first instance you start up becomes the primary
instance and accepts client connections. The second instance starts up as a secondary
instance and can accept client connections only if the first instance fails. In such an event, the secondary instance becomes the primary instance.
When the failed instance can once again be started up, it starts up as the secondary
instance, and will not accept client connections unless the current primary instance
fails.
CLUSTER_DATABASE
CLUSTER_DATABASE is a Real Application Clusters parameter that specifies whether
or not Real Application Clusters is enabled.
CLUSTER_DATABASE_INSTANCES
CLUSTER_DATABASE_INSTANCES is a 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 Real Application Clusters environment. A proper setting for this parameter can improve memory use.
Oracle uses the value of this parameter to compute the default value of the LARGE_
POOL_SIZE parameter when the PARALLEL_AUTOMATIC_TUNING parameter is set to
true. Note that the PARALLEL_AUTOMATIC_TUNIG parameter has been deprecated.
CLUSTER_INTERCONNECTS
GC_FILES_TO_LOCKS
Syntax GC_FILES_TO_LOCKS =
'{file_list=lock_count[!blocks][EACH][:...]}'
Spaces are not allowed within the quotation marks.
■ file_list
One or more datafiles listed by their file numbers, or ranges of file numbers, with
comma separators:
filenumber[-filenumber][,filenumber[-filenumber]]...
To find the correspondence between filenames and file numbers, query the FILE_
NAME and FILE_ID columns of the DBA_DATA_FILES data dictionary view.
■ lock_count
The number of PCM locks assigned to file_list. By default these locks are
fixed. If you set lock_count to 0, then Oracle uses fine-grain locking for these
files and takes locks as needed from the pool of releasable locks.
■ blocks
Specifies the number of contiguous blocks covered by one lock. The default is
noncontiguous blocks.
■ EACH
Indicates that each datafile in file_list is assigned a separate set of lock_
count PCM locks.
GCS_SERVER_PROCESSES
Default value If CLUSTER_DATABASE is set to true, then 2
If CLUSTER_DATABASE is set to false, then 0
GCS_SERVER_PROCESSES specifies the initial number of server processes in Global
Cache Service to serve the inter-instance traffic among Real Application Clusters
instances.
INSTANCE_GROUPS
INSTANCE_GROUPS is a Real Application Clusters parameter that you can specify
only in parallel mode. Used in conjunction with the PARALLEL_INSTANCE_GROUP
parameter, it lets you restrict parallel query operations to a limited number of
instances.
INSTANCE_NUMBER
MAX_COMMIT_PROPAGATION_DELAY
MAX_COMMIT_PROPAGATION_DELAY is a Real Application Clusters parameter. This
initialization parameter should not be changed except under a limited set of
circumstances specific to the cluster database.
PARALLEL_INSTANCE_GROUP
PARALLEL_INSTANCE_GROUP is a 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.
This parameter identifies the parallel instance group Oracle will use for spawning
parallel execution processes. Parallel operations will spawn parallel execution
processes only on instances that specify a matching group in their INSTANCE_GROUPS
parameter. If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a group that does not exist, then the operation runs serially. No parallelism is used.
THREAD
THREAD is a Real Application Clusters parameter that specifies the number of the redo
thread to be used by an instance. When you create a database, Oracle creates and enables thread 1 as a public thread (one that can be used by any instance). You must create and enable subsequent threads using the ADD LOGFILE THREAD clause and ENABLE THREAD clause of the ALTER DATABASE statement. The number of threads you create is limited by the MAXINSTANCES parameter specified in the CREATE DATABASE statement.
In exclusive mode, thread 1 is the default thread. However, you can specify THREAD
for an instance running in exclusive mode if you want to use the redo log files in a
thread other than thread 1.
In parallel mode, you can specify any available redo thread number, as long as that
thread number is enabled and is not in use by another instance.
A value of zero specifies that this instance can use any available, enabled public
thread.
■ Redo Logs, Archiving, and Recovery
CONTROL_FILE_RECORD_KEEP_TIME
CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before
a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed.
Note: This parameter applies only to records in the control file that
are circularly reusable (such as archive log records and various
backup records). It does not apply to records such as datafile,
tablespace, and redo thread records, which are never reused unless the
corresponding object is dropped from the tablespace.
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
FAST_START_MTTR_TARGET
LOG_ARCHIVE_CONFIG
Syntax LOG_ARCHIVE_CONFIG =
{
[ SEND | NOSEND ]
[ RECEIVE | NORECEIVE ]
[ DG_CONFIG=(remote_db_unique_name1 [, ... remote_db_
unique_name9) | NODG_CONFIG ]
}
Default value - 'SEND, RECEIVE, NODG_CONFIG'
■ SEND
Enables the sending of redo logs to remote destinations
■ NOSEND
Disables the sending of redo logs to remote destinations
■ RECEIVE
Enables the receipt of remotely archived redo logs
■ NORECEIVE
Disables the receipt of remotely archived redo logs
■ DG_CONFIG
Specifies a list of up to 9 unique database names (defined with the DB_UNIQUE_
NAME initialization parameter) for all of the databases in the Data Guard
configuration.
■ NODG_CONFIG
Eliminates the list of service provider names previously specified with the DG_
CONFIG option.
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
LOG_ARCHIVE_DUPLEX_DEST
LOG_ARCHIVE_DUPLEX_DEST is similar to the initialization parameter LOG_
ARCHIVE_DEST. This parameter specifies a second archive destination: the duplex
archive destination. This duplex archive destination can be either a must-succeed or a best-effort archive destination, depending on how many archive destinations must
succeed (as specified in the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter).
The default setting of a null string ("") or (' ') indicates that a duplex archive
destination does not exist.
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_FORMAT is applicable only if you are using the redo log in
ARCHIVELOG mode. Use a text string and variables to specify the default filename
format when archiving redo log files. The string generated from this format is
appended to the string specified in the LOG_ARCHIVE_DEST parameter.
The following variables can be used in the format:
%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files
across multiple incarnations of the database
Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros. An example of specifying the archive redo log filename format follows:
LOG_ARCHIVE_FORMAT = 'log%t_%s_%r.arc'
LOG_ARCHIVE_MAX_PROCESSES
Range of values 1 to 30
LOG_ARCHIVE_MIN_SUCCEED_DEST
Range of values 1 to 10 if you are using LOG_ARCHIVE_DEST_n
1 or 2 if you are using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST
LOG_ARCHIVE_TRACE
Range of values 0, 1, 2, 4, 8, 16, 32, 64, 128, 256, 512, 1024, 2048, 4096, 8192
The valid values have the following meanings:
■ 0: Disable archivelog tracing (this is the default)
■ 1: Track archival of redo log file
■ 2: Track archival status of each archivelog destination
■ 4: Track archival operational phase
■ 8: Track archivelog destination activity
■ 16: Track detailed archivelog destination activity
■ 32: Track archivelog destination parameter modifications
■ 64: Track ARCn process state activity
■ 128: Track FAL (fetch archived log) server related activities
■ 256: Track RFS Logical Client
■ 512: Track LGWR redo shipping network activity
■ 1024: Track RFS Physical Client
■ 2048: Track RFS/ARCn Ping Heartbeat
■ 4096: Track Real Time Apply
■ 8192: Track Redo Apply (Media Recovery or Physical Standby)
LOG_BUFFER
Default value 512 KB or 128 KB * CPU_COUNT, whichever is greater
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.
In general, larger values for LOG_BUFFER reduce redo log file I/O, particularly if
transactions are long or numerous. In a busy system, a value 65536 or higher is
reasonable.
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_INTERVAL specifies the frequency of checkpoints in terms of the
number of redo log file blocks that can exist between an incremental checkpoint and
the last block written to the redo log. This number refers to physical operating system blocks, not database blocks. Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. Therefore, if the value exceeds the actual redo log file size, checkpoints occur only when switching logs. Checkpoint frequency is one of the factors that influence the time required for the database to recover from an unexpected failure.
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINT_TIMEOUT specifies (in seconds) the amount of time that has
passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds. Specifying a value of 0 for the timeout disables time-based checkpoints. Hence, setting the value to 0 is not recommended unless FAST_START_MTTR_TARGET is set.
LOG_CHECKPOINTS_TO_ALERT
LOG_CHECKPOINTS_TO_ALERT lets you log your checkpoints to the alert file. Doing
so is useful for determining whether checkpoints are occurring at the desired
frequency.
RECOVERY_PARALLELISM
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.
■ Resource Manager
RESOURCE_LIMIT
RESOURCE_LIMIT determines whether resource limits are enforced in database
profiles.
Values:
■ TRUE
Enables the enforcement of resource limits
■ FALSE
Disables the enforcement of resource limits
RESOURCE_MANAGER_PLAN
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.
You can change the setting of this parameter using the ALTER SYSTEM statement to
turn on the resource manager (if it was previously off) or to turn off the resource
manager or change the current plan (if it was previously on). If you specify a plan that does not exist in the data dictionary, Oracle returns an error message.
■ Security and Auditing
AUDIT_FILE_DEST
AUDIT_SYS_OPERATIONS
AUDIT_SYSLOG_LEVEL
AUDIT_SYSLOG_LEVEL enables OS audit logs to be written to the system via the
SYSLOG utility if the AUDIT_TRAIL parameter is set to os.
The value of facility can be any of the following: USER, LOCAL0-LOCAL7, SYSLOG,
DAEMON, KERN, MAIL, AUTH, LPR, NEWS, UUCP or CRON.
The value of level can be any of the following: NOTICE, INFO, DEBUG, WARNING, ERR,
CRIT, ALERT, EMERG . If you use this parameter, it is best to assign a file corresponding to every facility.level combination (especially kern.emerg) in syslog.conf . Sometimes these are assigned to print to the console in the default syslog.conf file. This can become annoying and will be useless as audit logs.
Also, if you use this parameter, it is best to set the maximum length of syslog messages in the system to 512 bytes.
AUDIT_TRAIL
AUDIT_TRAIL enables or disables database auditing.
Values:
■ none
Disables database auditing.
■ os
Enables database auditing and directs all audit records to the operating system's
audit trail.
■ db
Enables database auditing and directs all audit records to the database audit trail
(the SYS.AUD$ table).
■ db,extended
Enables database auditing and directs all audit records to the database audit trail
(the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB
columns of the SYS.AUD$ table.
■ xml
Enables database auditing and writes all audit records to XML format OS files.
■ xml,extended
Enables database auditing and prints all columns of the audit trail, including
SqlText and SqlBind values.
You can use the SQL statement AUDIT to set auditing options regardless of the setting
of this parameter.
O7_DICTIONARY_ACCESSIBILITY
O7_DICTIONARY_ACCESSIBILITY 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 the SYS schema.
For example, if O7_DICTIONARY_ACCESSIBILITY is set to false, then the SELECT
ANY TABLE privilege allows access to views or tables in any schema except the SYS
schema (data dictionary tables cannot be accessed). The system privilege EXECUTE
ANY PROCEDURE allows access on the procedures in any schema except the SYS
schema. If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges. The following roles, which can be granted to the database administrator, also allow access to dictionary objects:
■ SELECT_CATALOG_ROLE
■ EXECUTE_CATALOG_ROLE
■ DELETE_CATALOG_ROLE
OS_AUTHENT_PREFIX
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.
The default value of this parameter is OPS$ for backward compatibility with previous
versions. However, you might prefer to set the prefix value to "" (a null string), thereby eliminating the addition of any prefix to operating system account names.
OS_ROLES
Values:
■ TRUE
The operating system completely manages the role grants for all database
usernames. When a user attempts to create a session, the username's security
domain is initialized using the roles identified by the operating system. A user can
subsequently enable as many roles identified by the operating system as specified
by the parameter MAX_ENABLED_ROLES.
Revocation by Oracle of roles granted by the operating system is ignored, as are
any roles previously granted by Oracle.
■ FALSE
Oracle identifies and manages the roles.
RDBMS_SERVER_DN
REMOTE_LOGIN_PASSWORDFILE
REMOTE_LOGIN_PASSWORDFILE specifies whether Oracle checks for a password file.
Values:
■ NONE
Oracle ignores any password file. Therefore, privileged users must be
authenticated by the operating system.
■ SHARED
One or more databases can use the password file. The password file can contain
SYS as well as non-SYS users.
Note:-
The value EXCLUSIVE is supported for backward
compatibility. It now has the same behavior as the value SHARED.
REMOTE_OS_AUTHENT
REMOTE_OS_AUTHENT specifies whether remote clients will be authenticated with the
value of the OS_AUTHENT_PREFIX parameter.
REMOTE_OS_ROLES
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.
SQL92_SECURITY
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 specifies whether users must have been granted the SELECT object
privilege in order to execute such UPDATE or DELETE statements.
■ Sessions and Processes
CPU_COUNT
PROCESSES
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.
The default values of the SESSIONS and TRANSACTIONS parameters are derived from
this parameter. Therefore, if you change the value of PROCESSES, you should evaluate
whether to adjust the values of those derived parameters.
SESSIONS
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.
Oracle uses the default value of this parameter as its minimum. Values between 1 and
the default do not trigger errors, but Oracle ignores them and uses the default instead.
The default values of the ENQUEUE_RESOURCES and TRANSACTIONS parameters are
derived from SESSIONS. Therefore, if you increase the value of SESSIONS, you
should consider whether to adjust the values of ENQUEUE_RESOURCES and
TRANSACTIONS as well. (Note that ENQUEUE_RESOURCES is obsolete as of Oracle
Database 10g release 2 (10.2).)
In a shared server environment, the value of PROCESSES can be quite small. Therefore,
Oracle recommends that you adjust the value of SESSIONS to approximately 1.1 *
total number of connections.
■ Shared Server Architecture
CIRCUITS
DISPATCHERS
Syntax DISPATCHERS = 'dispatch_clause'
dispatch_clause::=
(PROTOCOL = protocol) |
(ADDRESS = address) |
(DESCRIPTION = description )
[options_clause]
options_clause::=
(DISPATCHERS = integer |
SESSIONS = integer |
CONNECTIONS = integer |
TICKS = seconds |
POOL = {1 | ON | YES | TRUE | BOTH |
({IN | OUT} = ticks) | 0 | OFF | NO | FALSE |
ticks} |
MULTIPLEX = {1 | ON | YES | TRUE |
0 | OFF | NO | FALSE | BOTH | IN | OUT} |
LISTENER = tnsname |
SERVICE = service |
INDEX = integer)
Default value If SHARED_SERVERS is greater than 0, then DISPATCHERS defaults to
'(PROTOCOL=tcp)', causing 1 TCP/IP dispatcher to be created.
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)' Attributes may be specified using the full attribute name or any substring beginning with the first 3 characters. For example, SESSIONS can be specified as SES, SESS, SESSI, and so on.
Specify only one of the following attributes: PROTOCOL, ADDRESS, or DESCRIPTION.
If you specify either ADDRESS or DESCRIPTION, then you can specify additional
network attributes. Doing so supports multi-homed hosts.
MAX_DISPATCHERS
MAX_DISPATCHERS specifies the maximum number of dispatcher processes allowed
to be running simultaneously. It can be overridden by the DISPATCHERS parameter
and is maintained for backward compatibility with older releases.
MAX_SHARED_SERVERS
MAX_SHARED_SERVERS specifies the maximum number of shared server processes
allowed to be running simultaneously. Setting this parameter enables you to reserve
process slots for other processes, such as dedicated servers.
When you want to reduce the range of shared servers, you can reduce MAX_SHARED_
SERVERS before reducing SHARED_SERVERS. If MAX_SHARED_SERVERS is lower than
SHARED_SERVERS, then the number of shared servers will not vary but will remain at
the constant level specified by SHARED_SERVERS. If MAX_SHARED_SERVERS is not
specified, then a shared server process may be spawned as long as the number of free
process slots is greater than 1 / 8 the maximum number of processes, or 2 if
PROCESSES is less than 24.
SHARED_SERVER_SESSIONS
SHARED_SERVER_SESSIONS specifies the total number of shared server sessions to
allow. Setting this parameter enables you to reserve user sessions for dedicated
servers.
SHARED_SERVERS
SHARED_SERVERS specifies the number of server processes that you want to create
when an instance is started. If system load decreases, then this minimum number of
servers is maintained. Therefore, you should take care not to set SHARED_SERVERS
too high at system startup.
■ Standby Database
ARCHIVE_LAG_TARGET
ARCHIVE_LAG_TARGET limits the amount of data that can be lost and effectively
increases the availability of the standby database by forcing a log switch after the
specified amount of time elapses.
A 0 value disables the time-based thread advance feature; otherwise, the value
represents the number of seconds. Values larger than 7200 seconds are not of much use
in maintaining a reasonable lag in the standby database. The typical, or recommended
value is 1800 (30 minutes). Extremely low values can result in frequent log switches,
which could degrade performance; such values can also make the archiver process too
busy to archive the continuously generated logs.
DB_FILE_NAME_CONVERT
DB_UNIQUE_NAME
DB_UNIQUE_NAME specifies a globally unique name for the database. Databases with
the same DB_NAME within the same DB_DOMAIN (for example, copies of a database
created for reporting or a physical standby) must have a unique DB_UNIQUE_NAME.
Every database's DB_UNIQUE_NAME must be unique within the enterprise.
The value of DB_UNIQUE_NAME can be up to 30 characters and is case insensitive. The
following characters are valid in a database name: alphanumeric characters,
underscore (_), number sign (#), and dollar sign ($).
DG_BROKER_CONFIG_FILEn
DG_BROKER_CONFIG_FILEn (where n = 1, 2) specifies the names for the Data Guard
broker configuration files. Every database that is part of a Data Guard broker configuration has two broker configuration files, which contain entries that describe the state and properties of the configuration (such as the sites and databases that are part of the configuration, the roles and properties of each of the databases, and the state of each of the elements of the configuration). Two files are provided so as to always maintain the last known good state of the configuration.
If DG_BROKER_CONFIG_FILEn is not explicitly defined, then it is set to an operating
system-specific default value at instance startup. The parameter can only be altered
when the DMON (Data Guard broker) process is not running.
DG_BROKER_START
DG_BROKER_START enables Oracle to determine whether or not the Data Guard
broker (DMON) 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.
If the site is never going to be configured in a Data Guard broker configuration, then you can leave the parameter unspecified and accept the default value of false. If the site is part of a Data Guard broker configuration, then automatic startup of the DMON process is simplified by setting DG_BROKER_START to true in the initialization parameter file.
FAL_CLIENT
FAL_CLIENT specifies the FAL (fetch archive log) client name that is used by the FAL
service, configured through the FAL_SERVER parameter, to refer to the FAL client. The
value is an Oracle Net service name, which is assumed to be configured properly on
the FAL server system to point to the FAL client (standby database).
Given the dependency of FAL_CLIENT on FAL_SERVER, the two parameters should
be configured or changed at the same time.
FAL_SERVER
FAL_SERVER specifies the FAL (fetch archive log) server for a standby database. The
value is an Oracle Net service name, which is assumed to be configured properly on
the standby database system to point to the desired FAL server.
LOG_FILE_NAME_CONVERT
STANDBY_ARCHIVE_DEST
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. You can see the value of this parameter by querying the V$ARCHIVE_DEST data dictionary view
STANDBY_FILE_MANAGEMENT
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.
Values:
■ MANUAL
disables automatic standby file management
■ AUTO
enables automatic standby file management
Setting STANDBY_FILE_MANAGEMENT to AUTO causes Oracle to automatically create
files on the standby database and, in some cases, overwrite existing files. Care must be taken when setting STANDBY_FILE_MANAGEMENT and DB_FILE_NAME_CONVERT so
that existing standby files will not be accidentally overwritten.
If the standby database is on the same system as the primary database, then ensure
that the primary and standby systems do not point to the same files.
■ Temporary Sort Space
BITMAP_MERGE_AREA_SIZE
CREATE_BITMAP_AREA_SIZE
HASH_AREA_SIZE
PGA_AGGREGATE_TARGET
PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all
server processes attached to the instance.
Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically
setting the WORKAREA_SIZE_POLICY parameter to AUTO. This means that SQL
working areas used by memory-intensive SQL operators (such as sort, group-by,
hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero
value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.
Setting PGA_AGGREGATE_TARGET to 0 automatically sets the WORKAREA_SIZE_
POLICY parameter to MANUAL. This means that SQL workareas are sized using the *_
AREA_SIZE parameters.
Oracle attempts to keep the amount of private memory below the target specified by
this parameter by adapting the size of the work areas to private memory. When
increasing the value of this parameter, you indirectly increase the memory allotted to work areas. Consequently, more memory-intensive operations are able to run fully in memory and less will work their way over to disk.
When setting this parameter, you should examine the total memory on your system
that is available to the Oracle instance and subtract the SGA. You can assign the
remaining memory to PGA_AGGREGATE_TARGET.
SORT_AREA_RETAINED_SIZE
SORT_AREA_SIZE
WORKAREA_SIZE_POLICY
■ Transactions
COMMIT_WRITE
COMMIT_WRITE is an advanced parameter used to control how redo for transaction
commits is written to the redo logs. The IMMEDIATE and BATCH options control how
redo is batched by Log Writer. The WAIT and NOWAIT options control when the redo
for a commit is flushed to the redo logs.
DML_LOCKS
A DML lock is a lock obtained on a table that is undergoing a DML operation (insert,
update, delete). DML_LOCKS specifies the maximum number of DML locks—one for
each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.
The default value assumes an average of four tables referenced for each transaction.
For some systems, this value may not be enough.
Enqueues are shared memory structures that serialize access to database resources. If
you set the value of DML_LOCKS to 0, enqueues are disabled and performance is
slightly increased. However, you should be aware of the following restrictions when
you set you DML_LOCKS to 0:
■ You cannot use DROP TABLE, CREATE INDEX statements.
■ You cannot use explicit lock statements such as LOCK TABLE IN EXCLUSIVE
MODE.
■ Enterprise Manager cannot run on any instances for which DML_LOCKS is set to 0
Oracle holds more locks during parallel DML than during serial execution. Therefore,
if your database supports a lot of parallel DML, you may need to increase the value of this parameter.
FAST_START_PARALLEL_ROLLBACK
FAST_START_PARALLEL_ROLLBACK determines the maximum number of processes
that can exist for performing parallel rollback. This parameter is useful on systems in which some or all of the transactions are long running.
Values:
■ FALSE indicates that parallel rollback is disabled
■ LOW limits the number of rollback processes to 2 * CPU_COUNT
■ HIGH limits the number of rollback processes to 4 * CPU_COUNT
TRANSACTIONS
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.
■ Undo Management
RESUMABLE_TIMEOUT
RESUMABLE_TIMEOUT enables or disables resumable statements and specifies
resumable timeout at the system level.
ROLLBACK_SEGMENTS
Syntax ROLLBACK_SEGMENTS =
(segment_name [, segment_name] ... )
Default value If you do not specify this parameter, the instance uses public rollback
segments by default, unless the UNDO_MANAGEMENT initialization
parameter is set to AUTO. In that case, the ROLLBACK_SEGMENTS
parameter is ignored and automatic undo management is used.
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).
You cannot change the value of this parameter dynamically, but you can change its
value and then restart the instance. Although this parameter usually specifies private rollback segments, it can also specify public rollback segments if they are not already in use.
To find the name, segment ID number, and status of each rollback segment in the
database, query the data dictionary view DBA_ROLLBACK_SEGS.
When UNDO_MANAGEMENT is set to AUTO, ROLLBACK_SEGMENTS is ignored.
TRANSACTIONS_PER_ROLLBACK_SEGMENT
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.
You can acquire more rollback segments by naming them in the parameter
ROLLBACK_SEGMENTS.
UNDO_MANAGEMENT
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
UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention.
For AUTOEXTEND undo tablespaces, the system retains undo for at least the time
specified in this parameter, and automatically tunes the undo retention period to
satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the
system automatically tunes for the maximum possible undo retention period, based on
undo tablespace size and usage history, and ignores UNDO_RETENTION unless
retention guarantee is enabled.
The setting of this parameter should account for any flashback requirements of the
system. Automatic tuning of undo retention is not supported for LOBs. The
RETENTION value for LOB columns is set to the value of the UNDO_RETENTION
parameter.
The UNDO_RETENTION parameter can only be honored if the current undo tablespace
has enough space. If an active transaction requires undo space and the undo
tablespace does not have available space, then the system starts reusing unexpired
undo space. This action can potentially cause some queries to fail with a "snapshot too old" message.
The amount of time for which undo is retained for the Oracle Database for the current
undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of
the V$UNDOSTAT dynamic performance view.
UNDO_TABLESPACE
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, then an error will occur and startup will fail.
If the UNDO_TABLESPACE parameter is omitted, the first available undo tablespace in
the database is chosen. If no undo tablespace is available, the instance will start
without an undo tablespace. In such cases, user transactions will be executed using the SYSTEM rollback segment. You should avoid running in this mode under normal
circumstances.
You can replace an undo tablespace with another undo tablespace while the instance is
running.
Others:-
UTL_FILE_DIR
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.
All users can read or write to all files specified by this parameter. Therefore all
PL/SQL users must be trusted with the information in the directories specified by this parameter.
FILEIO_NETWORK_ADAPTERS
FILEIO_NETWORK_ADAPTERS specifies a list of network adapters that can be used to
access the disk storage. On platforms where the database files reside in network
attached storage, this parameter provides the storage access library the list of network adapters that can be used to access the storage.
The netrwork adapter name is a fully qualified address name of the network card that
can be accessed through the host name database or using the Network Information
Service. The components of the adapter name are separated by periods. For example,
the following is a fully qualified adapter name:
ib1.oracle.com
SKIP_UNUSABLE_INDEXES
SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of tables with
unusable indexes or index partitions.
Values:
■ true
Disables error reporting of indexes and index partitions marked UNUSABLE. This
setting allows all operations (inserts, deletes, updates, and selects) on tables with
unusable indexes or index partitions.
Note: If an index is used to enforce a UNIQUE constraint on a table,
then allowing insert and update operations on the table might violate
the constraint. Therefore, this setting does not disable error reporting
for unusable indexes that are unique.
■ false
Enables error reporting of indexes marked UNUSABLE. This setting does not
allow inserts, deletes, and updates on tables with unusable indexes or index
partitions.
SQL_TRACE
SQL_TRACE enables or disables 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.
Caution: Using this initialization parameter to enable the SQL trace
facility for the entire instance can have a severe performance impact.
Enable the facility for specific sessions using the ALTER SESSION
statement. If you must enable the facility on an entire production
environment, then you can minimize performance impact by:
■ Maintaining at least 25% idle CPU capacity
■ Maintaining adequate disk space for the USER_DUMP_DEST
location
■ Striping disk space over sufficient disks
Note: This parameter is deprecated. Oracle recommends that you
use the DBMS_MONITOR and DBMS_SESSION packages instead. SQL_
TRACE is retained for backward compatibility only.
SQLTUNE_CATEGORY
SQLTUNE_CATEGORY specifies the category name for use by sessions to qualify the
lookup of SQL profiles during SQL compilation.
Asm Instance Parameters
ASM_DISKGROUPS
ASM_DISKGROUPS specifies a list of names of disk groups to be mounted by an
Automatic Storage Management instance at instance startup or when an ALTER
DISKGROUP ALL MOUNT statement is issued.
Automatic Storage Management (ASM) automatically adds a disk group to this
parameter when the disk group is successfully created or mounted, and automatically
removes a disk group from this parameter when the disk group is dropped or
dismounted.
Issuing the ALTER DISKGROUP...ALL MOUNT or ALTER DISKGROUP...ALL
DISMOUNT command does not affect the value of this parameter.
ASM_DISKSTRING
ASM_DISKSTRING specifies an operating system-dependent value used by Automatic
Storage Management to limit the set of disks considered for discovery. When a new
disk is added to a disk group, each Automatic Storage Management instance that has
the disk group mounted must be able to discover the new disk using the value of
ASM_DISKSTRING.
In most cases, the default value will be sufficient. Using a more restrictive value may reduce the time required for Automatic Storage Management to perform discovery,
and thus improve disk group mount time or the time for adding a disk to a disk group.
A "?" at the beginning of the string gets expanded to the Oracle home directory.
Depending on the operating system, wildcard characters can be used.
It may be necessary to dynamically change ASM_DISKSTRING before adding a disk so
that the new disk will be discovered.
An attempt to dynamically modify ASM_DISKSTRING will be rejected and the old
value retained if the new value cannot be used to discover a disk that is in a disk
group that is already mounted.
ASM_POWER_LIMIT
ASM_POWER_LIMIT specifies the maximum power on an Automatic Storage
Management instance for disk rebalancing. The higher the limit, the faster rebalancing will complete. Lower values will take longer, but consume fewer processing and I/O resources.
If the POWER clause of a rebalance operation is not specified, then the default power
will be the value of ASM_POWER_LIMIT.
No comments:
Post a Comment