Oracle Startup Parameters
Version 11.2.0.3
 
Documented
Page Index
Sections
Advanced Queuing (AQ) & Streams Database Instance Identification Network & Connectivity
ANSI Compliance Diagnostics / Statistics NLS Parameters
Archive Logging Distributed Replication Optimizer
ASM Exadata RAC
Auditing File Locations Result Cache
Backup & Restore Flashback Security
BFiles I/O Transactions
Buffer Cache Java Undo
Cursors & Library Cache Miscellaneous Undocumented
Data Guard Memory Allocation, Sizing & Usage  
 
Advanced Queuing (AQ) & Streams
AQ_TM_PROCESSES

Default: 0
Required for Advanced Queuing. Valid range of vaues is 0 to 10. Zero means Oracle manages the resource and is recommended for AQ
aq_tm_processes=0
STREAMS_POOL_SIZE

Default: 0
Oracle's Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value. If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value, and the SGA_TARGET parameter is set to 0 (zero), then the Streams pool size is the value specified by the STREAMS_POOL_SIZE parameter, in bytes.

If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool.
streams_pool_size=0
 
ANSI Compliance
BLANK_TRIMMING

Default: FALSE
Specifies the data assignment semantics of character datatypes. 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.
Range of values: {TRUE | FALSE}
blank_trimming=TRUE
 
Archive Logging
ARCHIVE_LAG_TARGET

Default: 0
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.

The ARCHIVE_LAG_TARGET initialization parameter causes Oracle to examine an instance's current online redo log periodically. If the following conditions are met the instance will switch the log:

* The current log was created prior to n seconds ago, and the estimated archival time for the current log is m seconds (proportional to the number of redo blocks used in the current log), where n + m exceeds the value of the ARCHIVE_LAG_TARGET initialization parameter.
* The current log contains redo records.

In an Oracle Real Application Clusters environment, the instance also kicks other threads into switching and archiving logs if they are falling behind. This can be particularly useful when one instance in the cluster is less active than the other instances.

The parameter specifies the target of how many seconds of redo the standby could lose in the event of a primary shutdown or crash. It also provides an upper limit of how long (in the number of seconds) the current log of the primary database can span. Because the estimated archival time is also considered, this is not the exact log switch time.

Value 0 or any integer in [60, 7200]
archive_lag_target=0
LOG_ARCHIVE_DEST Deprecated in Enterprise Edition in favour of log_archive_dest_n
LOG_ARCHIVE_DEST_n

Default: NULL
Defines up to 10 (where n = 1, 2, 3, ... 10) destinations, each of which must specify either the LOCATION or the SERVICE attribute to specify where to archive the redo data. All other attributes are optional. Whether specifying the LOCATION or SERVICE attribute, it must be the first attribute supplied in the list of attributes.

LOG_ARCHIVE_DEST_[1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | .. | 31] =
{ null_string |
{ LOCATION=path_name | SERVICE=service_name }
[ MANDATORY ]
[ REOPEN[=seconds] ]
[ DELAY[=minutes] ]
[ NOREGISTER ]
[ TEMPLATE=template ]
[ ALTERNATE=destination ]
[ DEPENDENCY=destination ]
[ MAX_FAILURE=count ]
[ SYNC | ASYNC ]
[ AFFIRM | NOAFFIRM ]
[ NET_TIMEOUT=seconds ]
[ VALID_FOR=(redo_log_type,database_role) ]
[ DB_UNIQUE_NAME ]
[ MAX_CONNECTIONS=count ]
[ COMPRESSION={ENABLE|DISABLE} ]
}
log_archive_dest_1= 'LOCATION =/app/oracle/product/flash_recovery_area/arch
valid_for=(all_logfiles,all_roles) db_unique_name=proda'

log_archive_dest_2='service=prodb valid_for=(online_logfile,primary_role) db_unique_name=prodb
NET_TIMEOUT=30 REOPEN=60 OPTIONAL MAX_CONNECTIONS=3'
LOG_ARCHIVE_DEST_STATE_n

Default: ENABLE
LOG_ARCHIVE_DEST_STATE_n[1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | .. | 31] =
Specifies the state for log_archive_dest_n.
Range of values: {ENABLE | DEFER | ALTERNATE}
log_archive_dest_state_1='ENABLE'
LOG_ARCHIVE_DUPLEX_DEST

Default: NULL
Similar to LOG_ARCHIVE_DEST 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_dest='/app/oracle/product/flash_recovery_area/arch'
log_archive_duplex_dest='/u02/arch'
LOG_ARCHIVE_FORMAT

Default: Operating system dependent. For OEL %t_%s_%r.dbf
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
%t thread number
%T thread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID ensures unique names are constructed for the archived log files across multiple DB incarnations

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=%t+%s+%r.arc
LOG_ARCHIVE_LOCAL_FIRST
Default: TRUE
Specifies when the archiver processes (ARCn) transmit redo data to remote standby database destinations.
Range of values: {TRUE | FALSE}
log_archive_local_first=TRUE
LOG_ARCHIVE_MAX_PROCESSES

Default: 4
Specifies the number of archiver background processes (ARC0 through ARCn) Oracle initially invokes. If the LOG_ARCHIVE_START initialization parameter has the value true, then this value is evaluated at instance startup though LOG_ARCHIVE_START initialization parameter was deprecated as of 10gR1. Otherwise, this parameter is evaluated when the archiver process is first invoked by SQL*Plus or SQL syntax.
Range of values: {1 .. 40}
log_archive_max_processes=6
LOG_ARCHIVE_MIN_SUCCEED_DEST
Default: 1
The minimum number of destinations that must succeed for the online logfile to be available for reuse.
log_archive_min_succeed_dest=2
LOG_ARCHIVE_TRACE

Default 0
Value Description
0 Disable archivelog tracing
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_archive_trace=128
 
ASM
ASM_DISKGROUPS

Default: NULL
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. Range of values Comma-separated list of strings, of up to 30 characters in length
asm_diskgroups=dgroupA, dgroupB
ASM_DISKSTRING

Default: NULL
An operating system-dependent value used by Automatic Storage Management to limit the set of disks considered for discovery. Value is a comma delimited string of disks.
asm_diskstring='dev/rdsk/*s2, /dev/rdsk/c1*'
ASM_POWER_LIMIT
Default: 1
Specifies the maximum power on an Automatic Storage Management instance for disk rebalancing.
Range of values  {1 to 11}
asm_power_limit=5
ASM_PREFERRED_READ_FAILURE_ GROUPS
Default: NULL
Specifies the failure groups that contain preferred read disks. Preferred disks are instance specific.
TBD
 
Auditing
AUDIT_FILE_DEST
Default: $ORACLE_BASE/admin/ORACLE_SID/adump
The operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set to os.
audit_file_dest=/app/oracle/product/admin/orabase/adump
AUDIT_SYS_OPERATIONS

Default: FALSE
Enables or disables auditing of operations issued by user SYS, and users connecting with SYSDBA or SYSOPER privileges.
Syntax AUDIT_SYS_OPERATIONS = {TRUE | FALSE}
audit_sys_operations=TRUE
AUDIT_SYSLOG_LEVEL

Default: NULL
Allows SYS and standard OS audit records to be written to the system audit log using the SYSLOG utility. If you use this parameter, it is best to assign a file corresponding to every combination of facility and priority (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. If AUDIT_SYSLOG_LEVEL is set and SYS auditing is enabled (AUDIT_SYS_OPERATIONS = TRUE), then SYS audit records are written to the system audit log. If AUDIT_SYSLOG_LEVEL is set and standard audit records are being sent to the operating system (AUDIT_TRAIL = os), then standard audit records are written to the system audit log.

AUDIT_SYSLOG_LEVEL = 'facility_clause.priority_clause'

Facility Clause Options: { USER | LOCAL[0 | 1 | 2 | 3 | 4 | 5 | 6 | 7] | SYSLOG | DAEMON | KERN | MAIL | AUTH | LPR | NEWS | UUCP | CRON }
Priority Options: { NOTICE | INFO | DEBUG | WARNING | ERR | CRIT | ALERT | EMERG }
audit_syslog_level='KERN.EMERG'
AUDIT_TRAIL
Default: DB
Enables or disables database auditing.
Syntax AUDIT_TRAIL = {DB | OS | NONE | TRUE | FALSE | DB_EXTENDED}
audit_trail='DB'
 
Backup and Restore
BACKUP_TAPE_IO_SLAVES

Default: FALSE
Specifies if I/O server processes (also called slaves) are used by Recovery Manager to back up, copy, or restore data to tape.
Range of values TRUE | FALSE
backup_tape_io_slaves=FALSE
CONTROL_FILE_RECORD_KEEP_TIME

Default: 7
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.
Range of values: 0 to 365 (days)
ALTER SYSTEM SET control_file_record_keep_time=30 SCOPE=BOTH;
FAST_START_IO_TARGET Deprecated in favor of FAST_START_MTTR_TARGET
FAST_START_MTTR_TARGET Specify the number of seconds the database takes to perform crash recovery of a single instance. The value Is overridden by FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL so do not set them when using this parameter. The estimated MTTR can be found in v$intance_recovery.
Range of values: 1 to 3600 (seconds)
fast_start_mttr_target=15
RECOVERY_PARALLELISM
Default: System-determined parallel recovery
Specifies the number of processes to participate in instance or crash recovery. To force serial crash and instance recovery, set the parameter to 0 or 1. 0 disables parallel instance and crash recovery on a system that has multiple CPUs. An alert log displays the degree of parallelism that was chosen when the database instance/recovery starts.
recovery_parallelism=0
TAPE_ASYNCH_IO

Default: TRUE
Controls whether I/O to sequential devices (for example, backup or restore of Oracle data to or from tape) is asynchronous.
Range of values: {TRUE | FALSE}
tape_asynch_io=FALSE
 
BFILEs
SESSION_MAX_OPEN_FILES

Default: 10
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.
session_max_open_files=6
 
Buffer Cache and I/O
DB_nK_CACHE_SIZE

Defaults: 0
Specifies the size of the cache for the nK buffers. You can set this parameter only when DB_BLOCK_SIZE has a value other than nK.
Range of values Minimum: 0 (values greater than zero are rounded up to the nearest granule size)
db_4K_cache_size=8M
DB_BLOCK_BUFFERS

Default: 0
Specifies the number of database buffers in the buffer cache. It is one of several parameters that contribute to the total memory requirements of the SGA of an instance. Range of values 50 to an operating system-specific maximum. DB_BLOCK_BUFFERS cannot be combined with the dynamic DB_CACHE_SIZE parameter.
db_block_buffers=10000
DB_BLOCK_SIZE

Default: 8192
Specifies (in bytes) the size of Oracle database blocks. For Real Application Clusters, this parameter affects the maximum value of the FREELISTS storage parameter for tables and indexes. Oracle uses one database block for each freelist group. Decision support system (DSS) and data warehouse database environments tend to benefit from larger block size values.
Range of values 2048 to 32768, but your operating system may have a narrower range
db_block_size=8192
DB_CACHE_ADVICE

Default: ON
Enables or disables statistics gathering used for predicting behavior with different cache sizes through the V$DB_CACHE_ADVICE performance view.
Syntax DB_CACHE_ADVICE = {ON | READY | OFF}
db_cache_advice=READY
DB_CACHE_SIZE

Default 0 ... if SGA_TARGET is set..
Specifies the size of the DEFAULT buffer pool for buffers with the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter).

Syntax DB_CACHE_SIZE = integer [K | M | G]. A value of zero is illegal
db_cache_size=48M
DB_FILE_MULTIBLOCK_READ_COUNT

Default: 8
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 (applies to full table scans and index fast full scans, so non-random I/O.). 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.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

The maximum value is always less than the operating system's maximum I/O size expressed as Oracle blocks ((max I/Osize) / DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, Oracle uses the maximum.

Depending on your exact version and the use of system statistics with version 9+ larger values for the db_file_multiblock_read_count parameter influence the cost based optimizer to favor full table scans over using indexes.
Range of values Operating system-dependent
db_file_multiblock_read_count=128

ALTER SYSTEM RESET db_file_multiblock_read_count SCOPE=SPFILE SID='*';
DB_KEEP_CACHE_SIZE

Default: 0
Specifies the size of the KEEP buffer pool. The size of the buffers in the KEEP buffer pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter). Minimum: 0 (values greater than zero are rounded up to the nearest granule size) Maximum: operating system-dependent.
db_keep_cache_size=128K
DB_RECYCLE_CACHE_SIZE

Default: 0
Specifies the size of the RECYCLE buffer pool. The size of the buffers in the RECYCLE pool is the primary block size (the block size defined by the DB_BLOCK_SIZE initialization parameter). Minimum: 0 (values greater than zero are rounded up to the nearest granule size) Maximum: operating system-dependent.
db_recycle_cache_size=64K
DB_WRITER_PROCESSES
Default: 1 or CPU_COUNT / 8, whichever is greater
Specifies the initial number of database writer processes for an instance.
Range of values: 1 to 36
db_writer_processes=2
DBWR_IO_SLAVES

Default: 0
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.

Range of values: 0 to operating system-dependent
dbwr_io_slaves=1
DISK_ASYNCH_IO

Default: TRUE
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. 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.

Range of values: {TRUE | FALSE}
disk_asynch_io=FALSE
FILESYSTEMIO_OPTIONS

Default: not set
Enables and disables asynchronous I/O or direct I/O on file system files. SETALL enables both. This parameter can not be modified in memory.

Range of values: {NONE | SETALL | DIRECTIO | ASYNCH}
filesystemio_options=SETALL
READ_ONLY_OPEN_DELAYED

Default: FALSE
Determines when datafiles in read-only tablespaces are accessed.
If TRUE the datafiles are accessed for the first time only when an attempt is made to read data stored within them.
If FALSE the datafiles are accessed at database open time.

Range of values: {TRUE | FALSE}
read_only_open_delayed=TRUE
USE_INDIRECT_DATA_BUFFERS

Default: FALSE
Controls how the system global area (SGA) uses memory. It enables or disables the use of the extended buffer cache mechanism for 32-bit platforms that can support more than 4 GB of physical memory. On platforms that do not support this much physical memory it is ignored.

Range of values: {TRUE | FALSE}
use_indirect_data_buffers=TRUE
 
Cursors and Library Cache
CURSOR_SHARING

Default: Exact
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.

Range of values: {SIMILAR | EXACT | FORCE}
cursor_sharing=SIMILAR
CURSOR_SPACE_FOR_TIME Deprecated
OPEN_CURSORS

Default: 50
Specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. This parameter can be used to prevent a session from opening an excessive number of cursors.
Range of values: {0 - 65536}
open_cursors=320
SESSION_CACHED_CURSORS

Default: 50
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.
Range of values: 0 to an operating system dependent limit
session_cached_cursors=220
 
Data Guard
DG_BROKER_CONFIG_FILE1
DG_BROKER_CONFIG_FILE2

Default: Operating system dependent
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.
Range of values per "n" listed: One filename
dg_broker_config_file=$ORACLE_BASE/admin/orabasee/dr1db_orabase.dat
dg_broker_config_file=$ORACLE_BASE/admin/orabase/dr2db_orabase.dat
DG_BROKER_START

Default: FALSE
Enables Oracle to determine whether or not the DMON (Data Guard broker) process should be started.
Range of values: {TRUE | FALSE}
dg_broker_start=TRUE
DB_FILE_NAME_CONVERT

Defalt: NULL
Useful for creating a duplicate database for recovery purposes. You can also use DB_FILE_NAME_CONVERT to rename the datafiles in the clone controlfile when setting up a clone database during tablespace point-in-time recovery.
db_file_name_convert='/dbs/proda/','/dbs/prodb/s_','dbs/proda/ ','dbs/prodb/s_'
DB_LOST_WRITE_PROTECT

Default: NONE
Enables or disables lost write detection. A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write, while in fact the write did not occur in the persistent storage.

When the parameter is set to TYPICAL on the primary database, the instance logs buffer cache reads for read-write tablespaces in the redo log, which is necessary for detection of lost writes.

When the parameter is set to FULL on the primary database, the instance logs reads for read-only tablespaces as well as read-write tablespaces.

When the parameter is set to TYPICAL or FULL on the standby database or on the primary database during media recovery, the instance performs lost write detection.

When the parameter is set to NONE on either the primary database or the standby database, no lost write detection functionality is enabled.

Range of values: {NONE | TYPICAL | FULL}
db_lost_write_protect=FULL
FAL_CLIENT Specifies the FAL (fetch archive log) client name that is used by the FAL service
fal_client=proda
FAL_SERVER Specifies the FAL (fetch archive log) server for a standby database
fal_server=prodb
LOG_ARCHIVE_CONFIG

Default: 'SEND,RECEIVE,NODG_CONFIG'
Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique database names (DB_UNIQUE_NAME) for each database in the Data Guard configuration.
Range of values: {DG_CONFIG | NODG_CONFIG | NORECEIVE | NOSEND | RECEIVE | SEND}
log_archive_config='DG_CONFIG=(proda,prodb)'
LOG_FILE_NAME_CONVERT Converts the filename of a new log file on the primary database to the filename of a log file on the standby database. If you add a log file to the  primary database, you must add a corresponding file to the standby database.
log_file_name_convert='/dbs/proda/','/dbs/prodb/s_','dbs/proda/ ','dbs/prodb/s_'
STANDBY_ARCHIVE_DEST

Default: Operating System Specific
Can be used to specify where archived logs received from a primary database are stored on a standby database. It is no longer necessary to set this parameter, because an appropriate location is automatically chosen.
standby_archive_dest=?/dbs/arch
STANDBY_FILE_MANAGEMENT

Default: MANUAL
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. STANDBY_FILE_MANAGEMENT is only applicable on physical standbys.
Range of values: {MANUAL | AUTO}
standby_file_management=MANUAL
 
Database / Instance / Identification
DB_DOMAIN

Default: NULL
In a distributed database system, DB_DOMAIN specifies the logical location of the database within the network structure.
Range of values is any legal string of name components, separated by periods and up to 128 characters long (including the periods). The value cannot be NULL.
db_domain='MLIB.ORG'
DB_NAME Specifies a database identifier of up to 8 characters. This parameter must be specified and must correspond to the name specified in the CREATE DATABASE statement.
db_name=orabase
 
Diagnostics / Statistics
BACKGROUND_CORE_DUMP

Default: partial
Specifies whether Oracle includes the SGA in the core file for Oracle background processes.
Range of Values: {PARTIAL | FULL}
background_core_dump=partial
BACKGROUND_DUMP_DEST Deprecated
COMMIT_LOGGING Controls how redo is batched by Log Writer. If COMMIT_LOGGING is altered after setting COMMIT_WAIT to FORCE_WAIT, then the FORCE_WAIT option is no longer valid.
Range of Values: {IMMEDIATE | BATCH}
commit_logging=BATCH
CORE_DUMP_DEST
Default: {ORACLE_HOME}/dbs
Primarily a UNIX parameter not supported on all platforms. Specifies the directory for core file dumps
core_dump_dest='/app/oracle/product/diag/rdbms/orabase/orabase/cdump'
DB_BLOCK_CHECKING

Default FALSE
Controls whether Oracle performs block checking for data blocks. When this parameter is set to TRUE, Oracle performs block checking for all data blocks. When it is set to false, Oracle does not perform block checking for blocks in the user tablespaces. However, block checking for the SYSTEM tablespace is always turned on.
Range of values: {TRUE | FALSE}
db_block_checking=TRUE
DB_BLOCK_CHECKSUM

Default: TRUE
Determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read-only if this parameter is TRUE and the last write of the block stored a checksum. In addition, Oracle gives every log block a checksum before writing it to the current log.
Range of values: {TRUE | FALSE}
db_block_checksum=FALSE
DIAGNOSTIC_DEST Replacement for BDUMP and UDUMP related parameters. Points to ADR_BASE
diagnostic_dest='/app/oracle/product'
ENABLE_DDL_LOGGING
Default: FALSE
Enables or disables the writing of DDL statements to the alert log
enable_ddl_logging=TRUE
EVENT

No Default
Debug event control - default null string [CHAR]
Do not alter the value of this parameter except under the supervision of Oracle Support Services staff
MAX_DUMP_FILE_SIZE

Default: UNLIMITED
Specifies the maximum size of trace files (excluding the alert file)
Valid values are: 0 to unlimited in the form <integer>[K | M | G | UNLIMITED]
max_dump_file_size=1G
SHADOW_CORE_DUMP

Default: PARTIAL
Specifies whether Oracle includes the SGA in the core file for foreground (client) processes.
Valid values are: {PARTIAL | FULL | NONE}
shadow_core_dump=FULL
STATISTICS_LEVEL

Default: TYPICAL
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.
Valid values are: {ALL | TYPICAL | BASIC}
statistics_level=ALL
TIMED_OS_STATISTICS

Default: 0
When TRUE statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views
Valid values are: {0 | 5} - 0 for BASIC and TYPICAL, 5 for ALL
timed_os_statistics=5
TIMED_STATISTICS

Default: TRUE
When TRUE statistics are collected and stored in trace files or displayed in the V$SESSTATS and V$SYSSTATS dynamic performance views
Valid values are: {TRUE | FALSE}
timed_statistics=FALSE
TRACE_ENABLED

Default: FALSE
Controls tracing of the execution history, or code path, of Oracle. Oracle Support Services uses this information for debugging.
Valid values are: {TRUE | FALSE}
trace_enable=FALSE
TRACEFILE_IDENTIFIER
No default value is set
Specifies a custom identifier that becomes part of the Oracle Trace file name. A custom identifier is used to identify a trace file simply from its name and without having to open it or view its contents.
tracefile_identifier='test_plan1';
USER_DUMP_DEST Deprecated
 
Distributed Replication
COMMIT_POINT_STRENGTH

Default: 1
Relevant only in distributed database systems. Specifies a value that determines the commit point site in a distributed transaction. The node in the transaction with the highest value for COMMIT_POINT_STRENGTH will be the commit point site.
Range of values: {0 - 255}
commit_point_strength=50
DISTRIBUTED_LOCK_TIMEOUT

Default: 60
Specifies the amount of time (in seconds) for distributed transactions to wait for locked resources.
Range of values: {1 to unlimited}
distributed_lock_timeout=120
GLOBAL_NAMES

Default: FALSE
Specifies whether a database link is required to have the same name as the database to which it connects.
Range of values: {TRUE | FALSE}
global_names=TRUE
HS_AUTOREGISTER

Default: TRUE
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.
Range of values: {TRUE | FALSE}
hs_autoregister=FALSE
OPEN_LINKS

Default: 4
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.
Range of values: {0 - 255}
open_links=10
OPEN_LINKS_PER_INSTANCE

Default: 4
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.
Range of values: {0 - 255}
open_links_per_instance=5
REPLICATION_DEPENDENCY_TRACKING

Default: TRUE
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.
Range of values: {TRUE | FALSE}
replication_dependency_tracking=FALSE
 
Exadata Storage Server
CELL_OFFLOAD_COMPACTION
Default: ADAPTIVE
Cell packet compaction strategy
TBD
CELL_OFFLOAD_PARAMETERS
Default: Not Set
Additional cell offload parameters
TBD
CELL_OFFLOAD_PLAN_DISPLAY
Default: AUTO
Cell offload explain plan display
cell_offload_plan_display=MANUAL
CELL_OFFLOAD_PROCESSING
Default: TRUE
Enable SQL processing offload to cells
cell_offload_processing=FALSE
CELL_PARTITION_LARGE_EXTENTS
Default: DEFAULT
Enables large extent allocation for partitioned tables
TBD
_ALLOW_CELL_SMART_SCAN_ATTR
Default: TRUE
Allow checking smart_scan_capable Attr
TBD
_CELL_FAST_FILE_CREATE
Default: TRUE
Allow optimized file creation path for Cells
TBD
_CELL_FAST_FILE_RESTORE
Default: TRUE
Allow optimized RMAN restore for Cells
TBD
_CELL_FILE_FORMA_CHUNK_SIZE
Default: 0
Cell file format chunk size in MB
_cell_file_format_chunk_size=8192
_CELL_INDEX_SCAN_ENABLED
Default: TRUE
Enable CELL processing of index FFS
TBD
_CELL_RANGE_SCAN_ENABLED
Default: TRUE
Enable CELL processing of index range scans
TBD
_DISABLE_CELL_OPTIMIZED_BACKUPS
Default: FALSE
Disable cell optimized backups
TBD
 
File Locations, Names, and Sizes
CONTROL_FILES

Default: No default set
Control file names list [file_path,file_path..]
*.control_files='c:\oracle\product\oradata\orabase\control01.ctl', 'c:\oracle\product\oradata\orabase\control02.ctl', 'c:\oracle\product\oradata\orabase\control03.ctl'
DB_CREATE_FILE_DEST

Default: NULL
Specifies the default location for Oracle-Managed datafiles (OMF).
db_create_file_dest = <directory | disk group>
db_create_file_dest='/u01/oradata/orabase'
DB_CREATE_ONLINE_LOG_DEST_n

Default: NULL
Specifies the default location for Oracle-managed control files and online redo logs.
Syntax DB_CREATE_ONLINE_LOG_DEST_[1 | 2 | 3 | 4 | 5] = directory | disk group
db_create_online_log_dest_1='/app/oracle/product/oradata/logs'
DB_FILES

Default: 200
Specifies the maximum number of database files that can be opened for this database.
Minimum: the largest among the absolute file numbers of the datafiles in the database
Maximum: operating system-dependent
db_files=100
DB_RECOVERY_FILE_DEST Specifies the default location for the flash recovery area.
Syntax DB_RECOVERY_FILE_DEST = {DIRECTORY | DISK GROUP}
db_recovery_file_dest='c:\oracle\product\flash_recovery_area'
DB_RECOVERY_FILE_DEST_SIZE Specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the fast recovery area
Syntax DB_RECOVERY_FILE_DEST_SIZE = integer [K | M | G]
db_recovery_file_dest_size=4G
FILE_MAPPING

Default: FALSE
Specifies if mapping is enabled. The FMON background process is started to manage mapping information when enabled.
Range of values: {TRUE | FALSE}
file_mapping=FALSE
IFILE
No Default
Embed the named parameter file in the init.ora as it is read.
ifile=?/dbs/proda_dg.ora
SPFILE

No Default
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.
spfile=/app/oracle/product/11.1.0/db_1/dbs/spfileorabase.ora
UTL_FILE_DIR

No Default
Alllow the specification of 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. This parameter should not be used in favor of DIRECTORY objects.
utl_file_dir=*
utl_file_dir=/stage
 
Flashback
DB_FLASHBACK_RETENTION_TARGET

Default: 1440
Specifies the upper limit (in minutes) on how far back in time the database may be flashed back.
Range of values 0 to 232 - 1 (max value represented by 32 bits)
db_flashback_retention_target=2880
RECYCLEBIN
Default: ON
Specifies if the recyclebin holds dropped tables and associated indexes.
Range of values: {ON | OFF}
recyclebin=ON
 
I/O
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.
fileio_metwork_adapters=netapp1.mlib.org
FILESYSTEMIO_OPTIONS

No Default
Enables or disable asynchronous I/O or direct I/O on file system files. This parameter is platform-specific and has a default value that is best for a particular platform.
Range of values: {ASYNCH, DIRECTIO, SETALL, NONE}
filesystemio_options=setall
 
Java
JAVA_JIT_ENABLED

Default: Operating system dependant
Enables or disables the Just-in-Time (JIT) compiler for the Oracle Java Virtual Machine (OracleJVM) environment.
Range of values: {TRUE | FALSE}
java_jit_enabled=TRUE
JAVA_MAX_SESSIONSPACE_SIZE

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

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

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

Default: 1048576 for W2K & Linux
For systems containing bitmap indexes: Specifies the amount of memory Oracle uses to merge bitmaps retrieved from a range scan of the index. The default value is 1 MB. A larger value usually improves performance, because the bitmap segments must be sorted before being merged into a single bitmap. Range of values Operating system-dependent
bitmap_merge_area_size=1048576
BUFFER_POOL_KEEP
Default: NULL
Number of database blocks/latches in keep buffer pool [CHAR: (buffers:n, latches:m)]
buffer_pool_keep=TRUE
BUFFER_POOL_RECYCLE

Default: NULL
Number of database blocks/latches in recycle buffer pool [CHAR: (buffers:n, latches:m)]
Range of values Operating system-dependent
buffer_pool_recycle=TRUE
CREATE_BITMAP_AREA_SIZE

Default: 8388608
Relevant only for systems containing bitmap indexes. It specifies the amount of memory (in bytes) allocated for bitmap creation. The default value is 8 MB. A larger value may speed up index creation.
create_bitmap_area_size=8388608
HASH_AREA_SIZE

Default: Derived: 2 * SORT_AREA_SIZE
Relevant to parallel execution operations and to the query portion of DML or DDL statements. It specifies the maximum amount of memory, in bytes, to be used for hash joins.
hash_area_size=2097152
HI_SHARED_MEMORY_ADDRESS
Default: 0
Specifies the starting address at runtime of the SGA. It is ignored on platforms that specify the SGA's starting address at link time.
hi_shared_memory_address=0
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.
large_pool_size=0
LOCK_SGA

Default: False
Locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space. This parameter is ignored on platforms that do not support it.
lock_sga=FALSE
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.
Range of values: 2 MB to 64 MB (32-bit O/S), 2 MB to 256 MB (64-bit O/S)
log_buffer=4964352
MEMORY_MAX_TARGET

Default: 0
Specifies the maximum value to which a DBA can set the MEMORY_TARGET initialization parameter. See the description of MEMORY_TARGET for more information about how the settings of MEMORY_MAX_TARGET and MEMORY_TARGET affect each other.
memory_max_target=820M
MEMORY_TARGET

Default: 0
Specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.
memory_target=820M
OBJECT_CACHE_MAX_SIZE_PERCENT

Default: 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_max_size_percent=10
OBJECT_CACHE_OPTIMAL_SIZE
Default: 100K
Specifies (in bytes) the size to which the session object cache is reduced when the size of the cache exceeds the maximum size.
object_cache_optimal_size=102400
OLAP_PAGE_POOL_SIZE
Default: 0
Specifies (in bytes) the size of the OLAP page pool.
olap_page_pool_size=0
PGA_AGGREGATE_TARGET
Default: 10 MB or 20% of the size of the SGA, whichever is greater
Specifies the target aggregate PGA memory available to all server processes attached to the instance.
Minimum: 10 MB, Maximum: 4096 GB - 1
pga_aggregate_target=0
PRE_PAGE_SGA

Default: FALSE
Determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.
pre_page_sga=TRUE
SGA_MAX_SIZE Specifies the maximum size of the SGA for the lifetime of the instance.
sga_max_size=4G
SGA_TARGET

Default: 0
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 nonzero 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


Range of values: {64 MB to operating system-dependent}
sga_target=0
SHARED_MEMORY_ADDRESS

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

This parameter to specifies the entire address on 32-bit platforms and to specify the low-order 32 bits of a 64-bit address on 64-bit platforms. Use HI_SHARED_MEMORY_ADDRESS to specify the high-order 32 bits of a 64-bit address on 64-bit platforms. If both parameters are 0 or unspecified, the SGA address defaults to a platform-specific location.
shared_memory_address=0
SHARED_POOL_RESERVED_SIZE
Default: 5% of the value of SHARED_POOL_SIZE
Specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory.
share_pool_reservde_size=16567500
SHARED_POOL_SIZE

Default: 0
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.
shared_pool_size=0
SORT_AREA_RETAINED_SIZE

Default: 0 (Derived from SORT_AREA_SIZE)
Specifies (in bytes) the maximum amount of the user global area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer, which Oracle uses to maintain a portion of the sort in memory. This memory is released back to the UGA, not to the operating system, after the last row is fetched from the sort space.
sort_area_retained_size=0
SORT_AREA_SIZE

Default: 65536
Specifies (in bytes) the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.
sort_area_size=1048576
 
Miscellaneous
COMPATIBLE Allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release.
compatible=11.2.0.0.0
CONTROL_MANAGEMENT_PACK _ACCESS
EE Default: DIAGNOSTIC+TUNING
Specifies which of the Server Manageability Packs should be active.
Range of values: {NONE | DIAGNOSTIC | DIAGNOSTIC+TUNING}
control_management_pack_access=DIAGNOSTIC+TUNING
CPU_COUNT Specifies the number of CPUs available to Oracle. Range of values 0 to unlimited.
cpu_count=2
DB_UNIQUE_NAME Specifies a globally unique name for the database. Databases with the same DB_NAME within the same DB_DOMAIN. Every database's DB_UNIQUE_NAME must be unique within the enterprise.
Syntax DB_UNIQUE_NAME = database_unique_name
db_unique_name=orabase
DB_UNRECOVERABLE_SCN_TRACKING
When TRUE turns off control file writes to update fields that track the highest unrecoverable SCN and Timing during a NOLOGGING Direct Path operation.
Syntax DB_UNRECOVERABLE_SCN_TRACKING = [TRUE | FALSE]
db_unique_name=orabase
DDL_LOCK_TIMEOUT

Default: 0
Specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.
ddl_lock_timeout=0
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.
Format: FIXED_DATE = [YYYY-MM-DD-HH24:MI:SS (or the default date format) | NONE]
fixed_date=NONE
JOB_QUEUE_PROCESSES

Default: 1000
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.
job_queue_processes=1000
LOG_CHECKPOINT_INTERVAL

Default: 0
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 o/s blocks, not database blocks.
Range of values: {0 to 2^31 - 1}
log_checkpoint_interval=0
LOG_CHECKPOINT_TIMEOUT

Default: 1800
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 for more than integer seconds.
Range of values {0 to 2^31 - 1}
log_checkpoint_timeout=1800
LOG_CHECKPOINTS_TO_ALERT

Default: FALSE
True writes checkpoints to the alert file. Doing so is useful for determining whether checkpoints are occurring at the desired frequency and whether exceptions are being generated.
Range of values {TRUE | FALSE}
max_checkpoints_to_alert=TRUE
PLSCOPE_SETTINGS

Default: IDENTIFIERS:NONE
Controls the compile time collection, cross reference, and storage of PL/SQL source code identifier data.
Range of values {IDENTIFIERS:NONE | IDENTIFIERS:ALL}
plscope_settings=IDENTIFIERS:ALL

equivalent to:
ALTER SYSTEM SET plscope_settings = 'IDENTIFIERS:ALL' scope=spfile;
PLSQL_CCFLAGS

Default: empty string
Provides a mechanism that allows PL/SQL programmers to control conditional compilation of each PL/SQL library unit independently.
plsql_ccflags='DEBUG:TRUE'

equivalent to:
ALTER SESSION SET plsql_ccflags = 'DEBUG:TRUE';
PLSQL_CODE_TYPE

Default: INTERPRETED
Specifies the compilation mode for PL/SQL library units.
plsql_code_type=NATIVE

equivalent to:
ALTER SYSTEM SET plsql_code_type = 'NATIVE' SCOPE=BOTH;
PLSQL_DEBUG
Default: FALSE
Specifies whether or not PL/SQL library units will be compiled for debugging.
plsql_debug=FALSE
PLSQL_WARNINGS
Default: DISABLE:ALL
Enables or disables feedback on Critical, Informational, and Performance warnings during PL/SQL object compilation.
plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
PROCESSES

Default: 100
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.
processes=1250
RDBMS_SERVER_DN

No Default
Specifies the Distinguished Name (DN) of the Oracle server. It is used for retrieving Enterprise Roles from an enterprise directory service.
Syntax: rdbms_server_dn=<X.500 Distinguished Name>
TBD
REDO_TRANSPORT_USER

No Default
Specifies the name of the user whose password verifier is used when a remote login password file is used for redo transport authentication. This user must have the SYSDBA or SYSOPER privilege and must have the same password in the database that initiates the redo transport session and in the database that is the target of the redo transport session.
redo_transport_user=uwdba
REMOTE_DEPENDENCIES_MODE

Default: TIMESTAMP
Specifies how Oracle should handle dependencies upon remote PL/SQL stored procedures.
Range of values: {SIGNATURE | TRANSPORT}
remote_dependencies_mode=TIMESTAMP
REMOTE_LISTENER

No Default
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 repositories.
Syntax: remote_listener=<network_name>
TBD
RESOURCE_LIMIT
Default: FALSE
Determines whether resource limits are enforced in database profiles.
resource_limit=TRUE
RESOURCE_MANAGER_CPU_ALLOCATION
Default: The number of logical CPUs reported by the operating system
Specifies the number of CPUs that the Resource Manager should utilize. The Resource Manager controls how a system's CPUs are utilized by its database's sessions.
resource_manager_cpu_allocation=4
RESOURCE_MANAGER_PLAN
No Default
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.
resource_manager_plan='SCHEDULER[0x2C58]:DEFAULT_MAINTENANCE_PLAN'
RESUMABLE_TIMEOUTS

Default: 0 seconds
Enables or disables resumable statements and specifies resumable timeout at the system level.
Range of Values: {0 to 2^31 - 1 (in seconds)}
resumable_timeouts=6000
ROLLBACK_SEGMENTS

Default: No specified as UNDO is used
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).
Range of Values: {Any rollback segment names listed in DBA_ROLLBACK_SEGS except SYSTEM}
TBD
SERIAL_REUSE

Default: Disable
Specifies which types of cursors make use of the serial-reusable memory feature. This feature allocates private cursor memory in the SGA so that it can be reused (serially, not concurrently) by sessions executing the same cursor.
Range of values: {DISABLE | ALL | SELECT | DML | PLSQL | FORCE}
serial_reuse=DISABLE
SERVICE_NAMES

Default: DB_UNIQUE_NAME.DB_DOMAIN if defined
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.
service_name=orabase
SESSIONS

Default: Derived: (1.1 * PROCESSES) + 5
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.
sessions=200
SKIP_UNUSABLE_INDEXES

Default: TRUE
Enables or disables the use and reporting of tables with unusable indexes or index partitions. If a SQL statement uses a hint that forces the usage of an unusable index, then this hint takes precedence over initialization parameter settings, including SKIP_UNUSABLE_INDEXES. If the optimizer chooses an unusable index, then an ORA-01502 error will result.
skip_unusable_indexes=TRUE
SMTP_OUT_SERVER

No Default
Specifies the SMTP host and port to which UTL_MAIL delivers out-bound E-mail. Multiple servers may be specified, separated by commas.
Syntax: SMTP_OUT_SERVER = server_clause [, server_clause ] ...
smtp_out_server=smtp.drizzle.com

Equivalent to:
ALTER SYSTEM SET smtp_out_server = 'smtp.drizzle.com' SCOPE=BOTH;
SQL92_SECURITY

Default: FALSE
The SQL standard specifies 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.
sql92_security=FALSE
SQL_TRACE
Default: FALSE
Enables or disables the SQL trace facility. Setting this parameter to true enables tracing. Deprecated in 11.2.0.3.
sql_trace=TRUE
SQLTUNE_CATEGORY
Default: DEFAULT
Specifies the category name for use by sessions to qualify the lookup of SQL profiles during SQL compilation.
sqltune_category=DEFAULT
SQL_TRANSFORMATION_ENABLED

Default: FALSE
Determines whether a cost-based query transformation will be applied to star queries.
Range of values: {FALSE | TRUE | TEMP_DISABLE}
star_transformation_enabled=TRUE
WORKAREA_SIZE_POLICY

Default: AUTO
Specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.
Range of values: {AUTO | MANUAL}
workarea_size_policy=AUTO
XML_DB_EVENTS

Default: ENABLE
Enables or disables XML DB Events.
Range of values: {ENABLE | DISABLE}
xml_db_events=ENABLE
 
Network & Connections Parameters
CIRCUITS

No Default
Specifies the total number of virtual circuits that are available for inbound and outbound network sessions. It is one of several parameters that contribute to the total SGA requirements of an instance. Do not specify a value unless you want to limit the number of virtual circuits.
circuits=2
DISPATCHERS

Default: If SHARED_SERVERS is greater than 0, then DISPATCHERS defaults to '(PROTOCOL=tcp)', causing 1 TCP/IP dispatcher to be created.
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.

Range of values:
dispatch_clause::= (PROTOCOL = protocol) | (ADDRESS = address) | (DESCRIPTION = description )
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)
dispatchers='(PROTOCOL=TCP) (SERVICE=orabaseXDB)'
dispatchers='(PROTOCOL=TCP)(DISPATCHERS=3)'
LICENSE_MAX_SESSIONS

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

Default: 0
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_max_users=0
LICENSE_SESSIONS_WARNING

Default: 0
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.
license_sessions_warning=0
LISTENER_NETWORKS

No Default
Specifies one or more sets of local & remote listeners for cross-registration. All listeners within the same network_name will cross-register.
Syntax: LISTENER_NETWORKS =
'((NAME=network_name) (LOCAL_LISTENER=["]listener_address[,...]["]) [(REMOTE_LISTENER=["]listener_address[,...]["])])' [,...]
TBD
LOCAL_LISTENER

Default: (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))
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.
Syntax: LOCAL_LISTENER = network_name
local_listener=(PROTOCOL=TCP)(HOST=bigdog.mlog.org)(PORT=1521))
MAX_DISPATCHERS

No Default
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_dispatchers=10
MAX_SHARED_SERVERS

No Default
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.
max_shared_servers=10
SHARED_SERVER_SESSIONS
No Default
Specifies the number of shared server sessions to allow. Setting this parameter enables reserving user sessions for dedicated servers.
shared_server_sessions=60
SHARED_SERVERS

Default: 0 (not enabled)
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.
shared_servers=10
 
NLS Parameters
NLS_CALENDAR

No Default
Specifies which calendar system Oracle uses
Range of values {Arabic Hijrah | English Hijrah | Gregorian | Japanese Imperial | Persian | ROC Official (Republic of China) | Thai Buddha}
TBD
NLS_COMP

Default: BINARY
Specifies which calendar system Oracle uses
Range of values: {BINARY | LINGUISTIC | ANSI}
nls_comp=BINARY
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.
Range of values: Any valid character string, with a maximum of 10 bytes (not including null)
nls_currency=$
NLS_DATE_FORMAT Specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY.
nls_date_format="MM/DD/YYYY"
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_date_language=AMERICAN
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.
TBD
NLS_ISO_CURRENCY Specifies the string to use as the international currency symbol for the C number format element.
TBD
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_language=AMERICAN
NLS_LENGTH_SEMANTICS Enables creationof CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.
nls_length_semantics=BYTE
NLS_NCHAR_CONV_EXCP

Default: FALSE
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_nchar_conv_excp=TRUE
NLS_NUMERIC_CHARACTERS Specifies the characters to use as the group separator and decimal character. It overrides those characters defined implicitly by NLS_TERRITORY. The group separator separates integer groups (that is, thousands, millions, billions, and so on). The decimal separates the integer portion of a number from the decimal portion.
nls_numeric_characters=", "
NLS_SORT Specifies the collating sequence for ORDER BY queries.
Range of values: {BINARY | linguistic_definition}
nls_sort=BINARY
NLS_TERRITORY Specifies the name of the territory whose conventions are to be followed for day and week numbering.
nls_territory=AMERICA
NLS_TIMESTAMP_FORMAT Defines the default timestamp format to use with the TO_CHAR and TO_TIMESTAMP functions.
nls_timestamp_format='YYYY-MM-DD HH:MI:SS.FF'
NLS_TIMESTAMP_TZ_FORMAT Defines the default timestamp with time zone format to use with the TO_CHAR and TO_TIMESTAMP_TZfunctions.
nls_timestamp_tz_format='YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
 
Optimizer
CREATE_STORED_OUTLINES

No Default
Enables automatic outline creation for subsequent queries in the same session. These outlines receive a unique system-generated name and are stored in the DEFAULT category. If a particular query already has an outline defined for it in the DEFAULT category, then that outline will remain and a new outline will not be created.
Syntax CREATE_STORED_OUTLINES = {TRUE | FALSE | CATEGORY_NAME} [NOOVERRIDE]
create_stored_outlines=FALSE
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

Defult: FALSE
Enables or disables the automatic recognition of repeatable SQL statements, as well as the generation of SQL plan baselines for such statements.
optimizer_capture_sql_plan_baselines=TRUE
OPTIMIZER_DYNAMIC_SAMPLING

Default: 2
Controls the level of dynamic sampling performed by the optimizer.
Range of values: {0 - 10}
optimizer_dynamic_sampling=2
OPTIMIZER_FEATURES_ENABLE
Default: 11.2.0.1
Acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
optimizer_features_enable=11.1.0.7
OPTIMIZER_INDEX_CACHING A point that is often overlooked with optimizer_index_caching is that it supposed to apply only for index costs during nested loops (although it does seem to be applied for inlist iterators).

Consequently it is not really a measure of what fraction of an index is cached, it is a measure of how many rows I
select from a driving table that require me to collect the same data from the inner table repeatedly.

For example:
If I select 5 rows from table A and then join to table B, it is possible that I will do a physical read for the index blocks for tableB after reading the first row from table A - but then use the same index blocks for the join to the next 4 rows from table A.

In this case, an optimizer_index_caching value of 80% would be the representative value.

by: Jonathan Lewis / 4 Nov 2004

A relevant metalink note (62286.1) would suggest that Oracle intend the
value to be considered for in-list as well as NL.

by: Niall Litchfield
optimizer_index_caching=20
OPTIMIZER_INDEX_COST_ADJ

Default: 100 (%)
The default instructs the optimizer to use its default index use costing. A lower value makes the cost of index usage lower and a larger value makes the cost of index usage appear more expensive.
Range of values 1 to 10000
optimizer_index_cost_adj=90
OPTIMIZER_MODE

Default: ALL_ROWS
The default behavior of the instance's optimization approach
Range of values: {first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows}
optimizer_mode='ALL_ROWS'
OPTIMIZER_SECURE_VIEW_MERGING

Default: TRUE
Enables the optimizer to use view merging to improve query performance without performing the checks that would otherwise be performed to ensure that view merging does not violate any security intentions of a view's creator.
Range of values: {TRUE | FALSE}
optimizer_secure_view_merging=TRUE
OPTIMIZER_USE_INVISIBLE_INDEXES

Default: FALSE
Enables or disables the use of invisible indexes by the CBO
Range of values: TRUE FALSE
optimizer_use_invisible_indexes=FALSE
OPTIMIZER_USE_PENDING_STATISTICS

Default: FALSE
Specifies if the optimizer can use pending statistics when compiling SQL statements
Range of values: {TRUE | FALSE}
optimizer_use_pending_statistics=FALSE
OPTIMIZER_USE_SQL_PLAN_BASELINES

Default: TRUE
Enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management Base, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.
Range of values: {TRUE | FALSE}
optimizer_use_sql_plan_baselines=TRUE
PLSQL_OPTIMIZE_LEVEL

Default: 2
Specifies the optimization level that will be used to compile PL/SQL library units. The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units.
Range of values: 0 - 3
plsql_optimize_level=3
QUERY_REWRITE_ENABLED

Default: TRUE
Allows you to enable or disable query rewriting globally for the database.
Range of values: {FALSE | FORCE | TRUE}
query_rewrite_enabled=TRUE
QUERY_REWRITE_INTEGRITY

Default: ENFORCED
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.
Range of values: {ENFORCED |  STALE _TOLERATED | TRUSTED}
query_rewrite_integrity='TRUSTED'
 
Real Application Clusters
ACTIVE_INSTANCE_COUNT

Default: NULL
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.

Range of values 1 or >= the number of instances in the cluster. (Values other than 1 have no effect on the active or standby status of any instances.)
ACTIVE_INSTANCE_COUNT=6
CLUSTER_DATABASE

Default: FALSE
Specifies whether or not Real Application Clusters is enabled.
Range of values: {TRUE | FALSE}
cluster_database=TRUE
CLUSTER_DATABASE_INSTANCES

Default: 1
Specifies the number of instances that are configured as part of a RAC database.
Range of values Any nonzero value
cluster_database_instances=4
CLUSTER_INTERCONNECTS

Default: NULL
Range of values One or more IP addresses, separated by colons. Provides information about additional cluster interconnects available for use in a RAC environments.

This parameter can be used to override the default interconnect with a preferred cluster traffic network. This parameter is useful in Data Warehouse systems that have reduced availability requirements and high interconnect bandwidth demands. You can also use CLUSTER_INTERCONNECTS to override the default interconnect chosen by Oracle.

For example, if you are running two instances of Oracle for two databases on the same machine, then you can load balance the interconnect traffic to different physical interconnects. This does not reduce Oracle availability.
cluster_interconnects=10.2.1.1:10.2.1.2:10.2.1.3:10.2.1.4
GCS_SERVER_PROCESSES

Default depends on CPUs and whether stand-alone, ASM, or RAC
The number of background GCS server processes (LMS0, ... LMS9 and LMSa, ... LMSz) to serve inter-instance traffic among Oracle RAC instances. GCS server processes are only seen in an Oracle RAC environment.
Range of values: 0 - 36
gcs_server_processes=16
INSTANCE_GROUPS

No Default
A RAC 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_GROUPS = group_name [, group_name ] ...

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.

This parameter specifies one or more instance groups and assigns the current instance to those groups. If one of the specified groups is also specified in the PARALLEL_INSTANCE_GROUP parameter, then Oracle allocates query processes for a parallel operation from this instance.
instance_groups=TSTA1,TSTA2,TSTA3,TSTA4
INSTANCE_NAME

Default: The instance's SID
In a Real Application Clusters environment, multiple instances can be associated with a single database service. Clients can override Oracle's connection load balancing by specifying a particular instance by which to connect to the database. INSTANCE_NAME specifies the unique name of this instance.
instance_name=orabase
INSTANCE_NUMBER Lowest available number; derived from instance start up order and INSTANCE_NUMBER value of other instances. If not configured for Real Application Clusters, then 0.

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

The INSTANCE parameter of the ALTER TABLE ... ALLOCATE EXTENT statement assigns an extent to a particular free list group. If you set INSTANCE_NUMBER to the value specified for the INSTANCE parameter, the instance uses that extent for inserts and for updates that expand rows.

The practical maximum value of this parameter is the maximum number of instances specified in the CREATE DATABASE statement. The absolute maximum is operating system-dependent.
instance_number=0
INSTANCE_TYPE

Default: 0
An Oracle RAC parameter that can be specified in parallel mode or exclusive mode. It specifies a unique number that maps the instance to one free list group for each database object created with storage parameter FREELIST GROUPS.
instance_type=RDBMS
PARALLEL_ADAPTIVE_MULTI_USER

Default: TRUE
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.
Range of values: {TRUE | FALSE}
parallel_adaptive_multi_user=TRUE
PARALLEL_DEGREE_LIMIT

Default: CPU
With automatic degree of parallelism, Oracle automatically decides whether or not a statement should execute in parallel and what degree of parallelism the statement should use. The optimizer automatically determines the degree of parallelism for a statement based on the resource requirements of the statement. However, the optimizer will limit the degree of parallelism used to ensure parallel server processes do not flood the system. The limit is enforced by PARALLEL_DEGREE_LIMIT.
Range of values: {CPU | IO | <integer>}
parallel_degree_limit=CPU
PARALLEL_DEGREE_POLICY

new in 11.2

DEFAULT: MANUAL
When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution but that cannot obtain the necessary parallel servers processes due to a lack of system resources. Once the necessary resources become available the SQL statement will be dequeued and allowed to execute. The queue is a simple First In First Out queue based on the time a SQL statement was executed.

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED, Oracle will also decide if an object that will be accessed using parallel execution would benefit from being cached in the SGA (buffer cache). The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In a RAC environment Oracle will affinitize or map piece of the object into each of the buffer caches on the active instances by fileNumber and extentNumber unless the object is hash partitioned and the number of hash partitions is larger than the number of nodes. By creating this mapping, Oracle will automatically know which buffer cache to access to find different parts or partitions of the object. Using this information Oracle will prevent multiple instances from reading the same information from disk over and over again. If the size of the object is larger than the size of the buffer cache or the size of the buffer cache multiplied by the number of active instances in a RAC cluster than it will be read using direct path reads.
Syntax PARALLEL_DEGREE_POLICY = {MANUAL | LIMITED | AUTO}
parallel_degree_limit=AUTO
PARALLEL_EXECUTION_MESSAGE_SIZE
Default: Operating System and Compatible Parameter value dependent
The size of messages used for parallel execution (formerly parallel query, PDML, Parallel Recovery, replication).
Range of values : 2148 to 32768
parallel_execution_message_size=16384
PARALLEL_FORCE_LOCAL

Default: FALSE
On a RAC cluster forces parallel query slaves to stay on the local instance and not parallelize across nodes increasing interconnect traffic.
Range of values: {TRUE | FALSE}
parallel_force_local=TRUE
PARALLEL_INSTANCE_GROUP

No Default
PARALLEL_INSTANCE_GROUP = group_name

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.
parallel_instance_group='SYS$USERS'
PARALLEL_IO_CAP_ENABLED

Default: FALSE
Specifies whether or not Oracle caps the default degree of parallelism to no greater than what the I/O system can support. The value is calculated using DBMS_RESOURCE_MANAGER.CALIBRATE_IO. If PARALLEL_IO_CAP_ENABLED is set to true and the database I/O capacity has been calibrated, then Oracle will reduce the default degree of parallelism if the I/O capacity cannot support the available number of CPUs.
Range of values: {TRUE | FALSE}
parallel_io_cap_enabled=TRUE
PARALLEL_MAX_SERVERS

Default: 20
?
parallel_max_servers=10
PARALLEL_MIN_PERCENT
Default: 0
?
parallel_min_percent=0
PARALLEL_MIN_SERVERS
Default: 0
?
parallel_min_servers=0
PARALLEL_MIN_TIME_THRESHOLD
Default: AUTO
?
parallel_min_time_threshold=AUTO
PARALLEL_SERVER
Default: FALSE
?
parallel_server=TRUE
PARALLEL_SERVER_INSTANCES
Default: 1
?
parallel_server_instances=2
PARALLEL_SERVERS_TARGET
Default: 8
?
parallel_servers_target=4
PARALLEL_THREADS_PER_CPU
Default: 2
?
parallel_threads_per_cpu=4
THREAD

Default:0
THREAD has been superseded by the INSTANCE_NAME and INSTANCE_NUMBER parameters, and will be made obsolete in a future release of the Oracle Database.
thread=0
 
Result Cache
CLIENT_RESULT_CACHE_LAG

Default: 3000
Specifies the maximum time (in milliseconds) since the last round trip to the server, before which the OCI client query execute makes a round trip to get any database changes related to the queries cached on the client.
client_result_cache_lag=3000
CLIENT_RESULT_CACHE_SIZE

Default: 0
Specifies the maximum size of the client per-process result set cache (in bytes). All OCI client processes inherit this maximum size. Setting a nonzero value enables the client query cache feature. This can be overridden by the client configuration parameter OCI_RESULT_CACHE_MAX_SIZE.
Range of values: {0 to operating system dependent}
client_result_cache_size=0
RESULT_CACHE_MAX_RESULT
Default: 5%
Specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use.
result_cache_max_result=7
RESULT_CACHE_MAX_SIZE

Default: Derived
Specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. Values of this parameter greater than 0 are rounded up to the next multiple of 32 KB. If the value of this parameter is 0, then the feature is disabled.
result_cache_max_size=2129920
RESULT_CACHE_MODE

Default: MANUAL
Specifies when a ResultCache operator is spliced into a query's execution plan.
Range of Values: {MANUAL | FORCE}
result_cache_mode=MANUAL
RESULT_CACHE_REMOTE_EXPIRATION

Default: 0
Specifies the number of minutes that a result using a remote object is allowed to remain valid. Setting this parameter to 0 implies that results using remote objects should not be cached. Setting this parameter to a nonzero value can produce stale answers (for example, if the remote table used by a result is modified at the remote database).
result_cache_remote_expiration=0
 
Security
O7_DICTIONARY_ACCESSIBILITY
Default: FALSE
Version 7 Dictionary Accessibility support. Range of values TRUE or FALSE
07_dictionary_accessibility=FALSE
DB_SECUREFILE

Default: PERMITTED
Specifies whether to treat LOB files as SecureFiles. Requires the tablespace is created with SEGMENT SPACE MANAGEMENT = AUTO
Range of values: {NEVER | PERMITTED | ALWAYS | IGNORE}
db_securefile=PERMITTED
DB_ULTRA_SAFE

Default: OFF
Sets the default values for other parameters that control protection levels.
Range of values: {OFF | DATA_ONLY | DATA_AND_INDEX}
db_ultra_safe=OFF
LDAP_DIRECTORY_ACCESS

Default: NONE
Specifies whether Oracle refers to Oracle Internet Directory for user authentication information. If directory access is turned on, then this parameter also specifies how users are authenticated.
Range of values: {NONE | PASSWORD | SSL}
ldap_directory_access=NONE
LDAP_DIRECTORY_SYSAUTH
Default: NO
Enables or disables directory-based authorization for SYSDBA and SYSOPER.
ldap_directory_sysauth=NO
OS_AUTHENT_PREFIX

Default: OPS$
Specifies a prefix that Oracle uses to authenticate users attempting to connect to the server. Oracle concatenates the value of this parameter to the beginning of the user's operating system account name and password. When a connection request is attempted, Oracle compares the prefixed username with Oracle usernames in the database.
os_authent_prefix=OPS$
OS_ROLES
Default: FALSE
Determines whether Oracle or the operating system identifies and manages the roles of each username.
os_rules=FALSE
REMOTE_LOGIN_PASSWORD_FILE

Default: EXCLUSIVE
Specifies whether Oracle checks for a password file.
Range of Values: {SHARED | EXCLUSIVE | NONE}
remote_login_passwordfile='EXCLUSIVE'
REMOTE_OS_AUTHENT
Default:FALSE
Specifies whether remote clients will be authenticated with the value of the OS_AUTHENT_PREFIX parameter.
remote_os_authent=FALSE
REMOTE_OS_ROLES

Default:FALSE
Specifies whether operating system roles are allowed for remote clients. The default value, false, causes Oracle to identify and manage roles for remote clients.
remote_os_rules=FALSE
SEC_CASE_SENSITIVE_LOGON
Default: TRUE
Enables or disables password case sensitivity in the database.
sec_case_sensitive_logon=TRUE
SEC_MAX_FAILED_LOGIN_ATTEMPTS

Default: 10
Specifies the number of authentication attempts that can be made by a client on a connection to the server process. After the specified number of failure attempts, the connection will be automatically dropped by the server process.
sec_max_failed_login_attempts=3
SEC_PROTOCOL_ERROR_FURTHER_ACTION

Default: CONTINUE
Specifies the further execution of a server process when receiving bad packets from a possibly malicious client.
Range of Values: {CONTINUE | DELAY <integer> | DROP <integer>}
sec_protocol_error_further_action=CONTINUE
SEC_PROTOCOL_ERROR_TRACE_ACTION

Default: TRACE
Specifies the action that the database should take when bad packets are received from a possibly malicious client.
Range of Values: {NONE | TRACE | LOG | ALERT}
sec_protocol_eror_trace_action=TRACE
SEC_RETURN_SERVER_RELEASE_BANNER
Default: FALSE
Specifies whether or not the server returns complete database software information to clients.
sec_return_server_release_banner=FALSE
 
Transactions
COMMIT_WAIT

No Default
An advanced parameter used to control when the redo for a commit is flushed to the redo logs.
Range of values: {NOWAIT | WAIT | FORCE_WAIT}
commit_wait=NOWAIT
COMMIT_WRITE

Default: IMMEDIATE WAIT
If this parameter is not explicitly specified, then database commit behavior defaults to writing commit records to disk before control is returned to the client.
Range of values: '{IMMEDIATE | BATCH},{WAIT |NOWAIT}'
commit_write='BATCH, NOWAIT'
DML_LOCKS Default value Derived: 4 * TRANSACTIONS. 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.
Range of values 20 to unlimited; a setting of 0 disables enqueues
dml_locks=748
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
Range of values {HI | LO | FALSE}
fast_start_parallel_rollback=LOW
GLOBAL_TXN_PROCESSES

Default: 1
Specifies the initial number of GTXn background processes (GTX0, ... GTX9 and GTXa, ... GTXj) per instance to support global (XA) transactions in an Oracle RAC environment.
Range of values:{0 to 20}
global_txn_processes=3
TRANSACTIONS

Default: Derived: (1.1 * SESSIONS)
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.
Range of values: {4 to 2^32}
transactions=187
TRANSACTIONS_PER_ROLLBACK _SEGMENT

Default 5
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.
transactions_per_rollback_segment=6
 
Undo Tablespace
UNDO_MANAGEMENT

Default: AUTO
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.
Range of values: {MANUAL | AUTO}
undo_management=AUTO
UNDO_RETENTION

Default: 900
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.
Range of values: 0 to 2^31-1
undo_retention=1440
UNDO_TABLESPACE
No Default
Name of the undo tablespace to be used during startup.
undo_tablespace='UNDOTBS1'
 
Undocumented
Query for undocumented initialization parameters set pagesize 35
set linesize 150
col NAME format a40
col VALUE format a20
col DESCRIPTION format a60
set pause on
set pause 'Hit enter to continue'

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND SUBSTR(x.ksppinm,1,1) = '_'
ORDER BY 1;
__DB_CACHE_SIZE Undocumented
__db_cache_size=136M
__JAVA_CACHE_SIZE Undocumented
__java_pool_size=12M
__LARGE_POOL_SIZE Undocumented
__large_pool_size=4M
__ORACLE_BASE Set from the environment
__oracle_base='c:\oracle\product'
__PGA_AGGREGATE_TARGET Undocumented
__pga_aggregate_target=308M
__SGA_TARGET Undocumented
__sga_target=512M
__SHARED_IO_POOL_SIZE Undocumented
__shared_io_pool_size=48M
__SHARED_POOL_SIZE Undocumented
__shared_pool_size=300M
__STREAMS_POOL_SIZE Undocumented
__streams_pool_size=4M
_ALWAYS_ANTI_JOIN Undocumented
_always_anti_join='CHOOSE'
_ALWAYS_SEMI_JOIN Undocumented
_always_semi_join='CHOOSE'
_AWR_DISABLED_FLUSH_TABLES Undocumented
TBD
_B_TREE_BITMAP_PLANS
Default: TRUE
Undocumented
_b_tree_bitmap_plans=TRUE
_BCT_BITMAPS_PER_FILE
Default: 8
Number of bitmaps to store for each datafile
_bct_bitmaps_per_file=12
_BCT_BUFFER_ALLOCATION_MAX
Default: 104857600
maximum size of all change tracking buffer allocations, in bytes
TBD
_BCT_BUFFER_ALLOCATION_MIN_EXTENTS
Default: 1
mininum number of extents to allocate per buffer alloca
_bct_buffer_allocation_min_extents=2;
_BCT_BUFFER_ALLOCATION_SIZE
Default: 2097152
size of one change tracking buffer allocation, in bytes
TBD
_BCT_CHUNK_SIZE
Default: 0
change tracking datafile chunk size, in bytes
TBD
_BCT_CRASH_RESERVE_SIZE
Default: 262144
change tracking reserved crash recovery SGA space, in bytes
_bct_crash_reserve_size=393216;
_BCT_FILE_BLOCK_SIZE
Default: 0
block size of change tracking file, in bytes
_bct_file_block_size=8192;
_BCT_FILE_EXTENT_SIZE
Default: 0
extent size of change tracking file, in bytes
_bct_file_extent_size=65536;
_BCT_FIXTAB_FILE
Default: NULL
change tracking file for fixed tables
_bct_fixtab_file='/u01/oradata/flash_recovery_area/bct/bctfile.bct';
_BCT_INITIAL_PRIVATE_DBA_BUFFER_SIZE
Default: 0
initial number of entries in the private change tracking dba buffers
_bct_initial_private_dba_buffer_size=8192;
_BCT_PUBLIC_DBA_BUFFER_SIZE
Default: 0
total size of all public change tracking dba buffers, in bytes
_bct_public_dba_buffer_size=104857600
_BLOOM_FILTER_ENABLED
Default: TRUE
Undocumented
_bloom_filter_enabled=TRUE
_BLOOM_FOLDING_ENABLED
Default: TRUE
Undocumented
_bloom_folding_enabled=TRUE
_BLOOM_PRUNING_ENABLED
Default: TRUE
Undocumented
_bloom_pruning_enabled=TRUE
_COMPLEX_VIEW_MERGING Undocumented
_complex_view_merging=TRUE
_CONVERT_SET_TO_JOIN Undocumented
_convert_set_to_join=FALSE
_CORRUPTED_ROLLBACK_SEGMENTS

Default: TRUE
Marks an undo segment as corrupt so it can be dropped
SQL> select segment_name, tablespace_name, status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------------
SYSTEM                         SYSTEM                         ONLINE
_SYSSMU1_1222867085$           UNDOTBS1                       NEEDS RECOVERY
_SYSSMU20_1235038139$          UNDOTBS2                       ONLINE
_SYSSMU19_1235038139$          UNDOTBS2                       ONLINE
_SYSSMU18_1235038139$          UNDOTBS2                       ONLINE
_SYSSMU17_1235038139$          UNDOTBS2                       ONLINE
_SYSSMU16_1235038139$          UNDOTBS2                       ONLINE
_SYSSMU15_1235038139$          UNDOTBS2                       ONLINE
_SYSSMU14_1235038139$          UNDOTBS2                       ONLINE
_SYSSMU13_1235038139$          UNDOTBS2                       ONLINE
_SYSSMU12_1235038139$          UNDOTBS2                       ONLINE
_SYSSMU11_1235038139$          UNDOTBS2                       ONLINE

SQL> create pfile from spfile;

SQL> shutdown abort

-- edit pfile and add
_corrupted_rollback_segments=('_SYSSMU1_1222867085$')

SQL> create spfile from pfile

SQL> startup

SQL> drop rollback segment "_SYSSMU1_1222867085$";
_COST_EQUALITY_SEMI_JOIN Undocumented
_cost_equality_semi_join=TRUE
_CPU_TO_IO Undocumented
_cpu_to_io=0
_DB_ALWAYS_CHECK_SYSTEM_TS

Default: TRUE
When set to FALSE stops Oracle from check for corrupt blocks in the SYSTEM tablespace during startup
SQL> ALTER SYSTEM SET "_db_always_check_system_ts" = FALSE;
SQL> ALTER SYSTEM SET db_block_checksum = FALSE;
_DB_WRITER_FLUSH_IMU

Default: TRUE
Alows Oracle the ability to artificually age a transaction for increased automatic cache management. Required for In-Memory Undo
SQL> ALTER SYSTEM SET "_db_writer_flush_imu" = FALSE;
_DIMENSION_SKIP_NULL Undocumented
_dimension_skip_null=TRUE
_ELIMINATE_COMMON_SUBEXPR Undocumented
_eliminate_common_subexpr=TRUE
_ENABLE_TYPE_DEP_SELECTIVITY Undocumented
_enable_type_dep_selectivity=TRUE
_FAST_FULL_SCAN_ENABLED Undocumented
_fast_full_scan_enabled=TRUE
_FIRST_K_ROWS_DYNAMIC_PRORATION Undocumented
_first_k_rows_dynamic_proration=TRUE
_GBY_HASH_AGGREGATION_ENABLED Undocumented
_gby_hash_aggregation_enabled=TRUE
_GENERALIZED_PRUNING_ENABLED Undocumented
_generalized_pruning_enabled=TRUE
_GLOBALINDEX_PNUM_FILTER_ENABLED Undocumented
_globalindex_pnum_filter_enabled=TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED Undocumented
_gs_anti_semi_join_allowed=TRUE
_HIGH_PRIORITY_PROCESSES High priority process name mask

More information
_high_priority_processes=LMS*|VKTM
_IMPROVED_OUTERJOIN_CARD Undocumented
_improved_outerjoin_card=TRUE
_IMPROVED_ROW_LENGTH_ENABLED Undocumented
_improved_row_length_enabled=TRUE
_IMU_POOLS
Default: 3
Determines the number of pools used by In-Memory Undo
_imu_pools=5
_IN_MEMORY_UNDO

Default: TRUE
Determines whether In-Memory Undo is active
_in_memory_undo=FALSE
_INDEX_JOIN_ENABLED Undocumented
_index_join_enabled=TRUE
_KSB_RESTART_POLICY_TIMES Undocumented
_ksb_restart_policy_times='0'
_ksb_restart_policy_times
='60'
_ksb_restart_policy_times='120'
_ksb_restart_policy_times='240' # internal update to set default
_LEFT_NESTED_LOOPS_RANDOM Undocumented
_left_nested_loops_random=TRUE
_LOCAL_COMMUNICATION_COSTING_ENABLED Undocumented
_local_communication_costing_enabled=TRUE
_LOG_IO_SIZE
Default: 0
Automatically initiate log write if this many redo blocks in buffer
_log_io_size=60
_MEMORY_BROKER_STAT_INTERVAL Sets the interval between AMM checks to resize the SGA. The range of values is 1 to 999 seconds
_minimal_stats_aggregation=999
_MINIMAL_STATS_AGGREGATION Undocumented
_minimal_stats_aggregation=TRUE
_MMV_QUERY_REWRITE_ENABLED Undocumented
_mmv_query_rewrite_enabled=TRUE
_NEW_INITIAL_JOIN_ORDERS Undocumented
_new_initial_join_orders=TRUE
_NEW_SORT_COST_ESTIMATE Undocumented
_new_sort_cost_estimate=TRUE
_NEWSORT_ENABLED

Default: TRUE
Activates new sorting algorithm in 10gR2 and above
Range of values: {TRUE | FALSE}
alter session set "_newsort_enabled"=FALSE;
_NLJ_BATCHING_ENABLED Undocumented
_nlj_batching_enabled=1
_OPTIM_ADJUST_FOR_PART_SKEWS Undocumented
_optim_adjust_for_part_skews=TRUE
_OPTIM_ENHANCE_NNULL_DETECTION Undocumented
_optim_enhance_nnull_detection=TRUE
_OPTIM_NEW_DEFAULT_JOIN_SEL Undocumented
_optim_new_default_join_sel=TRUE
_OPTIM_PEEK_USER_BINDS Undocumented
_optim_peek_user_binds=TRUE
_OPTIMIZER_ADAPTIVE_CURSOR_SHARING Undocumented
_optimizer_adaptive_cursor_sharing=TRUE
_OPTIMIZER_BETTER_INLIST_COSTING Undocumented
_optimizer_better_inlist_costing='ALL'
_OPTIMIZER_DBQT_NO_SIZE_RESTRICTION Undocumented
_optimizer_cbqt_no_size_restriction=TRUE
_OPTIMIZER_COMPLEX_PRED_SELECTIVITY Undocumented
_optimizer_complex_pred_selectivity=TRUE
_OPTIMIZER_COMPUTER_INDEX_STATS Undocumented
_optimizer_compute_index_stats=TRUE
_OPTIMIZER_CONNECTD_BY_COMBINE_SW Undocumented
_optimizer_connect_by_combine_sw=TRUE
_OPTIMIZER_CONNECT_BY_COST_BASED Undocumented
_optimizer_connect_by_cost_based=TRUE
_OPTIMIZER_CORRECTD_SQL_SELECTIVITY Undocumented
_optimizer_correct_sq_selectivity=TRUE
_OPTIMIZER_COST_BASED_TRANSFORMATION Undocumented
_optimizer_cost_based_transformation='LINEAR'
_OPTIMIZER_COST_HJSMJ_MULTIMATCH Undocumented
_optimizer_cost_hjsmj_multimatch=TRUE
_OPTIMIZER_COST_MODEL Undocumented
_optimizer_cost_model='CHOOSE'
_OPTIMIZER_DIM_SUBQ_JOIN_SEL Undocumented
_optimizer_dim_subq_join_sel=TRUE
_OPTIMIZER_DISTINCT_ELIMINATION Undocumented
_optimizer_distinct_elimination=TRUE
_OPTIMIZER_ENABLE_DENSITY_IMPROVEMENTS Undocumented
_optimizer_enable_density_improvements=TRUE
_OPTIMIZER_ENABLE_EXTENDED_STATS Undocumented
_optimizer_enable_extended_stats=TRUE
_OPTIMIZER_ENHANCED_FILTER_PUSH Undocumented
_optimizer_enhanced_filter_push=TRUE
_OPTIMIZER_EXTEND_JPPD_VIEW_TYPES Undocumented
_optimizer_extend_jppd_view_types=TRUE
_OPTIMIZER_EXTENDED_CURSOR_SHARING Undocumented
_optimizer_extended_cursor_sharing='UDO'
_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL Undocumented
_optimizer_extended_cursor_sharing_rel='SIMPLE'
_OPTIMIZER_EXTENDED_STATS_USAGE_CONTROL Undocumented
_optimizer_extended_stats_usage_control=224
_OPTIMIZER_FILTER_PRED_PULLUP Undocumented
_optimizer_filter_pred_pullup=TRUE
_OPTIMIZER_FKR_INDEX_COST_BIAS Undocumented
_optimizer_fkr_index_cost_bias=10
_OPTIMIZER_GROUP_BY_PLACEMENT Undocumented
_optimizer_group_by_placement=TRUE
_OPTIMIZER_IMPROVE_SELECTIVITY Undocumented
_optimizer_improve_selectivity=TRUE
_OPTIMIZER_INVALIDATION_PERIOD

Default:
time window for invalidation of cursors of analyzed obj
_optimizer_invalidate_period=4140
_OPTIMIZER_JOIN_ELIMINATION_ENABLED Undocumented
_optimizer_join_elimination_enabled=TRUE
_OPTIMIZER_JOIN_ORDER_CONTROL Undocumented
_optimizer_join_order_control=3
_OPTIMIZER_JOIN_SEL_SANITY_CHECK Undocumented
_optimizer_join_sel_sanity_check=TRUE
_OPTIMIZER_MAX_PERMUTATIONS Undocumented
_optimizer_max_permutations=2000
_OPTIMIZER_MODE_FORCE Undocumented
_optimizer_mode_force=TRUE
_OPTIMIZER_MULTI_LEVEL_PUSH_PRED Undocumented
_optimizer_multi_level_push_pred=TRUE
_OPTIMIZER_NATIVE_FULL_OUTER_JOIN Undocumented
_optimizer_native_full_outer_join='FORCE'
_OPTIMIZER_NEW_JOIN_CARD_COMPUTATION Undocumented
_optimizer_new_join_card_computation=TRUE
_OPTIMIZER_NULL_AWARE_ANTIJOIN Undocumented
_optimizer_null_aware_antijoin=TRUE
_OPTIMIZER_OR_EXPANSION Undocumented
_optimizer_or_expansion='DEPTH'
_OPTIMIZER_ORDER_BY_ELIMINATION_ENABLED Undocumented
_optimizer_order_by_elimination_enabled=TRUE
_OPTIMIZER_OUTER_TO_ANTI_ENABLED Undocumented
_optimizer_outer_to_anti_enabled=TRUE
_OPTIMIZER_PUSH_DOWN_DISTINCT Undocumented
_optimizer_push_down_distinct=0
_OPTIMIZER_PUSH_PRED_COST_BASED Undocumented
_optimizer_push_pred_cost_based=TRUE
_OPTIMIZER_ROWNUM_BIND_DEFAULT Undocumented
_optimizer_rownum_bind_default=10
_OPTIMIZER_ROWNM_PRED_BASED_FKR Undocumented
_optimizer_rownum_pred_based_fkr=TRUE
_OPTIMIZER_SKIP_SCAN_ENABLED Undocumented
_optimizer_skip_scan_enabled=TRUE
_OPTIMIZER_SORTMERGE_JOIN_INEQUALITY Undocumented
_optimizer_sortmerge_join_inequality=TRUE
_OPTIMIZER_SQU_BOTTOMUP Undocumented
_optimizer_squ_bottomup=TRUE
_OPTIMIZER_STAR_TRAN_IN_WITH_CLAUSE Undocumented
_optimizer_star_tran_in_with_clause=TRUE
_OPTIMIZER_SYSTEM_STATS_USAGE Undocumented
_optimizer_system_stats_usage=TRUE
_OPTIMIZER_TRANSITIVITY_RETAIN Undocumented
_optimizer_transitivity_retain=TRUE
_OPTIMIZER_UNDO_COST_CHANGE Undocumented
_optimizer_undo_cost_change='11.1.0.7'
_OR_EXPAND_NVL_PREDICATE Undocumented
_or_expand_nvl_predicate=TRUE
_ORDERED_NESTED_LOOP Undocumented
_ordered_nested_loop=TRUE
_PARALLEL_BROADCAST_ENABLED Undocumented
_parallel_broadcast_enabled=TRUE
_PARALLEL_SYSPLS_OBEY_FORCE
in 11.1, verify if still in 11.2
Undocumented
_parallel_syspls_obey_force=TRUE
_PARTITION_VIEW_ENABLED Undocumented
_partition_view_enabled=TRUE
_PIVOT_IMPLEMENTATION_METHOD Undocumented
_pivot_implementation_method='CHOOSE'
_PRE_REWRITE_PUSH_PRED Undocumented
_pre_rewrite_push_pred=TRUE
_PRED_MOVE_AROUND Undocumented
_pred_move_around=TRUE
_PUSH_JOIN_PREDICATE Undocumented
_push_join_predicate=TRUE
_PUSH_JOIN_UNION_VIEW Undocumented
_push_join_union_view=TRUE
_PUSH_JOIN_UNION_VIEW2 Undocumented
_push_join_union_view2=TRUE
_PX_MINUX_INTERSECT Undocumented
_px_minus_intersect=TRUE
_PX_PWG_ENABLED Undocumented
_px_pwg_enabled=TRUE
_PX_UAL_SERIAL_INPUT Undocumented
_px_ual_serial_input=TRUE
_QUERY_REWRITE_SETOPGRW_ENABLE Undocumented
_query_rewrite_setopgrw_enable=TRUE
_REALFREE_HEAP_PAGESIZE_HINT Undocumented
TBD
_RECURSIVE_IMU_TRANSACTIONS
Default: FALSE
Enables recursive SQL to use In-Memory Undo
_recursive_imu_transactions=TRUE
_REMOVE_AGGR_SUBQUERY Undocumented
_remove_aggr_subquery=TRUE
_RIGHT_OUTER_HASH_ENABLED Undocumented
_right_outer_hash_enable=TRUE
_SELFJOIN_MV_DUPLICATES Undocumented
_selfjoin_mv_duplicates=TRUE
_SQL_MODEL_UNFOLD_FORLOOPS Undocumented
_sql_model_unfold_forloops='RUN_TIME'
_SQLTUNE_CATEGORY_PARSED Parsed sqltune_category
_sqltune_category_parsed='DEFAULT'
_SUBQUERY_PRUNING_ENABLED Undocumented
_subquery_pruning_enabled=TRUE
_SUBQUERY_PRUNIN_MV_ENABLED Undocumented
_subquery_pruning_mv_enabled=FALSE
_SYSTEM_TRIG_ENABLED

Default: TRUE
If set to FALSE system triggers will not fire
Range of values TRUE | FALSE
alter session set "_system_trig_enabled"=FALSE;
_TABLE_SCAN_COST_PLUS_ONE Undocumented
_table_scan_cost_plus_one=TRUE
_TRACE_FILES_PUBLIC

Default: FALSE
Sets the file protection mask to 644
Range of values TRUE | FALSE
alter session set "_trace_files_public"=TRUE;
_UNDO_BLOCK_COMPRESSION
Default: TRUE
Undocumented but appears to apply segment compression to undo blocks
_undo_block_compression=TRUE;
_UNION_REWRITE_FOR_GS Undocumented
_union_rewrite_for_gs='YES_GSET_MVS'
_UNNEST_SUBQUERY

Default: TRUE
_unnest_subquery=FALSE
Range of values TRUE | FALSE
EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
  SELECT srvr_id
  FROM serv_inst);

SELECT * FROM TABLE(dbms_xplan.display);

alter session set "_unnest_subquery"=FALSE;

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
  SELECT srvr_id
  FROM serv_inst);

SELECT * FROM TABLE(dbms_xplan.display);
_USE_COLUMN_STATS_FOR_FUNCTION Undocumented
_use_column_stats_for_function=TRUE
 
Related Topics
Init.ora & SPFILE
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved