Oracle Startup Parameters

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version to Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Page Index
Advanced Queuing (AQ) & Streams

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.

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.
Default: 1024
Message cache transaction hash table size
Default: 1
Add PT batch size
Default: FALSE
AQ - disable new cross processes at an instance
Default: 5
AQ PT DQ prefech size
Default: 5
Drop PT batch Size
Default: 5
Minimum enqueue shards per queue at an instance
Default: 10
Maximum number of slaves for knlpipcm
Default: 100
Absolute threshold greater than average latency
Default: 30
Relative threshold of average latency
Default: 120
Time(seconds) between consecutive AQ load balancing efforts
Default: 120
Time(seconds) between consecutive AQ load statistics collection
Default: 30
AQ PT look back size
Default: 1500
Maximum allowable scan delay for AQ indexes and IOTs
Default: 15
Partition background processes
Default: 30
PT shrink window size
Default: 30
PT statistics sample window size
Default: 5
AQ PT QT prefech size
Default: 32
bitmap child latches
Default: 512
Shard child latches
Default: 16
Shard priority child latches
Default:  32
Retry child latches
Default:  1024
Subscriber element child latches
Default:  512
Subscriber child latches
Default:  128
Txn child latches
Default:  FALSE
Stop all AQ background processes
Default:  10485760
Large payload threshold size
Default:  20000
Sub Shard Size
Default:  1
SubShards Per Deq Partition
Default:  1
SubShards Per Q Partition
Default: 0
Dequeue count interval for Time Managers to cleanup DEQ IOT BLOCKS
Default: 0
Scan limit for Time Managers to clean up IOT
Default: 0
Statistics collection window duration
Default: 1
Trunc PT batch Size
Default: 32768
AQ cross single message buffer size
Default: 0
Limit for cached enqueue/dequeue operations
ANSI Compliance

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}
Archive Logging

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]
LOG_ARCHIVE_DEST Deprecated in Enterprise Edition in favour of 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 }
[ REOPEN[=seconds] ]
[ DELAY[=minutes] ]
[ TEMPLATE=template ]
[ ALTERNATE=destination ]
[ DEPENDENCY=destination ]
[ MAX_FAILURE=count ]
[ NET_TIMEOUT=seconds ]
[ VALID_FOR=(redo_log_type,database_role) ]
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

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}

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.

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:

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}

Default: 1
The minimum number of destinations that must succeed for the online logfile to be available for reuse.

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)
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
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*'
Default: 1
Specifies the maximum power on an Automatic Storage Management instance for disk rebalancing.
Range of values
 {1 to 11}
Default: NULL
Specifies the failure groups that contain preferred read disks. Preferred disks are instance specific.
Default: AUTO
ASM File access mechanism
Default: 1
initial ACD chunks created
Default: FALSE
does the sysdba role have administrative privileges on ASM?
Default: FALSE
Default: FALSE
Disable checking for unprotected volumes in mirrored disk groups
Default: TRUE
Enable disk resilvering for external redundancy
Default:  TRUE
Discovery only raw devices
Default: FALSE
Allow a small memory_target for ASM instances
Default: FALSE
If system alias renaming is allowed
Default: TRUE
attempt unsafe reconnect to ASM
Default:  FALSE
Allow force-mounts of DGs w/o proper quorum
Default: Appliance
Configuration file name
Default: FALSE
Ignore OAK appliance library
Default: FALSE
Get appliance disk slot from disk path
Default: 1048576
Allocation unit size
Default: TRUE
Automatically rebalance free space across zones
Default: TRUE
Avoid PST Scans
Default: 4096
Metadata block size
Default: FALSE
Check for misbehaving CF-holding clients
Default: 10.1
Default ASM compatibility level
Default: 500000
ASM Disk Based Allocation Max Batch Size
Default: 100000
ASM Disk Based Allocation Space Check Threshold
Default: 0
ASM Disk Based Allocation Threshold
Default: FALSE
dbms_diskgroup.checkfile does not check block headers
Default: FALSE
Diagnostics for dead clients
  _asm_direct_con_expire_time 120 Expire time for idle direct connection to ASM instance
  _asm_disable_amdu_dump FALSE Disable AMDU dump
  _asm_disable_async_msgs FALSE disable async intra-instance messaging
  _asm_disable_dangerous_failgroup_checking FALSE Disable checking for dubious failgroup configurations
  _asm_disable_multiple_instance_check FALSE Disable checking for multiple ASM instances on a given node
  _asm_disable_profilediscovery FALSE disable profile query for discovery
  _asm_disable_smr_creation FALSE Do Not create smr
  _asm_disable_ufg_dump FALSE disable terminated umbilicus diagnostic
  _asm_disable_ufgmemberkill FALSE disable ufg member kill
  _asm_disk_repair_time 14400 seconds to wait before dropping a failing disk
  _asm_diskerr_traces 2 Number of read/write errors per disk a process can trace
  _asm_diskgroups2 disk groups to mount automatically set 2
  _asm_diskgroups3 disk groups to mount automatically set 3
  _asm_diskgroups4 disk groups to mount automatically set 4
  _asm_emulate_nfs_disk FALSE Emulate NFS disk test event
  _asm_emulmax 10000 max number of concurrent disks to emulate I /O errors
  _asm_emultimeout 0 timeout before emulation begins (in 3s ticks)
  _asm_enable_xrov FALSE Enable XROV capability
  _asm_evenread 2 ASM Even Read level
  _asm_evenread_alpha 0 ASM Even Read Alpha
  _asm_evenread_alpha2 0 ASM Even Read Second Alpha
  _asm_evenread_faststart 0 ASM Even Read Fast Start Threshold
  _asm_fail_random_rx FALSE Randomly fail some RX enqueue gets
  _asm_fd_cln_idle_sess_twait 60000000 Idle session time wait to run ASM FD cleanup
  _asm_fd_cln_on_fg FALSE ASM stale FD cleanup on foregrounds
  _asm_fob_tac_frequency 9 Timeout frequency for FOB cleanup
  _asm_force_quiesce FALSE Force diskgroup quiescing
  _asm_force_vam FALSE force VAM for external redundancy
  _asm_global_dump_level 267 System state dump level for ASM asserts
  _asm_hbeatiowait 120 number of secs to wait for PST Async Hbeat IO return
  _asm_hbeatwaitquantum 2 quantum used to compute time-to-wait for a PST Hbeat check
  _asm_healthcheck_timeout 180 seconds until health check takes action
  _asm_imbalance_tolerance 3 hundredths of a percentage of inter-disk imbalance to tolerate
  _asm_instlock_quota 0 ASM Instance Lock Quota
  _asm_iostat_latch_count 31 ASM I/O statistics latch count
  _asm_kfdpevent 0 KFDP event
  _asm_kill_unresponsive_clients TRUE kill unresponsive ASM clients
  _asm_libraries ufs library search order for discovery
  _asm_log_scale_rebalance FALSE Rebalance power uses logarithmic scale
  _asm_lsod_bucket_size 67 ASM lsod bucket size
  _asm_max_cod_strides 5 maximum number of COD strides
  _asm_max_parallelios 256 Maximum simultaneous outstanding IOs
  _asm_max_redo_buffer_size 2097152 asm maximum redo buffer size
  _asm_maxio 1048576 Maximum size of individual I/O request
  _asm_network_timeout 1 Keepalive timeout for ASM network connections
  _asm_networks ASM network subnet addresses
  _asm_nodekill_escalate_time 180 secs until escalating to nodekill if fence incomplete
  _asm_noevenread_diskgroups List of disk groups having even read disabled
  _asm_offload_all FALSE Offload all write operations to Exadata cells, when supported
  _asm_partner_target_disk_part 8 target maximum number of disk partners for repartnering
Default: 4
Target maximum number of failure group relationships for repartnering
Default: 1
Number of cycles/extents to load for non-mirrored files
  _asm_primary_load_cycles TRUE True if primary load is in cycles, false if extent counts
  _asm_procs_trace_diskerr 5 Number of processes allowed to trace a disk failure
  _asm_proxy_startwait 60 Maximum time to wait for ASM proxy connection
  _asm_random_zone FALSE Random zones for new files
  _asm_read_cancel AUTO Read cancel timeout in milliseconds
  _asm_read_cancel_back_out 5000 Time period in milliseconds when no reads are issued to a disk after a read is cancelled
  _asm_rebalance_plan_size 120 maximum rebalance work unit
  _asm_rebalance_space_errors 4 number of out of space errors allowed before aborting rebalance
  _asm_relocation_ignore_hard_failure 0 ignore HARD for relocation
  _asm_relocation_trace FALSE enable extent relocation tracing
  _asm_remote_client_timeout 300 timeout before killing disconnected remote clients
  _asm_repairquantum 60 quantum (in 3s) used to compute elapsed time for disk drop
  _asm_reserve_slaves TRUE reserve ASM slaves for CF txns
  _asm_resyncCkpt 1024 number of extents to resync before flushing checkpoint
  _asm_root_directory ASM ASM default root directory
  _asm_runtime_capability_volume_support FALSE runtime capability for volume support returns supported
  _asm_scrub_limit AUTO ASM disk scrubbing power
  _asm_scrub_unmatched_dba 3 Scrub maximum number of blocks with unmatched DBA
  _asm_secondary_load 10000 Number of cycles/extents to load for mirrored files
  _asm_secondary_load_cycles FALSE True if secondary load is in cycles, false if extent counts
  _asm_serialize_volume_rebalance FALSE Serialize volume rebalance
  _asm_shadow_cycle 3 Inverse shadow cycle requirement
  _asm_skip_dbfile_ios FALSE Skip I/Os to database files (do only ASM metadata I/O)
  _asm_skip_diskval_check FALSE skip client side discovery for disk revalidate
  _asm_skip_rename_check FALSE skip the checking of the clients for s/w compatibility for rename
  _asm_skip_resize_check FALSE skip the checking of the clients for s/w compatibility for resize
  _asm_storagemaysplit FALSE PST Split Possible
Default: 131072
ASM file stripe size
Default: 8
ASM file stripe width
  _asm_sync_rebalance FALSE Rebalance uses sync I/O
  _asm_trace_limit_timeout 30000 Time-out in milliseconds to reset the number of traces per disk and the number of processes allowed to trace
  _asm_usd_batch 64 ASM USD Update Max Batch Size
  _asm_wait_time 18 Max/imum time to wait before asmb exits
  _asm_write_cancel AUTO Write timeout in milliseconds
  _asm_xrov_nstats 0 Specify number of IOs before stats
Default: 4
Specify number of VIO processes
Default: 1
Specify 'reason' mode
Default: TRUE
Enable single issues of IOs
Default: asm
ASM instance id

The operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set to os.

Default: FALSE
Enables or disables auditing of operations issued by user SYS, and users connecting with SYSDBA or SYSOPER privileges.

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 }

Default: DB
Enables or disables database auditing.
Backup and Restore

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}

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

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.

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}

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.
Buffer Cache and I/O

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: 0 or (values greater than zero are rounded up to the nearest granule size)

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.

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

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

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

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

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

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.

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.

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

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

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}

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}

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}

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}
Cursors and Library Cache

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}

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}

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

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

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

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

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}
FAL_CLIENT Specifies the FAL (fetch archive log) client name that is used by the FAL service
FAL_SERVER Specifies the FAL (fetch archive log) server for a standby database

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.
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_'

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}
Database / Instance / Identification

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_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.
Diagnostics / Statistics

Default: partial
Specifies whether Oracle includes the SGA in the core file for Oracle background processes.
Range of Values: {PARTIAL | FULL}
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}
Default: {ORACLE_HOME}/dbs
Primarily a UNIX parameter not supported on all platforms. Specifies the directory for core file dumps

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}

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}
DIAGNOSTIC_DEST Replacement for BDUMP and UDUMP related parameters. Points to ADR_BASE

Default: FALSE
Enables or disables the writing of DDL statements to the alert log

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

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]

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

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}

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

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}

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}

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.
Default: TRUE
To enable or disable string compression in ASH
Default: 10
Ratio of the number of in-memory samples to the number of samples actually written to disk
Default: TRUE
To enable or disable Active Session History flushing
Default: 0
Oracle internal dummy ASH parameter used ONLY for testing!
Default: 66
The percentage above which if the in-memory ASH is full the emergency flusher will be triggered
Default: TRUE
To enable or disable Active Session sampling and flushing
Default: 90
Minimum Time interval passed to consider MMNL Dump
Default: FALSE
To enable or disable sampling every connected session including ones waiting for idle waits
Default: 1000
Time interval between two successive Active Session samples in millisecs
Default: 1048618
To set the size of the in-memory Active Session History buffers
Distributed Replication

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}

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

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}

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}

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}

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}

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}
Exadata Storage Server

Cell packet compaction strategy

enable SQL processing offload of encrypted data to cells
Default: TRUE

additional cell offload parameters
Default: Not set

cell offload explain plan display
Default: AUTO

enable SQL processing offload to cells
Default: TRUE

set the offload group name
Default: Not set

allow checking smart_scan_capable Attr
_allow_cell_smart_scan_attr=<FALSE | TRUE>

allow optimized file creation path for cells
Allow optimized file creation path for Cells
_cell_fast_file_create=<FALSE | TRUE>

allow optimized rman restore for cells
_cell_fast_file_restore=<FALSE | TRUE>

cell file format chunk size in MB
Default is 0

enable cell processing of index FFS
_cell_index_scan_enabled=<FALSE | TRUE>

Force materialization of all offloadable expressions on the cells
_cell_materialize_all_expressions=<FALSE | TRUE>

enable offload of expressions underlying virtual columns to cells
_cell_materialize_virtual_columns=<FALSE | TRUE>
Default is 24
Flashcache object expiration timeout

enable offload of backup compression to cells
_cell_offload_backup_compression=<FALSE | TRUE>

specifies capability table to load
Default is 1

enable complex SQL processing offload to cells
_cell_offload_complex_processing=<FALSE | TRUE>

enable offload of expressions to cells
_cell_offload_expressions=<FALSE | TRUE>

Query offloading of hybrid columnar compressed tables to exadata
_cell_offload_hybridcolumnar=<FALSE | TRUE>

enable out-of-order SQL processing offload to cells
_cell_offload_predicate_reordering_enabled=<FALSE | TRUE>

enable offload of SYS_CONTEXT evaluation to cells
_cell_offload_sys_context=<FALSE | TRUE>

enable timezone related SQL processing offload to cells
_cell_offload_timezone=<FALSE | TRUE>

enable offload of predicates on virtual columns to cells
_cell_offload_virtual_columns=<FALSE | TRUE>

enable CELL processing of index range scans
_cell_range_scan_enabled=<FALSE | TRUE>

cell storage index mode
_cell_storidx_minmax_enabled=<FALSE | TRUE>

cell storage index mode
Default is EVA

flash cache hint for control file accesses
Default is 3

_db_check_cell_hints=<FALSE | TRUE>

disable cell optimized backups
_disable_cell_optimized_backups=<FALSE | TRUE>

Allow dataonly passthru for smart scan
_kcfis_cell_passthru_dataonly=<FALSE | TRUE>

Do not perform smart IO filtering on the cell
_kcfis_cell_passthru_enabled=<FALSE | TRUE>

Enable automatic passthru mode when cell CPU util is too high
_kcfis_cell_passthru_fromcpu_enabled=<FALSE | TRUE>

Enable offload server usage for passthru operations
_kcfis_celloflsrv_passthru_enabled=<FALSE | TRUE>

Enable offload server usage for offload operations
_kcfis_celloflsrv_usage_enabled=<FALSE | TRUE>

Enable usage of cellsrv flash cache for kept objects
_kcfis_kept_in_cellfc_enabled=<FALSE | TRUE>
Default: FALSE
Enable use of cellsrv flash cache for non-kept objects
_kcfis_nonkept_in_cellfc_enabled=<FALSE | TRUE>
File Locations, Names, and Sizes

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'

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

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

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_RECOVERY_FILE_DEST Specifies the default location for the 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]
FILE_MAPPING Deprecated but still supported for backward compatibility
No Default
Embed the named parameter file in the init.ora as it is read.

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.

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.

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)
Default: ON
Specifies if the recyclebin holds dropped tables and associated indexes.
Range of values: {ON | OFF}
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.

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.

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}

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.

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.

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.
Memory Allocation, Sizing, and Usage

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

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.

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

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

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.

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.

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.
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.
Default: 0
Specifies (in bytes) the size of the OLAP page pool.
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

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.
SGA_MAX_SIZE Specifies the maximum size of the SGA for the lifetime of the instance.

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}

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.

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.

Default: 0
Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures. Larger values improve performance in multi-user systems. Smaller values use less memory.

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.

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.
Default: TRUE
ASSM default
Default: FALSE
Enable metadata block fetching in ASSM segment scan
Default: 11024
Number of blocks rejected before growing segment
Default: 10000
Number of blocks rejected before collecting stats
Default: 0
ASSM min number of blocks to cbk-reject
Default: 0
ASSM min number of blocks to kdt-reject
Default: FALSE
ASSM test reentrant gsp
COMPATIBLE Allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release.

Specifies which of the Server Manageability Packs should be active.
CPU_COUNT Specifies the number of CPUs available to Oracle. Range of values 0 to unlimited.
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_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.

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

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.

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}

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}

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}

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.

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>

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.

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

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>

Default: FALSE
Determines whether resource limits are enforced in database profiles.

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.

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

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}

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.

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.

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.

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

Equivalent to:
ALTER SYSTEM SET smtp_out_server = '' SCOPE=BOTH;

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.
SQL_TRACE Deprecated

Default: DEFAULT
Specifies the category name for use by sessions to qualify the lookup of SQL profiles during SQL compilation.

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

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}

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

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.

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

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.

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.

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.

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

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>

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.

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.

No Default
Specifies the number of shared server sessions to allow. Setting this parameter enables reserving user sessions for dedicated 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.
NLS Parameters

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}

Default: BINARY
Specifies which calendar system Oracle uses
Range of values: {BINARY | LINGUISTIC | ANSI}
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_DATE_FORMAT Specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY.
NLS_DATE_LANGUAGE Specifies the language to use for the spelling of day and month names and date abbreviations (a.m., p.m., AD, BC) returned by the TO_DATE and TO_CHAR functions.
NLS_DUAL_CURRENCY Specifies the dual currency symbol (such as "Euro") for the territory. The default is the dual currency symbol defined in the territory of your current language environment.
NLS_ISO_CURRENCY Specifies the string to use as the international currency symbol for the C number format element.
NLS_LANGUAGE Specifies the default language of the database. This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism. This parameter also determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT.
NLS_LENGTH_SEMANTICS Enables creationof CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.

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_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_TERRITORY Specifies the name of the territory whose conventions are to be followed for day and week numbering.
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'

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.

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

Default: 2
Controls the level of dynamic sampling performed by the optimizer.
Range of values: {0 - 10}

Umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
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

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

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}

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}

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

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

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}

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

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

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.
Default: CHOOSE
Always use this method for anti-join when possible
Default: CHOOSE
Always use this method for semi-join when possible
Default: FALSE
Always favor use of star transformation
Default: FALSE
Always favor use of vector transformation

Controls the compile time collection, cross reference, and storage of PL/SQL source code identifier data.

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

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

equivalent to:

Specifies the compilation mode for PL/SQL library units.

equivalent to:

Default: FALSE
Specifies whether or not PL/SQL library units will be compiled for debugging.

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
Real Application Clusters

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

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

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.

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

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

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.

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}

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

new in 11.2

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.

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

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}

No Default

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.

Default: See explanation at right
Default is (PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5)
Range of values: 0 to 3600

Default: 0
Range of values: 0 to 109
Default: See explanation at right
Range of values Default value to the value of PARALLEL_MAX_SERVERS
Default: AUTO
Range of values: {AUTO | integer}

Default: See explanation at right
Default is (PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 2)
Range of values: 0 to PARALLEL_MAX_SERVERS

Default: O/S dependent but usually 2
Any non-zero integer.

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.
Result Cache

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.

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}

Default: 5%
Specifies the percentage of RESULT_CACHE_MAX_SIZE that any single result can use.

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.

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

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

Default: FALSE
Version 7 Dictionary Accessibility support. Range of values TRUE or FALSE

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}

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

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}
Default: NO
Enables or disables directory-based authorization for SYSDBA and SYSOPER.

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.

Default: FALSE
Determines whether Oracle or the operating system identifies and manages the roles of each username.

Specifies whether Oracle checks for a password file.
Range of Values: {SHARED | EXCLUSIVE | NONE}

Specifies whether operating system roles are allowed for remote clients. The default value, false, causes Oracle to identify and manage roles for remote clients.

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.

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

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}

Default: FALSE
Specifies whether or not the server returns complete database software information to clients.

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

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}

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}

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.
Undo Tablespace

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}

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

No Default
Name of the undo tablespace to be used during startup.
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) = '_'
__DB_CACHE_SIZE Undocumented
__JAVA_CACHE_SIZE Undocumented
__LARGE_POOL_SIZE Undocumented
__ORACLE_BASE Set from the environment
__SGA_TARGET Undocumented
__SHARED_IO_POOL_SIZE Undocumented
__SHARED_POOL_SIZE Undocumented
__STREAMS_POOL_SIZE Undocumented

abort database instance when MRP crashes

if TRUE, abort recovery on join reconfigurations
  _ac_enable_dscn_in_rac FALSE Enable Dependent Commit SCN tracking
  _ac_strict_SCN_check FALSE enforce strict SCN check for AC replay across DG failover
  _accept_versions List of parameters for rolling operation
  _active_instance_count number of active instances in the cluster database
  _active_session_idle_limit 5 active session idle limit
  _active_session_legacy_behavior FALSE active session legacy behavior
  _active_standby_fast_reconfiguration TRUE if TRUE optimize dlm reconfiguration for active/standby OPS

enable adaptive direct read

enable adaptive direct write
  _adaptive_fetch_enabled TRUE enable/disable adaptive fetch in parallel group by
  _adaptive_log_file_sync_high_switch_freq_threshold 3 Threshold for frequent log file sync mode switches (per minute)
  _adaptive_log_file_sync_poll_aggressiveness 0 Polling interval selection bias (conservative=0, aggressive=100)
  _adaptive_log_file_sync_sampling_count 128 Evaluate post/wait versus polling every N writes
  _adaptive_log_file_sync_sampling_time 3 Evaluate post/wait versus polling every N seconds
  _adaptive_log_file_sync_sched_delay_window 60 Window (in seconds) for measuring average scheduling delay
  _adaptive_log_file_sync_use_polling_threshold 200 Ratio of redo synch time to expected poll time as a percentage

 Percentage of foreground load from when post/wait was last used

Permille of foreground load from when post/wait was last used
  _adaptive_scalable_log_writer_disable_worker_threshold 90 Percentage of overlap across multiple outstanding writes
  _adaptive_scalable_log_writer_enable_worker_aging 999900 Per million of redo gen rate when LGWR workers were last used
  _adaptive_scalable_log_writer_enable_worker_threshold 200 Increase in redo generation rate as a percentage
  _adaptive_scalable_log_writer_sampling_count 128 Evaluate single versus scalable LGWR every N writes
  _adaptive_scalable_log_writer_sampling_time 3 Evaluate single versus scalable LGWR every N seconds
  _adaptive_window_consolidator_enabled TRUE enable/disable adaptive window consolidator PX plan
  _add_col_optim_enabled TRUE Allows new add column optimization
  _add_nullable_column_with_default_optim TRUE Allows add of a nullable column with default optimization
  _add_stale_mv_to_dependency_list TRUE add stale mv to dependency list
  _add_trim_for_nlssort TRUE add trimming for fixed char semantics
  _addm_AUTO_enable TRUE governs whether ADDM gets run automatically after every AWR snapshot
  _addm_skiprules comma-separated list of ADDM nodes to skip
  _addm_version_check TRUE governs whether ADDM checks the input AWR snapshot version
  _adg_buffer_wait_timeout 10 Active Dataguard buffer wait time in cs
  _adg_distributed_lockmaster FALSE standby runs under ADG distributed lockmaster mode
  _adg_instance_recovery TRUE enable ADG instance recovery
  _adg_parselock_timeout 0 timeout for parselock get on ADG in centiseconds
  _adg_parselock_timeout_sleep 100 sleep duration after a parselock timeout on ADG in milliseconds
  _adjust_literal_replacement FALSE If TRUE, we will adjust the SQL/PLUS output
  _adr_migrate_runonce TRUE Enable/disable ADR Migrate Runonce action
  _advanced_index_compression_options 0 advanced index compression options
  _advanced_index_compression_options_value 20 advanced index compression options2
  _advanced_index_compression_trace 0 advanced index compression trace
  _afd_disable_fence FALSE Disable AFD fencing
  _affinity_on TRUE enable/disable affinity at run time
  _aged_out_cursor_cache_time 300 number of seconds an aged out session cached cursor stay incache
  _aggregation_optimization_settings 0 settings for aggregation optimizations
  _aiowait_timeouts 100 Number of aiowait timeouts before error is reported
  _alert_expiration 604800 seconds before an alert message is moved to exception queue
  _alert_message_cleanup 1 Enable Alert Message Cleanup
  _alert_message_purge 1 Enable Alert Message Purge
  _alert_post_background 1 Enable Background Alert Posting
  _all_shared_dblinks treat all dblinks as shared
  _allocate_creation_order FALSE should files be examined in creation order during allocation
  _allocation_update_interval 3 interval at which successful search in L1 should be updated
  _allow_cell_smart_scan_attr TRUE Allow checking smart_scan_capable Attr
  _allow_commutativity TRUE allow for commutativity of +, * when comparing expressions
  _allow_compatibility_adv_w_grp FALSE allow advancing DB compatibility with guaranteed restore points
  _allow_convert_to_standby FALSE allow convert to standby to go through
  _allow_drop_snapshot_standby_grsp FALSE Allow dropping snapshot standby guaranteed restore point
  _allow_drop_ts_with_grp FALSE Allow drop Tablespace with guaranteed restore points
  _allow_error_simulation FALSE Allow error simulation for testing
  _allow_file_1_offline_error_1245 FALSE don't signal ORA-1245 due to file 1 being offline
  _allow_level_without_connect_by FALSE allow level without connect by
  _allow_read_only_corruption FALSE allow read-only open even if database is corrupt
  _allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption
  _allow_terminal_recovery_corruption FALSE Finish terminal recovery even if it may cause corruption
  _alter_upgrade_signature_only FALSE alter table upgrade only sets signature
  _alternate_iot_leaf_block_split_points TRUE enable alternate index-organized table leaf-block split-points
  _and_pruning_enabled TRUE allow partition pruning based on multiple mechanisms
  _appqos_cdb_setting 0 QoSM CDB Performance Class Setting
  _appqos_po_multiplier 1000 Multiplier for PC performance objective value
  _appqos_qt 10 System Queue time retrieval interval
  _approx_cnt_distinct_gby_pushdown choose perform group-by pushdown for approximate distinct count query
  _approx_cnt_distinct_optimization 0 settings for approx_count_distinct optimizations
  _arch_comp_dbg_scan 0 archive compression scan debug
  _arch_comp_dec_block_check_dump 1 decompress archive compression blocks for checking and dumping
  _arch_compress_checksums FALSE enable/disable row checksums for archive compressed blocks
  _arch_compression TRUE archive compression enabled
  _arch_io_slaves 0 ARCH I/O slaves
  _arch_sim_mode 0 Change behavior of local archiving
  _array_cdb_view_enabled TRUE array mode enabled for CDB views
  _array_update_vector_read_enabled FALSE Enable array update vector read
  _async_recovery_claims TRUE if TRUE, issue recovery claims asynchronously
  _async_recovery_reads TRUE if TRUE, issue recovery reads asynchronously
  _async_rta_broadcast FALSE asynchronously broadcast RTA boundary
  _async_ts_threshold 1 check tablespace thresholds asynchronously
  _AUTO_assign_cg_for_sessions FALSE auto assign CGs for sessions
  _AUTO_bmr enabled enable/disable Auto BMR
  _AUTO_BRM_bg_time 3600 Auto BMR Process Run Time
  _AUTO_BRM_fc_time 60 Auto BMR Flood Control Time
_AUTO_BRM_max_rowno 1024 x$krbabrstat Max number of rows
_AUTO_BRM_pub_timeout 10 Auto BMR Publish Timeout
_AUTO_BRM_req_timeout 60 Auto BMR Requester Timeout
_AUTO_BRM_sess_threshold 30 Auto BMR Request Session Threshold
  _AUTO_BRM_sys_threshold 100 Auto BMR Request System Threshold
_AUTO_manage_enable_offline_check TRUE perodically check for OFFLINE disks and attempt to ONLINE
_AUTO_manage_exadata_disks TRUE Automate Exadata disk management
_AUTO_manage_infreq_tout 0 TEST: Set infrequent timeout action to run at this interval, unit is seconds
_AUTO_manage_ioctl_bufsz 8192 oss_ioctl buffer size, to read and respond to cell notifications
  _AUTO_manage_max_online_tries 3 Max. attempts to auto ONLINE an ASM disk
_AUTO_manage_num_pipe_msgs 1000 Max. number of out-standing msgs in the KXDAM pipe
_AUTO_manage_num_tries 2 Num. tries before giving up on a automation operation
_AUTO_manage_online_tries_expire_time 86400 Allow Max. attempts to auto ONLINE an ASM disk after lapsing this time (unit in seconds)
_automatic_maintenance_test 0 Enable AUTOTASK Test Mode
  _automemory_broker_interval 3 memory broker statistics gathering interval for auto memory
  _AUTOTASK_max_window 480 Maximum Logical Maintenance Window Length in minutes
  _AUTOTASK_min_window 15 Minimum Maintenance Window Length in minutes
  _AUTOTASK_test_name N/A Name of current Autotask Test (or test step)
  _autotune_gtx_idle_time 600 idle time to trigger auto-shutdown a gtx background process
  _autotune_gtx_interval 5 interval to autotune global transaction background processes
  _autotune_gtx_threshold 60 auto-tune threshold for degree of global transaction concurrency
  _aux_dfc_keep_time 1440 auxiliary datafile copy keep time in minutes
  _available_core_count 0 number of cores for this instance
  _avoid_prepare TRUE if TRUE, do not prepare a buffer when the master is local
  _AWR_cdbperf_threshold 21 Setting for AWR CDBPERF Threshold
  _AWR_corrupt_mode FALSE AWR Corrupt Mode
Default: None
Disable flushing of specified AWR tables
  _AWR_disabled_purge_tables Disable purging of specified AWR tables
_AWR_flush_threshold_metrics TRUE Enable/Disable Flushing AWR Threshold Metrics
_AWR_flush_workload_metrics FALSE Enable/Disable Flushing AWR Workload Metrics
_AWR_MMON_cpuusage TRUE Enable/disable AWR MMON CPU Usage Tracking
_AWR_MMON_DEEP_purge_all_expired FALSE Allows deep purge to purge AWR data for all expired snapshots
_AWR_MMON_DEEP_purge_extent 7 Set extent of rows to check each deep purge run
_AWR_MMON_DEEP_purge_interval 7 Set interval for deep purge of AWR contents
_AWR_MMON_DEEP_purge_numrows 5000 Set max number of rows per table to delete each deep purge run
_AWR_partition_interval 0 Setting for AWR Partition Interval
_AWR_pdb_registration_enabled FALSE Parameter to enable/disable AWR PDB Registration
_AWR_remote_target_dblink AWR Remote Target DBLink for Flushing
Default: FALSE
By default the value is FALSE which indicates that AWR snapshots will not be made when the database is in restricted mode.
Default: BESTFIT
Set Default AWR snapshot level
Default: 200
Setting for AWR SQL Child Limit
Default: TRUE
Enable the use of bitmap plans for tables w. only B-tree indexes
  _ba_cf_trace_buffer_size 131072 size of per-process I/O KBC trace buffer
_ba_container_filesystem_ausize 4194304 allocation unit size for Backup Appliance containers
_ba_max_containers 0 maximum number of Backup Appliance containers
_ba_max_groups 0 maximum number of Backup Appliance container groups
_ba_max_seg_bytes 4000 maximum number of bytes per array segment
_ba_timeouts_enabled TRUE enable timeouts
_background_process_opts 0 Misc BG procs parameter
_backup_align_write_io TRUE align backup write I/Os
_backup_appliance_enabled FALSE Backup Appliance Enabled
_backup_automatic_retry 10 automatic retry on backup write errors
_backup_bool_spare1 FALSE backup bool spare1
_backup_bool_spare2 FALSE backup bool spare2
_backup_bool_spare3 FALSE backup bool spare3
_backup_bool_spare4 FALSE backup bool spare4
_backup_disk_bufcnt 0 number of buffers used for DISK channels
_backup_disk_bufsz 0 size of buffers used for DISK channels
_backup_disk_io_slaves 0 BACKUP Disk I/O slaves
_backup_dynamic_buffers TRUE dynamically compute backup/restore buffer sizes
_backup_encrypt_opt_mode 4294967294 specifies encryption block optimization mode
_backup_file_bufcnt 0 number of buffers used for file access
_backup_file_bufsz 0 size of buffers used for file access
_backup_int_spare1 4294967294 backup int spare1
_backup_int_spare2 4294967294 backup int spare2
_backup_int_spare3 4294967294 backup int spare3
_backup_int_spare4 4294967294 backup int spare4
_backup_io_pool_size 1048576 memory to reserve from the large pool
_backup_kgc_blksiz 9 specifies buffer size to be used by HIGH compression
_backup_kgc_bufsz 0 specifies buffer size to be used by BASIC compression
_backup_kgc_memlevel 8 specifies memory level for MEDIUM compression
_backup_kgc_niters 0 specifies number of iterations done by BASIC compression
_backup_kgc_perflevel 1 specifies compression (performance) level for MEDIUM compression
_backup_kgc_scheme ZLIB specifies compression scheme
_backup_kgc_type 0 specifies compression type used by kgc BASIC compression
_backup_kgc_windowbits 15 specifies window size for MEDIUM compression
_backup_ksfq_bufcnt 0 number of buffers used for backup/restore
_backup_ksfq_bufmem_max 268435456 maximum amount of memory (in bytes) used for buffers for backup/restore
_backup_ksfq_bufsz 0 size of buffers used for backup/restore
_backup_lzo_size 262144 specifies buffer size for LOW compression
_backup_max_gap_size 4294967294 largest gap in an incremental/optimized backup buffer, in bytes
_backup_min_ct_unused_optim 2097152 mimimun size in bytes of change tracking to apply unused space optimuzation
_backup_seq_bufcnt 0 number of buffers used for non-DISK channels
_backup_seq_bufsz 0 size of buffers used for non-DISK channels
_backup_text_spare1 backup text spare1
_backup_text_spare2 backup text spare2
_backup_text_spare3 backup text spare3
_backup_text_spare4 backup text spare4
Default: 8
Number of bitmaps to store for each datafile
Default: 104857600
maximum size of all change tracking buffer allocations, in bytes
Default: 1
mininum number of extents to allocate per buffer alloca
Default: 2097152
size of one change tracking buffer allocation, in bytes
Default: 0
change tracking datafile chunk size, in bytes
Default: 262144
change tracking reserved crash recovery SGA space, in bytes
Default: 0
block size of change tracking file, in bytes
Default: 0
extent size of change tracking file, in bytes
Default: NULL
change tracking file for fixed tables
Default: 60
CTWR health check interval (seconds), zero to disable
Default: 0
initial number of entries in the private change tracking dba buffers
  _bct_mrp_timeout 600 CTWR MRP wait timeout (seconds), zero to wait forever
  _bct_public_dba_buffer_dynresize 2 allow dynamic resizing of public dba buffers, zero to disable
  _bct_public_dba_buffer_maxsize 0 max buffer size permitted for public dba buffers, in bytes
Default: 0
total size of all public change tracking dba buffers, in bytes
  _bg_spawn_diag_opts 0 background processes spawn diagnostic options
  _bigdata_external_table FALSE enables use of ORACLE_HIVE and ORACLE_HDFS access drivers
  _bitmap_or_improvement_enabled TRUE controls extensions to partition pruning for general predicates
  _block_level_offload_high_lat_thresh 40000 High Latency Threshold for Block Level Offload operations
  _block_sample_readahead_prob_threshold 10 controls readahead value during block sampling
  _blocking_sess_graph_cache_size blocking session graph cache size in bytes
  _blocks_per_cache_server 16 number of consecutive blocks per global cache server
  _bloom_filter_debug 0 debug level for bloom filtering
Default: TRUE
enables or disables bloom filter
  _bloom_filter_size 0 bloom filter vector size (in KB)
  _bloom_folding_density 16 bloom filter folding density lower bound
Default: TRUE
Enable folding of bloom filter
Default: 128
Bloom filter folding size lower bound (in KB)
  _bloom_max_size 262144 maximum bloom filter size (in KB)
  _bloom_minmax_enabled TRUE enable or disable bloom min max filtering
  _bloom_predicate_enabled TRUE enables or disables bloom filter predicate pushdown
  _bloom_predicate_offload TRUE enables or disables bloom filter predicate offload to cells
Default: TRUE
Enable partition pruning using bloom filtering
  _bloom_pruning_enabled TRUE
_bloom_pushing_max 512 bloom filter pushing size upper bound (in KB)
_bloom_pushing_total_max 262144 bloom filter combined pushing size upper bound (in KB)
_bloom_rm_filter FALSE remove bloom predicate in favor of zonemap join pruning predicate
_bloom_serial_filter ON enable serial bloom filter on exadata
_bloom_sm_enabled TRUE enable bloom filter optimization using slave mapping
_branch_tagging TRUE enable branch tagging for distributed transaction
_broadcast_scn_mode 1 broadcast-on-commit scn mode
_broadcast_scn_wait_timeout 10 broadcast-on-commit scn wait timeout in centiseconds
_bsln_adaptive_thresholds_enabled TRUE Adaptive Thresholds Enabled
_bt_mmv_query_rewrite_enabled TRUE allow rewrites with multiple MVs and base tables
_buffer_busy_wait_timeout 100 buffer busy wait time in centiseconds
_buffered_message_spill_age 300 Buffered message spill age
_buffered_publisher_flow_control_threshold 0 Flow control threshold for buffered publishers except capture
_bufq_stop_flow_control FALSE Stop enforcing flow control for buffered queues
_build_deferred_mv_skipping_mvlog_update TRUE DEFERRED MV creation skipping MV log setup update
_bump_highwater_mark_count 0 how many blocks should we allocate per free list on advancing HWM
_bwr_for_flushed_pi TRUE if TRUE, generate a BWR for a flushed PI
_bypass_srl_for_so_eor FALSE bypass SRL for S/O EOR logs
_bypass_xplatform_error FALSE bypass datafile header cross-platform-compliance errors
_cache_orl_during_open ALL cache online logs
_cache_stats_monitor FALSE if TRUE, enable cache stats monitoring
_capture_buffer_size 65536 To set the size of the PGA I/O recording buffers
_capture_publisher_flow_control_threshold 0 Flow control threshold for capture publishers
_case_sensitive_logon TRUE case sensitive logon enabled
_catalog_foreign_restore FALSE catalog foreign file restore
_causal_standby_wait_timeout 20 Causal standby wait timeout
_cdb_compatible TRUE CDB Compatible
_cdb_cross_container 65535 Debug flag for cross container operations
_cdb_rac_affinity TRUE rac affinity for parallel cdb operations
_cdb_spfile_inherit FALSE Inherit CDB Spfile enabled/disabled in a PDB
_cdb_view_parallel_degree 65535 Parallel degree for a CDB view query
_cdb_view_prefetch_batch_size 200 Batch Size for Prefetch for a CDB view query
_cdb_view_rc_shelflife 30 Result Cache Shelflife for a CDB view query
_cdc_subscription_owner Change Data Capture subscription_owner
_cdmp_diagnostic_level 2 cdmp directory diagnostic level
_cgs_allgroup_poll_time 20000 CGS DBALL group polling interval in milli-seconds
_cgs_big_group_enabled FALSE big group membership support
_cgs_comm_readiness_check 1 CGS communication readiness check
_cgs_dball_group_registration local CGS DBALL group registration type
_cgs_dbgroup_poll_time 600 CGS DB group polling interval in milli-seconds
_cgs_health_check_in_reconfig FALSE CGS health check during reconfiguration
_cgs_memberkill_from_rim_instance FALSE allow a RIM instance to issue a CSS member kill
_cgs_msg_batch_size 4096 CGS message batch size in bytes
_cgs_msg_batching TRUE CGS message batching
_cgs_node_kill_escalation TRUE CGS node kill escalation to CSS
_cgs_node_kill_escalation_wait 0 CGS wait time to escalate node kill to CSS in seconds
_cgs_os_level_connection_check 1 allow OS level connection and interface check
_cgs_os_level_connection_reqno 0 number of ping rqst to process at once, threads created
_cgs_reconfig_extra_wait 3 CGS reconfiguration extra wait time for CSS in seconds
_cgs_reconfig_timeout 0 CGS reconfiguration timeout interval
_cgs_send_timeout 300 CGS send timeout value
_cgs_support_rim_disc TRUE rim instance disconnect and reconnect event support
_cgs_ticket_sendback 50 CGS ticket active sendback percentage threshold
_cgs_tickets 1000 CGS messaging tickets
_cgs_zombie_member_kill_wait 20 CGS zombie member kill wait time in seconds
_change_vector_buffers 1 Number of change vector buffers for media recovery
_check_block_after_checksum TRUE perform block check after checksum if both are turned on
_check_block_new_invariant_for_flashback FALSE check block new invariant for flashback
_check_column_length TRUE check column length
_check_pdbid_in_redo FALSE Enable checking of pluggable database ID in redo
_check_ts_threshold 0 check tablespace thresholds
_child_read_ahead_dba_check FALSE if TRUE, assert child read ahead dba to be continuous of parent
_cleanout_shrcur_buffers TRUE if TRUE, cleanout shrcur buffers
_cleanup_rollback_entries 100 no. of undo entries to apply per transaction cleanup
_cleanup_timeout 150 timeout value for PMON cleanup
_cleanup_timeout_flags 0 flags for PMON cleanup timeout
_clear_buffer_before_reuse FALSE Always zero-out buffer before reuse for security
_cli_cachebktalloc 100 Percentage of memory to allocate
_client_enable_auto_unregister FALSE enable automatic unregister after a send fails with timeout
_client_ntfn_cleanup_interval 2400 interval after which dead client registration cleanup task repeats
_client_ntfn_pinginterval 75 time between pings to unreachable notification clients
  _client_ntfn_pingretries 6 number of times to ping unreachable notification clients
_client_ntfn_pingtimeout 30000 timeout to connect to unreachable notification clients
_client_result_cache_bypass FALSE bypass the client result cache
_client_tstz_error_check TRUE Should Client give error for suspect Timestamp with Timezone operations
_clone_one_pdb_recovery FALSE Recover ROOT and only one PDB in clone database
  _close_cached_open_cursors FALSE close cursors cached by PL/SQL at each commit
  _close_deq_by_cond_curs FALSE Close Dequeue By Condition Cursors
  _cloud_name gsm cloud name
  _cluster_flash_cache_slave_file cluster flash cache slave file for default block size
  _cluster_library clss cluster library selection
  _clusterwide_global_transactions TRUE enable/disable clusterwide global transactions
  _collapse_wait_history FALSE collapse wait history
  _collect_tempundo_stats TRUE Collect Statistics v$tempundostat
  _collect_undo_stats TRUE Collect Statistics v$undostat
  _column_compression_factor 0 Column compression ratio
  _column_elimination_off FALSE turn off predicate-only column elimination
  _column_tracking_level 1 column usage tracking
  _common_data_view_enabled TRUE common objects returned through dictionary views
  _common_user_prefix C## Enforce restriction on a prefix of a Common User/Role/Profile name
  _compilation_call_heap_extent_size 16384 Size of the compilation call heaps extents
_COMPLEX_VIEW_MERGING Enable complex view merging
  _compression_above_cache 0 number of recompression above cache for sanity check
  _compression_advisor 0 Compression advisor
  _compression_chain 90 percentage of chained rows allowed for Compression
  _compression_compatibility Compression compatability
  _concurrency_chosen 10 what is the chosen value of concurrency
  _connect_by_use_union_all TRUE use union all for connect by
  _connection_broker_host localhost connection broker host for listen address
  _controlfile_autobackup_delay 300 time delay (in seconds) for performing controlfile autobackups
  _controlfile_backup_copy_check TRUE enable check of the copied blocks during controlfile backup copy
  _controlfile_block_size 0 control file block size in bytes
  _controlfile_enqueue_dump FALSE dump the system states after controlfile enqueue timeout
  _controlfile_enqueue_holding_time 120 control file enqueue max holding time in seconds
  _controlfile_enqueue_holding_time_tracking_size 10 control file enqueue holding time tracking size
  _controlfile_enqueue_timeout 900 control file enqueue timeout in seconds
  _controlfile_section_init_size control file initial section size
  _controlfile_section_max_expand control file max expansion rate
  _controlfile_split_brain_check TRUE Check for a split-brain in distributed lock manager domain
  _controlfile_update_check OFF controlfile update sanity check
Default: FALSE
Enables conversion of set operator to join
Default: 0
Parallel recovery coordinator side extra message buffer size
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

SQL> create spfile from pfile

SQL> startup

SQL> drop rollback segment "_SYSSMU1_1222867085$";
Default: TRUE
Enables costing of equality semi-join
Default: 1
Connection pool number of hash latches
Default: CPU
Effective thread multiplier
Default: 0
Divisor for converting CPU cost to I/O cost
  _cpu_util_adj_force 0 force cpu util adjustment
  _cpu_util_adj_target 0 cpu utilization adjustment target
  _cr_grant_global_role TRUE if TRUE, grant lock for CR requests when block is in global role
  _cr_grant_local_role AUTO turn 3-way CR grants off, make it automatic, or turn it on
  _cr_grant_only FALSE if TRUE, grant locks when possible and do not send the block
  _cr_server_log_flush TRUE if TRUE, flush redo log before serving a CR buffer
  _cr_trc_buf_size 8192 size of cr trace buffer
  _create_stat_segment 0 create ilm statistics segment
  _create_table_in_any_cluster FALSE allow creation of table in a cluster not owned by the user
  _cross_con_row_count FALSE use actual row count for cross container views
  _crs_2phase bypass crs 2phase
  _ctx_doc_policy_stems FALSE enable ctx_doc.policy_stems api
  _cu_row_locking 0 CU row level locking
  _cursor_bind_capture_area_size 400 maximum size of the cursor bind capture area
  _cursor_bind_capture_interval 900 interval (in seconds) between two bind capture for a cursor
  _cursor_cache_time 900 number of seconds a session cached cursor stay in cache.
  _cursor_db_buffers_pinned 725 additional number of buffers a cursor can pin at once
  _cursor_diagnostic_node_agedout_count 100 Number of cursor-sharing diagnostic nodes to retain before reuse
Default: 2
Shared cursor features enabled bits
Default: 1024
Number of cursors per parent before obsoletion. Use if too many obsolete cursors are being created.
-- required
events= "106001 trace name context forever, level 1024"
-- then

  _cursor_plan_enabled TRUE enable collection and display of cursor plans
  _cursor_plan_hash_version 1 version of cursor plan hash value
  _cursor_plan_unparse_enabled TRUE enables/disables using unparse to build projection/predicates
  _cursor_reload_failure_threshold 0 Number of failed reloads before marking cursor unusable
  _cursor_runtimeheap_memlimit 5242880 Shared cursor runtime heap memory limit
  _cursor_stats_enabled TRUE Enable cursor stats
  _cvmap_buffers 5000 Number of change vector buffers for multi instance media recovery
  _cvw_enable_weak_checking TRUE enable weak view checking
  _data_transfer_cache_bc_perc_x100 500 Percentange * 100 of buffer cache to transfer to data transfer cache
_data_transfer_cache_size 0 Size of data transfer cache
_data_warehousing_scan_buffers TRUE if TRUE, enable data warehousing scan buffers
_data_warehousing_scan_flash_buffers FALSE if TRUE, enable data warehousing scan flash buffers
_data_warehousing_serial_scan TRUE if TRUE, enable data warehousing serial scans
_datafile_cow FALSE Use copy on write snapshot for the renamed file
_datafile_write_errors_crash_instance TRUE datafile write errors crash instance
_dataguard_prespawn_count 0 prespawn foreground init count
_datapump_compressbas_buffer_size 0 specifies buffer size for BASIC compression algorithm
_datapump_metadata_buffer_size 131072 specifies buffer size for metadata file I/O
_datapump_tabledata_buffer_size 262144 specifies buffer size for table data file I/O
_db_16k_flash_cache_file flash cache file for 16k block size
_db_16k_flash_cache_size 0 flash cache size for _db_16k_flash_cache_file
_db_2k_flash_cache_file flash cache file for 2k block size
_db_2k_flash_cache_size 0 flash cache size for _db_2k_flash_cache_file
_db_32k_flash_cache_file flash cache file for 32k block size
_db_32k_flash_cache_size 0 flash cache size for _db_32k_flash_cache_file
_db_4k_flash_cache_file flash cache file for 4k block size
_db_4k_flash_cache_size 0 flash cache size for _db_4k_flash_cache_file
_db_8k_flash_cache_file flash cache file for 8k block size
_db_8k_flash_cache_size 0 flash cache size for _db_8k_flash_cache_file
_db_aging_cool_count 1 Touch count set when buffer cooled
_db_aging_freeze_cr FALSE Make CR buffers always be too cold to keep in cache
_db_aging_hot_criteria 2 Touch count which sends a buffer to head of replacement list
_db_aging_stay_count 0 Touch count set when buffer moved to head of replacement list
_db_aging_touch_time 3 Touch count which sends a buffer to head of replacement list
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_block_adjcheck TRUE adjacent cache buffer checks - low blkchk overwrite parameter
_db_block_adjchk_level 0 adjacent cache buffer check level
_db_block_align_direct_read TRUE Align Direct Reads
_db_block_bad_write_check FALSE enable bad write checks
_db_block_buffers 218337 Number of database blocks cached in memory: hidden parameter
_db_block_cache_clone FALSE Always clone data blocks on get (for debugging)
_db_block_cache_history 0 buffer header tracing (non-zero only when debugging)
_db_block_cache_history_level 2 buffer header tracing level
_db_block_cache_history_lru FALSE buffer header tracing for lru operations
_db_block_cache_num_umap 0 number of unmapped buffers (for tracking swap calls on blocks)
_db_block_cache_protect FALSE protect database blocks (true only when debugging)
_db_block_cache_protect_internal 0 protect database blocks (for strictly internal use only)
_db_block_check_for_debug FALSE Check more and dump block before image for debugging
_db_block_check_objtyp TRUE check objd and typ on cache disk read
_db_block_chunkify_ncmbr FALSE chunkify noncontig multi block reads
_db_block_corruption_recovery_threshold 5 threshold number of block recovery attempts
_db_block_do_full_mbreads FALSE do full block read even if some blocks are in cache
_db_block_hash_buckets 524288 Number of database block hash buckets
_db_block_hash_latches 16384 Number of database block hash latches
_db_block_header_guard_level 0 number of extra buffer headers to use as guard pages
_db_block_hi_priority_batch_size 0 Fraction of writes for high priority reasons
_db_block_known_clean_pct 2 Initial Percentage of buffers to maintain known clean
_db_block_lru_latches 32 number of lru latches
_db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba
_db_block_max_scan_pct 40 Percentage of buffers to inspect when looking for free
_db_block_med_priority_batch_size 0 Fraction of writes for medium priority reasons
_db_block_numa 1 Number of NUMA nodes
_db_block_prefetch_fast_longjumps_enabled TRUE Batched IO enable fast longjumps
_db_block_prefetch_limit 0 Prefetch limit in blocks
_db_block_prefetch_override 0 Prefetch force override in blocks
_db_block_prefetch_private_cache_enabled TRUE Batched IO enable private cache
_db_block_prefetch_quota 10 Prefetch quota as a percent of cache size
_db_block_prefetch_skip_reading_enabled TRUE Batched IO enable skip reading buffers
_db_block_prefetch_wasted_threshold_perc 2 Allowed wasted percent threshold of prefetched size
_db_block_table_scan_buffer_size 4194304 Size of shared table scan read buffer
_db_block_temp_redo FALSE generate redo for temp blocks
_db_block_trace_protect FALSE trace buffer protect calls
_db_block_vlm_check FALSE check of rvlm mapping leaks (for debugging)
_db_block_vlm_leak_threshold 4 Threshold for allowable vlm leaks
_db_blocks_per_hash_latch Number of blocks per hash latch
_db_cache_advice_hash_latch_multiple 16 cache advisory hash latch multiple
_db_cache_advice_max_size_factor 2 cache advisory maximum multiple of current size to similate
_db_cache_advice_sample_factor 4 cache advisory sampling factor
_db_cache_advice_sanity_check FALSE cache simulation sanity check
_db_cache_block_read_stack_trace 0 dump short call stack for block reads
_db_cache_crx_check FALSE check for costly crx examination functions
_db_cache_miss_check_les FALSE check LEs after cache miss
_db_cache_mman_latch_check FALSE check for wait latch get under MMAN ops in kcb
_db_cache_pre_warm TRUE Buffer Cache Pre-Warm Enabled : hidden parameter
_db_cache_process_cr_pin_max 2147483647 maximum number of cr pins a process may have
_db_cache_wait_debug 0 trace new kslwaits
_db_change_notification_enable TRUE enable db change notification
_db_check_cell_hints FALSE
_db_disable_temp_encryption FALSE Disable Temp Encryption for Spills
_db_discard_lost_masterkey FALSE discard lost masterkey handles
_db_dump_from_disk_and_efc 0 dump contents from disk and efc
_db_dw_scan_adaptive_cooling FALSE if TRUE, enable adaptive DW scan cooling
_db_dw_scan_max_shadow_count 5 DW Scan adaptive cooling max shadow count
_db_dw_scan_obj_cooling_factor 500 DW Scan object cooling factor to cool all temperatures
_db_dw_scan_obj_cooling_interval 100 DW Scan object cooling interval in number of scans, seconds, or pct of cache size
_db_dw_scan_obj_cooling_policy CACHE_SIZE DW scan objtect cooling policy
_db_dw_scan_obj_warming_increment 1000 DW Scan object warming increment when an object is scanned
_db_fast_obj_check FALSE enable fast object drop sanity check
_db_fast_obj_ckpt TRUE enable fast object checkpoint
_db_fast_obj_truncate TRUE enable fast object truncate
_db_file_direct_io_count 1048576 Sequential I/O buf size
_db_file_exec_read_count 128 multiblock read count for regular clients
_db_file_format_io_buffers 4 Block formatting I/O buf count
_db_file_noncontig_mblock_read_count 11 number of noncontiguous db blocks to be prefetched
_db_file_optimizer_read_count 8 multiblock read count for regular clients
_db_flash_cache_disable_write_batchsize 4096 Flash cache disable writes processing batchsize
_db_flash_cache_force_replenish_limit 8 Flash cache force replenish lower limit in buffers
_db_flash_cache_keep_limit 80 Flash cache keep buffer upper limit in percentage
_db_flash_cache_max_latency 400 Flash cache maximum latency allowed in 10 milliseconds
_db_flash_cache_max_outstanding_writes 32 Flash cache maximum outstanding writes allowed
_db_flash_cache_max_read_retry 3 Flash cache max read retry
_db_flash_cache_max_slow_io 3 Flash cache maximum slow io allowed
_db_flash_cache_write_limit 1 Flash cache write buffer upper limit in percentage
_db_flashback_iobuf_size 1 Flashback IO Buffer Size
_db_flashback_log_min_size 16777216 Minimum flashback database log size in bytes
_db_flashback_log_min_total_space 0 Minimum flashback database log total space in bytes
_db_flashback_num_iobuf 64 Flashback Number of IO buffers
_db_full_caching TRUE enable full db implicit caching
_db_full_db_cache_diff_pct 5 Cache at least this % larger than DB size for full db caching
_db_generate_dummy_masterkey FALSE if TRUE, use old havior and generate dummy master key
_db_handles 3000 System-wide simultaneous buffer operations
_db_handles_cached 10 Buffer handles cached each process
_db_hot_block_tracking FALSE track hot blocks for hash latch contention
_db_index_block_checking TRUE index block checking override parameter
_db_initial_cachesize_create_mb 256 size of cache created at startup
_db_l2_tracing 0 flash cache debug tracing
_db_large_dirty_queue 25 Number of buffers which force dirty queue to be written
_db_lost_write_checking 2 Enable scn based lost write detection mechanism
_db_lost_write_corrupt_block FALSE allow corruption for lost write
_db_lost_write_tracing FALSE Enable _db_lost_write_checking tracing
_db_mttr_advice ON MTTR advisory
_db_mttr_partitions 0 number of partitions for MTTR advisory
_db_mttr_sample_factor 64 MTTR simulation sampling factor
_db_mttr_sim_target MTTR simulation targets
_db_mttr_sim_trace_size 256 MTTR simulation trace size
_db_mttr_trace_to_alert FALSE dump trace entries to alert file
_db_noarch_disble_optim FALSE Image redo logging (NOARCHIVEMODE)
_db_num_evict_waitevents 64 number of evict wait events
_db_num_gsm 0 database number in gsm dbpool
_db_obj_enable_ksr TRUE enable ksr in object checkpoint/reuse
_db_percent_hot_default 50 Percent of default buffer pool considered hot
_db_percent_hot_keep 0 Percent of keep buffer pool considered hot
_db_percent_hot_recycle 0 Percent of recycle buffer pool considered hot
_db_percpu_create_cachesize 2 size of cache created per cpu in deferred cache create
_db_prefetch_histogram_statistics FALSE maintain prefetch histogram statistics in x$kcbprfhs
_db_recovery_temporal_file_dest default database recovery temporal file location
_db_required_percent_fairshare_usage 10 percent of fairshare a processor group should always use
_db_row_overlap_checking TRUE row overlap checking override parameter for data/index blocks
_db_todefer_cache_create TRUE buffer cache deferred create
_db_writer_chunk_writes 0 Number of writes DBWR should wait for
_db_writer_coalesce_area_size 4194304 Size of memory allocated to dbwriter for coalescing writes
_db_writer_coalesce_encrypted_buffers TRUE Coalecsing for encrypted buffers
_db_writer_coalesce_write_limit 131072 Limit on size of coalesced write
Default: TRUE
If FALSE, DBWR will not downgrade IMU txns for AGING. If TRUE allows Oracle the ability to artificially age a transaction for increased automatic cache management. Required for In-Memory Undo
SQL> ALTER SYSTEM SET "_db_writer_flush_imu" = FALSE;
  _db_writer_histogram_statistics FALSE maintain dbwr histogram statistics in x$kcbbhs
_db_writer_max_writes 0 Max number of outstanding DB Writer IOs
_db_writer_nomemcopy_coalesce FALSE Enable DBWR no-memcopy coalescing
_db_writer_verify_writes FALSE Enable lost write detection mechanism
_dbfs_modify_implicit_fetch TRUE DBFS Link allows implicit fetch on modify - only on SecureFiles
_dbg_proc_startup FALSE debug process startup
_dbg_scan 0 generic scan debug
_dbms_sql_security_level 1 Security level in DBMS_SQL
_dbop_enabled 1 Any positive number enables automatic DBOP monitoring. 0 is disabled
_dbpool_name gsm database pool name
_dbrm_dynamic_threshold 33490396 DBRM dynamic threshold setting
_dbrm_num_runnable_list 0 Resource Manager number of runnable list per NUMA node
_dbrm_quantum DBRM quantum
_dbrm_runchk 0 Resource Manager Diagnostic Running Thread Check
_dbrm_short_wait_us 300 Resource Manager short wait length
_dbwr_async_io TRUE Enable dbwriter asynchronous writes
_dbwr_scan_interval 300 dbwriter scan interval
_dbwr_stall_write_detection_interval 0 dbwriter stall write detection interval
_dbwr_tracing 0 Enable dbwriter tracing
_dd_validate_remote_locks TRUE GES deadlock detection validate remote locks
_dde_flood_control_init TRUE Initialize Flood Control at database open
_dead_process_scan_interval 60 PMON dead process scan interval (in seconds)
_deadlock_diagnostic_level 2 automatic deadlock resolution diagnostics level
_deadlock_record_to_alert_log TRUE record resolved deadlocks to the alert log
_deadlock_resolution_incidents_always FALSE create incidents when resolving any deadlock?
_deadlock_resolution_incidents_enabled TRUE create incidents during deadlock resolution
_deadlock_resolution_level 1 automatic deadlock resolution level
_deadlock_resolution_min_wait_timeout_secs 60 the minimum wait timeout required for deadlock resolution
_deadlock_resolution_signal_process_thresh_secs 60 the amount of time given to process a deadlock resolution signal
_debug_enable FALSE wait for debug
_dedicated_server_poll_count 10 dedicated server poll count
_dedicated_server_post_wait FALSE dedicated server post/wait
_dedicated_server_post_wait_call FALSE dedicated server post/wait call
_default_encrypt_alg 0 default encryption algorithm
_default_non_equality_sel_check TRUE sanity check on default selectivity for like/range predicate
_defer_eor_orl_arch_for_so TRUE defer EOR ORL archival for switchover
_defer_log_boundary_ckpt TRUE defer media recovery checkpoint at log boundary
_defer_log_count 100 Number of log boundaries media recovery checkpoint lags behind
_defer_rcv_during_sw_to_sby FALSE Defer recovery during switchover to standby
_defer_sga_alloc_chunk_size 1073741824 Chunk size for defer sga allocation
_defer_sga_enabled FALSE Enable deferred shared memory allocation for SGA
_defer_sga_min_spsz_at_startup 53687091200 Minimum shared pool size at startup with deferred sga enabled
_defer_sga_min_total_defer_segs_sz 107374182400 Minimum total deferred segs size for defer sga allocation
_defer_sga_test_alloc_intv 0 SA** sleeps for N secs before allocating a deferred segment
_deferred_constant_folding_mode DEFAULT Deferred constant folding mode
_deferred_log_dest_is_valid TRUE consider deferred log dest as valid for log deletion (TRUE/FALSE)
_deferred_seg_in_seed TRUE Enable Deferred Segment Creation in Seed
_delay_index_maintain TRUE delays index maintenance until after MV is refreshed
_delta_push_share_blockers 0 enable delta push if greater than the # of share blockers
_deq_execute_reset_time 30 deq execute reset time
_deq_ht_child_latches 8 deq ht child latches
_deq_ht_max_elements 100000 deq ht max elements
_deq_large_txn_size 25000 deq large txn size
_deq_log_array_size 10000 deq log array size
_deq_max_fetch_count 10 deq max fetch count
_deq_maxwait_time 0 Change wait times between dequeue calls
_desired_readmem_rate 90 The desired percentage of redo reading from memory
_dg_broker_trace_level data guard broker trace level
_dg_cf_check_timer 15 Data Guard controlfile check timer
_dg_corrupt_redo_log 0 Corrupt redo log validation during archivals
_diag_adr_auto_purge TRUE Enable/disable ADR MMON Auto Purging
_diag_adr_enabled TRUE Parameter to enable/disable Diag ADR
_diag_adr_test_param 0 Test parameter for Diagnosability
_diag_adr_trace_dest C:\app\oracle\diag\rdbms\orabase\orabase\trace diagnosability trace directory path
_diag_arb_before_kill FALSE dump diagnostics before killing unresponsive ARBs
_diag_backward_compat TRUE Backward Compatibility for Diagnosability
_diag_cc_enabled TRUE Parameter to enable/disable Diag Call Context
_diag_conf_cap_enabled TRUE Parameter to enable/disable Diag Configuration Capture
_diag_crashdump_level 10 parameter for systemstate dump level, used by DIAG during crash
_diag_daemon TRUE start DIAG daemon
_diag_dde_async_age_limit 300 diag dde async actions: message age limit (in seconds)
_diag_dde_async_cputime_limit 300 diag dde async actions: action cputime limit (in seconds)
_diag_dde_async_mode 1 diag dde async actions: dispatch mode
_diag_dde_async_msg_capacity 1024 diag dde async actions: message buffer capacity
_diag_dde_async_msgs 50 diag dde async actions: number of preallocated message buffers
_diag_dde_async_process_rate 5 diag dde async actions: message processing rate - per loop
_diag_dde_async_runtime_limit 900 diag dde async actions: action runtime limit (in seconds)
_diag_dde_async_slaves 5 diag dde async actions: max number of concurrent slave processes
_diag_dde_enabled TRUE enable DDE handling of critical errors
_diag_dde_fc_enabled TRUE Parameter to enable/disable Diag Flood Control
_diag_dde_fc_implicit_time 0 Override Implicit Error Flood Control time parameter
_diag_dde_fc_macro_time 0 Override Macro Error Flood Control time parameter
_diag_dde_inc_proc_delay 1 The minimum delay between two MMON incident sweeps (minutes)
_diag_diagnostics TRUE Turn off diag diagnostics
_diag_dump_request_debug_level 1 DIAG dump request debug level (0-2)
_diag_dump_timeout 30 timeout parameter for SYNC dump
_diag_enable_startup_events FALSE enable events in instance startup notifiers
_diag_hm_rc_enabled TRUE Parameter to enable/disable Diag HM Reactive Checks
_diag_hm_tc_enabled FALSE Parameter to enable/disable Diag HM Test(dummy) Checks
_diag_patch_cap_enabled TRUE Parameter to enable/disable Diag Patch Configuration Capture
_diag_proc_enabled TRUE enable hung process diagnostic API
_diag_proc_max_time_ms 30000 hung process diagnostic API max wait time in milliseconds
_diag_proc_stack_capture_type 1 hung process diagnostic API stack capture type
_diag_test_seg_reinc_mode FALSE Sets trace segmentation to be in reincarnation mode
_diag_uts_control 0 UTS control parameter
_diag_verbose_error_on_init 0 Allow verbose error tracing on diag init
_diag_xm_enabled FALSE If TRUE, DIAG allows message exchanges across DB/ASM boundary
Default: TRUE
Control dimension skip when null feature
  _direct_io_skip_cur_slot_on_error TRUE Skip current slot on error
_direct_io_slots 0 number of slots for direct path I/O
_direct_io_wslots 0 number of write slots for direct path I/O
_direct_path_insert_features 0 disable direct path insert features
_direct_read_decision_statistics_driven TRUE enable direct read decision based on optimizer statistics
_dirty_appliance_mode FALSE Enable appliance mode even on non-appliance
_disable_12751 FALSE disable policy timeout error (ORA-12751)
_disable_12cbigfile FALSE DIsable Storing ILM Statistics in 12cBigFiles
_disable_active_influx_move FALSE disable active influx move during parallel media recovery
_disable_adaptive_shrunk_aggregation FALSE adaptive shrunk aggregation
_disable_appliance_check FALSE Disable appliance-specific code
_disable_appliance_partnering FALSE Disable appliance partnering algorithms
_disable_autotune_gtx FALSE disable autotune global transaction background processes
_disable_block_checking FALSE disable block checking at the session level
_disable_cdb_view_rc_invalidation FALSE disable Result Cache invalidation for CDB View results
_disable_cell_optimized_backups FALSE disable cell optimized backups
_disable_cpu_check FALSE disable cpu_count check
_disable_cursor_sharing FALSE disable cursor sharing
_disable_datalayer_sampling FALSE disable datalayer sampling
_disable_directory_link_check FALSE Disable directory link checking
_disable_duplex_link TRUE Turn off connection duplexing
_disable_duplicate_service_warning FALSE disable listener warning for duplicate service
_disable_fast_aggregation FALSE fast aggregation
_disable_fast_validate FALSE disable PL/SQL fast validation
_disable_fastopen FALSE Do Not Use Fastopen
_disable_fba_qrw 0 disable flashback archiver query rewrite
_disable_fba_wpr 0 disable flashback archiver wait for prepared transactions
_disable_file_locks FALSE disable file locks for control, data, redo log files
_disable_file_resize_logging FALSE disable file resize logging to alert log
_disable_flashback_archiver 0 disable flashback archiver
_disable_flashback_recyclebin_opt TRUE Don't use the Flashback Recyclebin optimization
_disable_flashback_wait_callback FALSE Disable flashback wait callback
_disable_function_based_index FALSE disable function-based index matching
_disable_gvaq_cache FALSE Disable cache
_disable_health_check FALSE Disable Health Check
_disable_highres_ticks FALSE disable high-res tick counter
_disable_image_check FALSE Disable Oracle executable image checking
_disable_implicit_row_movement FALSE disable implicit row movement
_disable_incremental_checkpoints FALSE Disable incremental checkpoints for thread recovery
_disable_incremental_recovery_ckpt FALSE Disable incremental recovery checkpoint mechanism
_disable_index_block_prefetching FALSE disable index block prefetching
_disable_initial_block_compression FALSE disable initial block compression
_disable_instance_params_check FALSE disable instance type check for ksp
_disable_interface_checking FALSE disable interface checking at startup
_disable_kcb_flashback_blocknew_opt FALSE Disable KCB flashback block new optimization
_disable_kcbhxor_osd FALSE disable kcbh(c)xor OSD functionality
_disable_kcbl_flashback_blocknew_opt FALSE Disable KCBL flashback block new optimization
_disable_kgghshcrc32_osd FALSE disable kgghshcrc32chk OSD functionality
_disable_latch_free_SCN_writes_via_32cas FALSE disable latch-free SCN writes using 32-bit compare & swap
_disable_latch_free_SCN_writes_via_64cas FALSE disable latch-free SCN writes using 64-bit compare & swap
_disable_logging FALSE Disable logging
_disable_metrics_group 0 Disable Metrics Group (or all Metrics Groups)
_disable_modsvc_refresh disable modify service cache refresh
_disable_multiple_block_sizes FALSE disable multiple block size support (for debugging)
_disable_ntlog_events FALSE Disable logging to NT event log
_disable_odm FALSE disable odm feature
_disable_oradebug_commands none disable execution of certain categories of oradebug commands
_disable_parallel_conventional_load FALSE Disable parallel conventional loads
_disable_primary_bitmap_switch FALSE disable primary bitmap switch
_disable_read_only_open_dict_check FALSE Disable read-only open dictionary check
_disable_rebalance_space_check FALSE disable space usage checks for storage reconfiguration
_disable_recovery_read_skip FALSE Disable the read optimization during media recovery
_disable_rolling_patch 0 Disable Rolling Patch Feature
_disable_sample_io_optim FALSE disable row sampling IO optimization
_disable_savepoint_reset FALSE disable the fix for bug 1402161
_disable_selftune_checkpointing FALSE Disable self-tune checkpointing
_disable_storage_type FALSE Disable storage type checks
_disable_streams_diagnostics 0 streams diagnostics
_disable_streams_pool_auto_tuning FALSE disable streams pool auto tuning
_disable_system_state 4294967294 disable system state dump
_disable_system_state_wait_samples FALSE Disable system state dump - wait samples
_disable_temp_tablespace_alerts FALSE disable tablespace alerts for TEMPORARY tablespaces
_disable_thread_internal_disable FALSE Disable thread internal disable feature
_disable_thread_snapshot TRUE Thread snapshot
_disable_txn_alert 0 disable txn layer alert
_disable_undo_tablespace_alerts FALSE disable tablespace alerts for UNDO tablespaces
_disable_wait_state Disable wait state
_discard_cmn_ddl_in_pdb_err FALSE Discard error when Common DDL is attempted in PDB
_discrete_transactions_enabled FALSE enable OLTP mode
_disk_sector_size_override FALSE if TRUE, OSD sector size could be overridden
_diskmon_pipe_name DiSKMon skgznp pipe name
_dispatcher_listen_on_vip FALSE Determines if dispatcher listens on VIP if no HOST is supplied
_dispatcher_rate_scale scale to display rate statistic (100ths of a second)
_dispatcher_rate_ttl time-to-live for rate statistic (100ths of a second)
_distinct_agg_optimization_gsets CHOOSE Use Distinct Aggregate Optimization for Grouping Sets
_distinct_view_unnesting FALSE enables unnesting of in subquery into distinct view
_distributed_recovery_connection_hold_time 200 number of seconds RECO holds outbound connections open
_dlmtrace Trace string of global enqueue type(s)
_dm_dmf_details_compatibility 12.1.0 set dm dmf details compatibility version
_dm_enable_legacy_dmf_output_types FALSE revert dmf output types to pre-
_dm_max_shared_pool_pct 1 max percentage of the shared pool to use for a mining model
_dml_batch_error_limit 0 number or error handles allocated for DML in batch mode
_dml_frequency_tracking FALSE Control DML frequency tracking
_dml_frequency_tracking_advance TRUE Control automatic advance and broadcast of DML frequencies
_dml_frequency_tracking_slot_time 15 Time length of each slot for DML frequency tracking
_dml_frequency_tracking_slots 4 Number of slots to use for DML frequency tracking
_dml_monitoring_enabled TRUE enable modification monitoring
_dnfs_rdma_enable 1 Enable dNFS RDMA transfers
_dnfs_rdma_max 1048576 Maximum size of dNFS RDMA transfer
_dnfs_rdma_min 8192 Minimum size of dNFS RDMA transfer
_domain_index_batch_size 2000 maximum number of rows from one call to domain index fetch routine
_domain_index_dml_batch_size 200 maximum number of rows for one call to domain index dml routines
_dra_bmr_number_threshold 1000 Maximum number of BMRs that can be done to a file
_dra_bmr_percent_threshold 10 Maximum percentage of blocks in a file that can be BMR-ed
_dra_enable_offline_dictionary FALSE Enable the periodic creation of the offline dictionary for DRA
_drm_parallel_freeze TRUE if TRUE enables parallel drm freeze
_drop_flashback_logical_operations_enq FALSE Drop logical operations enqueue immediately during flashback marker generation
_drop_stat_segment 0 drop ilm statistics segment
_drop_table_granule 256 drop_table_granule
_drop_table_optimization_enabled TRUE reduce SGA memory use during drop of a partitioned table
_ds_enable_auto_txn FALSE Dynamic Sampling Service Autonomous Transaction control parameter
_ds_iocount_iosize 6553664 Dynamic Sampling Service defaults: #IOs and IO Size
_ds_parse_model 2 Dynamic Sampling Service Parse Model control parameter
_dsc_feature_level 0 controls the feature level for deferred segment creation
_dskm_health_check_cnt 20 DiSKMon health check counter
_dss_cache_flush FALSE enable full cache flush for parallel execution
_dtree_area_size 131072 size of Decision Tree Classification work area
_dtree_binning_enabled TRUE Decision Tree Binning Enabled
_dtree_bintest_id 0 Decision Tree Binning Test ID
_dtree_compressbmp_enabled TRUE Decision Tree Using Compressed Bitmaps Enabled
_dtree_max_surrogates 1 maximum number of surrogates
_dtree_pruning_enabled TRUE Decision Tree Pruning Enabled
_dummy_instance FALSE dummy instance started by RMAN
_dump_10261_level 0 Dump level for event 10261, 1=>minimal dump 2=>top pga dump
_dump_common_subexpressions FALSE dump common subexpressions
_dump_connect_by_loop_data FALSE dump connect by loop error message into trc file
_dump_cursor_heap_sizes FALSE dump comp/exec heap sizes to tryace file
_dump_interval_limit 120 trace dump time interval limit (in seconds)
_dump_max_limit 5 max number of dump within dump interval
_dump_qbc_tree 0 dump top level query parse tree to trace
_dump_rcvr_ipc TRUE if TRUE enables IPC dump at instance eviction time
_dump_scn_increment_stack Dumps scn increment stack per session
_dump_system_state_scope local scope of sysstate dump during instance termination
_dump_trace_scope global scope of trace dump during a process crash
_dynamic_rls_policies TRUE rls policies are dynamic
_dynamic_share_range_factor 2 dynamic share range factor
_dynamic_stats_threshold 30 delay threshold (in seconds) between sending statistics messages
  _early_flush_delta 0 SCN delta to trigger early log flush
_eighteenth_spare_parameter eighteenth spare parameter - integer
_eighth_spare_parameter eighth spare parameter - integer
_eightieth_spare_parameter eightieth spare parameter - integer
_eighty-eighth_spare_parameter eighty-eighth spare parameter - string
_eighty-fifth_spare_parameter eighty-fifth spare parameter - string
_eighty-first_spare_parameter eighty-first spare parameter - string
_eighty-fourth_spare_parameter eighty-fourth spare parameter - string
_eighty-ninth_spare_parameter eighty-ninth spare parameter - string
_eighty-second_spare_parameter eighty-second spare parameter - string
_eighty-seventh_spare_parameter eighty-seventh spare parameter - string
_eighty-sixth_spare_parameter eighty-sixth spare parameter - string
_eighty-third_spare_parameter eighty-third spare parameter - string
_eleventh_spare_parameter eleventh spare parameter - integer
Default: TRUE
Enables elimination of common sub-expressions
  _emon_max_active_connections 256 maximum open connections to clients per emon
_emon_outbound_connect_timeout 30000 timeout for completing connection set up to clients
_emon_pool_inc 4 increment in EMON slaves per pool type
_emon_pool_max 16 maximum number of EMON slaves per pool type
_emon_pool_min 4 minimum number of EMON slaves per pool type
_emon_regular_ntfn_slaves 4 number of EMON slaves doing regular database notifications
_emon_send_timeout 10000 send timeout after which the client is unregistered
_emx_control 0 EM Express control (internal use only)
_emx_max_sessions 128 Maximum number of sessions in the EM Express cache
_emx_session_timeout 3600 Session timeout (sec) in the EM Express cache
_enable_12g_bft TRUE enable 12g bigfile tablespace
_enable_Front_End_View_Optimization 1 enable front end view optimization
_enable_NUMA_interleave TRUE Enable NUMA interleave mode
_enable_NUMA_optimization FALSE Enable NUMA specific optimizations
_enable_NUMA_support FALSE Enable NUMA support and optimizations
_enable_asyncvio FALSE enable asynch vectored I/O
_enable_automatic_maintenance 1 if 1, Automated Maintenance Is Enabled
_enable_automatic_sqltune TRUE Automatic SQL Tuning Advisory enabled parameter
_enable_block_level_transaction_recovery TRUE enable block level recovery
_enable_check_truncate TRUE enable checking of corruption caused by canceled truncate
_enable_columnar_cache 1 Enable Columnar Flash Cache Rewrite
_enable_cscn_caching FALSE enable commit SCN caching for all transactions
_enable_ddl_wait_lock TRUE use this to turn off ddls with wait semantics
_enable_default_affinity 0 enable default implementation of hard affinity osds
_enable_default_temp_threshold TRUE Enable Default Tablespace Utilization Threshold for UNDO Tablespaces
_enable_default_undo_threshold TRUE Enable Default Tablespace Utilization Threshold for TEMPORARY Tablespaces
_enable_dml_lock_escalation TRUE enable dml lock escalation against partitioned tables if TRUE
_enable_editions_for_users FALSE enable editions for all users
_enable_exchange_validation_using_check TRUE use check constraints on the table for validation
_enable_fast_file_zero TRUE enable fast file zero code path
_enable_fast_ref_after_mv_tbs FALSE enable fast refresh after move tablespace
_enable_flash_logging TRUE Enable Exadata Smart Flash Logging
_enable_hash_overflow FALSE TRUE - enable hash cluster overflow based on SIZE
_enable_heatmap_internal FALSE heatmap related - to be used by oracle dev only
_enable_hwm_sync TRUE enable HWM synchronization
_enable_iee_stats TRUE enables IEE stats gathering
_enable_ilm_flush_stats TRUE Enable ILM Stats Flush
_enable_ilm_testflush_stats FALSE Enable Test ILM Stats Flush
_enable_kernel_io_outliers FALSE enable kernel I/O outlier feature
_enable_kqf_purge TRUE Enable KQF fixed runtime table purge
_enable_list_io FALSE Enable List I/O
_enable_metrics_allpdb TRUE Enable/Disable Metrics for Root and all PDBs if applicable
_enable_metrics_pdb FALSE Enable/Disable Metrics for this Non-Root PDB
_enable_midtier_affinity TRUE enable midtier affinity metrics processing
_enable_minscn_cr TRUE enable/disable minscn optimization for CR
_enable_nativenet_tcpip FALSE Enable skgxp driver usage for native net
_enable_obj_queues TRUE enable object queues
_enable_offloaded_writes FALSE Enable offloaded writes for Unit Test
_enable_online_index_without_s_locking TRUE Allow online index creation algorithm without S DML lock
_enable_pdb_close_abort FALSE Enable PDB shutdown abort (close abort)
_enable_pluggable_database FALSE Enable Pluggable Database
_enable_query_rewrite_on_remote_objs TRUE mv rewrite on remote table/view
_enable_redo_global_post FALSE LGWR post globally on write
_enable_refresh_schedule TRUE enable or disable MV refresh scheduling (revert to 9.2 behavior)
_enable_reliable_latch_waits TRUE Enable reliable latch waits
_enable_rename_user FALSE enable RENAME-clause using ALTER USER statement
_enable_rlb TRUE enable RLB metrics processing
_enable_row_shipping TRUE use the row shipping optimization for wide table selects
_enable_sb_detection TRUE Split Brain Detection
Default: FALSE
Enable DDL operations (e.g. creation) involving schema synonyms
ALTER SYSTEM SET "_enable_schema_synonyms"=TRUE SCOPE=SPFILE;
-- for examples of schema synonym creation click on the Synonyms link at page bottom
  _enable_scn_wait_interface TRUE use this to turn off scn wait interface in kta
_enable_securefile_flashback_opt FALSE Enable securefile flashback optimization
_enable_separable_transactions FALSE enable/disable separable transactions
_enable_shared_pool_durations TRUE temporary to disable/enable kgh policy
_enable_shared_server_vector_io FALSE Enable shared server vector I/O
_enable_space_preallocation 3 enable space pre-allocation
_enable_spacebg TRUE enable space management background task
_enable_tablespace_alerts TRUE enable tablespace alerts
Default: TRUE
Enable type dependent selectivity estimates
  _endprot_chunk_comment chk 10235 dflt chunk comment for selective overrun protection
_endprot_heap_comment hp 10235 dflt heap comment for selective overrun protection
_endprot_subheaps TRUE selective overrun protection for subeheaps
_enqueue_deadlock_detect_all_global_locks FALSE enable deadlock detection on all global enqueues
_enqueue_deadlock_scan_secs 0 deadlock scan interval
_enqueue_deadlock_time_sec 5 requests with timeout <= this will not have deadlock detection
_enqueue_debug_multi_instance FALSE debug enqueue multi instance
_enqueue_hash 979 enqueue hash table length
_enqueue_hash_chain_latches 4 enqueue hash chain latches
_enqueue_locks 5860 locks for managed enqueues
_enqueue_paranoia_mode_enabled FALSE enable enqueue layer advanced debugging checks
_enqueue_resources 2296 resources for enqueues
_enqueue_sync_retry_attempts 3 max number of times the bg process to retry synchronous enqueue open if it failed because master could not allocate memory
_enqueue_sync_sim_mem_error FALSE simulate master instance running out of memory when synchronously getting a remotely mastered enqueue
_evolve_plan_baseline_report_level typical Level of detail to show in plan verification/evolution report
_evt_system_event_propagation TRUE disable system event propagation
_exafusion_enabled auto enable exafusion
_expand_aggregates TRUE expand aggregates
_explain_rewrite_mode FALSE allow additional messages to be generated during explain rewrite
_extended_pruning_enabled TRUE do runtime pruning in iterator if set to TRUE
_external_scn_logging_threshold_seconds 86400 High delta SCN threshold in seconds
_external_scn_rejection_delta_threshold_minutes 0 external SCN rejection delta threshold in minutes
_external_scn_rejection_threshold_hours 24 Lag in hours between max allowed SCN and an external SCN
_external_table_smart_scan HADOOP_ONLY External Table Smart Scan
  _fair_remote_cvt FALSE if TRUE enables fair remote convert
_fairness_threshold 2 number of times to CR serve before downgrading lock
_fast_cursor_reexecute FALSE use more memory in order to get faster execution
_fast_dual_enabled TRUE enable/disable fast dual
Default TRUE
Enable/disable index fast full scan
  _fast_index_maintenance TRUE fast global index maintenance during PMOPs
_fast_psby_conversion TRUE Enable fast physical standby conversion
_fastpin_enable 1 enable reference count based fast pins
_fbda_busy_percentage 0 flashback archiver busy percentage
_fbda_debug_assert 0 flashback archiver debug assert for testing
_fbda_debug_mode 0 flashback archiver debug event for testing
_fbda_global_bscn_lag 0 flashback archiver global barrier scn lag
_fbda_inline_percentage 0 flashback archiver inline percentage
_fbda_rac_inactive_limit 0 flashback archiver rac inactive limit
_fg_iorm_slaves 1 ForeGround I/O slaves for IORM
_fg_log_checksum TRUE Checksum redo in foreground process
_fg_sync_sleep_usecs 0 Log file sync via usleep
_fic_algorithm_set automatic Set Frequent Itemset Counting Algorithm
_fic_area_size 131072 size of Frequent Itemset Counting work area
_fic_max_length 20 Frequent Itemset Counting Maximum Itemset Length
_fic_min_bmsize 1024 Frequent Itemset Counting Minimum BITMAP Size
_fic_outofmem_candidates FALSE Frequent Itemset Counting Out Of Memory Candidates Generation
_fifteenth_spare_parameter fifteenth spare parameter - integer
_fifth_spare_parameter fifth spare parameter - integer
_fiftieth_spare_parameter fiftieth spare parameter - integer
_fifty-eighth_spare_parameter fifty-eighth spare parameter - integer
_fifty-fifth_spare_parameter fifty-fifth spare parameter - integer
_fifty-first_spare_parameter fifty-first spare parameter - integer
_fifty-fourth_spare_parameter fifty-fourth spare parameter - integer
_fifty-ninth_spare_parameter fifty-ninth spare parameter - integer
_fifty-second_spare_parameter fifty-second spare parameter - integer
_fifty-seventh_spare_parameter fifty-seventh spare parameter - integer
_fifty-sixth_spare_parameter fifty-sixth spare parameter - integer
_fifty-third_spare_parameter fifty-third spare parameter - integer
_file_offline_sync_timeout 900 Timeout to sync file offline enqueue (secs)
_file_set_enqueue_timeout 1200 Timeout to acquire file set enqueue (secs)
_file_size_increase_increment 67108864 Amount of file size increase increment, in bytes
_filemap_dir FILEMAP directory
Default TRUE
Enable the use of dynamic proration of join cardinalities
  _first_spare_parameter first spare parameter - integer
_fix_control bug fix control parameter
_flashback_11.1_block_new_opt FALSE use 11.1 flashback block new optimization scheme
_flashback_allow_noarchivelog FALSE Allow enabling flashback on noarchivelog database
_flashback_archiver_partition_size 0 flashback archiver table partition size
_flashback_barrier_interval 1800 Flashback barrier interval in seconds
_flashback_copy_latches 10 Number of flashback copy latches
_flashback_database_test_only FALSE Run Flashback Database in test mode
_flashback_delete_chunk_MB 128 Amount of flashback log (in MB) to delete in one attempt
_flashback_dynamic_enable TRUE enable flashback enable code path
_flashback_dynamic_enable_failure 0 Simulate failures during dynamic enable
_flashback_enable_ra TRUE Flashback enable read ahead
_flashback_format_chunk_mb 4 Chunk mega-bytes for formatting flashback logs using sync write
_flashback_format_chunk_mb_dwrite 16 Chunk mega-bytes for formatting flashback logs using delayed write
_flashback_fuzzy_barrier TRUE Use flashback fuzzy barrier
_flashback_generation_buffer_size 16777216 flashback generation buffer size
_flashback_hint_barrier_percent 20 Flashback hint barrier percent
_flashback_log_io_error_behavior 0 Specify Flashback log I/O error behavior
_flashback_log_min_size 100 Minimum flashback log size
_flashback_log_rac_balance_factor 10 flashback log rac balance factor
_flashback_log_size 1000 Flashback log size
_flashback_logfile_enqueue_timeout 600 flashback logfile enqueue timeout for opens
_flashback_marker_cache_enabled TRUE Enable flashback database marker cache
_flashback_marker_cache_size 328 Size of flashback database marker cache
_flashback_max_log_size 0 Maximum flashback log size in bytes (OS limit)
_flashback_max_n_log_per_thread 2048 Maximum number of flashback logs per flashback thread
_flashback_max_standby_sync_span 300 Maximum time span between standby recovery sync for flashback
_flashback_n_log_per_thread 128 Desired number of flashback logs per flashback thread
_flashback_prepare_log TRUE Prepare Flashback logs in the background
_flashback_size_based_on_redo TRUE Size new flashback logs based on average redo log size
_flashback_standby_barrier_interval 1 Flashback standby barrier interval in seconds
_flashback_validate_controlfile FALSE validate flashback pointers in controlfile for database
_flashback_verbose_info FALSE Print verbose information about flashback database
_flashback_write_max_loop_limit 10 Flashback writer loop limit before it returns
_flush_ilm_stats 0 flush ilm stats
_flush_plan_in_awr_sql 0 Plan is being flushed from an AWR flush SQL
_flush_redo_to_standby 0 Flush redo to standby test event parameter
_flush_undo_after_tx_recovery TRUE if TRUE, flush undo buffers after TX recovery
_force_arch_compress 0 Archive Compress all newly created compressed tables
_force_datefold_trunc FALSE force use of trunc for datefolding rewrite
_force_hash_join_spill FALSE force hash join to spill to disk
_force_hsc_compress FALSE compress all newly created tables
_force_logging_in_upgrade TRUE force logging during upgrade mode
_force_oltp_compress FALSE OLTP Compress all newly created compressed tables
_force_oltp_update_opt TRUE OLTP Compressed row optimization on update
_force_rcv_info_ping 0 Force recovery info ping to stdby
_force_rewrite_enable FALSE control new query rewrite features
_force_slave_mapping_intra_part_loads FALSE Force slave mapping for intra partition loads
_force_sys_compress TRUE Sys compress
_force_temptables_for_gsets FALSE executes concatenation of rollups using temp tables
_force_tmp_segment_loads FALSE Force tmp segment loads
_fortieth_spare_parameter fortieth spare parameter - integer
_forty-eighth_spare_parameter forty-eighth spare parameter - integer
_forty-fifth_spare_parameter forty-fifth spare parameter - integer
_forty-first_spare_parameter forty-first spare parameter - integer
_forty-fourth_spare_parameter forty-fourth spare parameter - integer
_forty-ninth_spare_parameter forty-ninth spare parameter - integer
_forty-second_spare_parameter forty-second spare parameter - integer
_forty-seventh_spare_parameter forty-seventh spare parameter - integer
_forty-sixth_spare_parameter forty-sixth spare parameter - integer
_forty-third_spare_parameter forty-third spare parameter - integer
_forwarded_2pc_threshold 10 auto-tune threshold for two-phase commit rate across RAC instances
_fourteenth_spare_parameter fourteenth spare parameter - integer
_fourth_spare_parameter fourth spare parameter - integer
_frame_cache_time 0 number of seconds a cached frame page stay in cache.
_full_diag_on_rim FALSE rim nodes have full DIA* function
_full_pwise_join_enabled TRUE enable full partition-wise join when TRUE
_fusion_security TRUE Fusion Security
_HIGH_PRIORITY_PROCESSES High priority process name mask

More information

Default: 3
Determines the number of pools used by In-Memory Undo

Default: TRUE
Determines whether In-Memory Undo is active
_ksb_restart_policy_times='240' # internal update to set default

Default: 0
Automatically initiate log write if this many redo blocks in buffer
_MEMORY_BROKER_STAT_INTERVAL Sets the interval between AMM checks to resize the SGA. The range of values is 1 to 999 seconds

controls whether new sorts can be used as system sort
_newsort_enabled=<FALSE | TRUE>
alter session set "_newsort_enabled"=TRUE;

controls when new sort avoids sorting ordered input
Default: 63

specifies options for the new sort algorithm
Default: 0

enables the use of new cost estimate for sort
_new_sort_cost_estimate=<FALSE | TRUE>

enable batching of the RHS IO in NLJ
Default is 1

converting a non-cdb to a pdb
_noncdb_to_pdb=<FALSE | TRUE>

adjust stats for skews across partitions
_optim_adjust_for_part_skews=<FALSE | TRUE>

TRUE to enable index [fast] full scan more often
_optim_enhance_nnull_detection=<FALSE | TRUE>

improves the way default equijoin selectivity are computed
_optim_new_default_join_sel=<FALSE | TRUE>

enable peeking of user binds
_optim_peek_user_binds=<FALSE | TRUE>

optimizer adaptive cursor sharing
_optimizer_adaptive_cursor_sharing=<FALSE | TRUE>

enable improved costing of index access using in-list(s)
Default is ALL

cost factor for cost-based query transformation
Default is 50

disable cost based transformation query size restriction
_optimizer_cbqt_no_size_restriction=<FALSE | TRUE>

enable selectivity estimation for builtin functions
_optimizer_complex_pred_selectivity=<FALSE | TRUE>

force index stats collection on index creation/rebuild
_optimizer_compute_index_stats=<FALSE | TRUE>

combine no filtering connect by and start with
_optimizer_connect_by_combine_sw=<FALSE | TRUE>

use cost-based transformation for connect by
_optimizer_connect_by_cost_based=<FALSE | TRUE>

force correct computation of subquery selectivity
_optimizer_correct_sq_selectivity=<FALSE | TRUE>

enables cost-based query transformation
Default is LINEAR

add cost of generating result set when #rows per key > 1
_optimizer_cost_hjsmj_multimatch=<FALSE | TRUE>

optimizer cost model
Default is CHOOSE

use join selectivity in choosing star transformation dimensions
_optimizer_dim_subq_join_sel=<FALSE | TRUE>

time window for invalidation of cursors of analyzed objects
time window for invalidation of cursors of analyzed obj
Default is 18000

retain equi-join pred upon transitive equality pred generation
_optimizer_transitivity_retain=<FALSE | TRUE>

running an Oracle-supplied script
_oracle_script=<FALSE | TRUE>

enable ordered nested loop costing
_ordered_nested_loop=<FALSE | TRUE>

 enable OR expanded plans for NVL/DECODE predicate
_or_expand_nvl_predicate=<FALSE | TRUE>

enable broadcasting of small inputs to hash and sort merge joins
_parallel_broadcast_enabled=<FALSE | TRUE>

TRUE to obey force parallel query/dml/ddl under System PL/SQL
_parallel_syspls_obey_force=<FALSE | TRUE>
_PRED_MOVE_AROUND Undocumented
_PX_PWG_ENABLED Undocumented

perform general rewrite using set operator summaries
_query_rewrite_setopgrw_enable=<FALSE | TRUE>

hint for real-free page size in bytes
Default = 65536

recursive transactions may be IMU
Enables recursive SQL to use In-Memory Undo
_recursive_imu_transactions=<FALSE | TRUE>

enables removal of subsumed aggregated subquery
_remove_aggr_subquery=<FALSE | TRUE>

right outer/semi/anti hash enabled
_right_outer_hash_enable=<FALSE | TRUE>

control rewrite self-join algorithm
_selfjoin_mv_duplicates=<FALSE | TRUE>

specifies compile-time unfolding of sql model for loops
Default = 'RUN_TIME'

Parsed category qualifier for applying hintsets
Default = 'DEFAULT'

enable the use of subquery predicates to perform pruning
_subquery_pruning_enabled=<FALSE | TRUE>

enable the use of subquery predicates with MVs to perform pruning
_subquery_pruning_mv_enabled=<FALSE | TRUE>
_SYSTEM_TRIG_ENABLED If set to FALSE system triggers will not fire
_system_trig_enabled"=<FALSE | TRUE>
alter session set "_system_trig_enabled"=FALSE;

bump estimated full table scan and index ffs cost by one
_table_scan_cost_plus_one=<FALSE | TRUE>

create publicly accessible trace files
Sets the file protection mask to 644
_trace_files_public=<FALSE | TRUE>;
alter session set "_trace_files_public"=TRUE;

trace tempspace management
Sets the file protection mask to 644
_trace_temp=<FALSE | TRUE>;
alter session set "_trace_temp"=TRUE;

enable auto tuning of undo_retention
Undocumented but appears to apply segment compression to undo blocks
_undo_autotune=<FALSE | TRUE>;

enable undo block compression
Undocumented but appears to apply segment compression to undo blocks
_undo_block_compression=<FALSE | TRUE>;

debug flag for undo related operations
Undocumented but appears to apply segment compression to undo blocks
Default is 0

invoke undo usage functions for testing
Undocumented but appears to apply segment compression to undo blocks
Default is 0

expand queries with GSets into UNIONs for rewrite

enables unnesting of complex subqueries
_unnest_subquery=<FALSE | TRUE>
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;

SELECT srvr_id
FROM servers
WHERE srvr_id IN (
  SELECT srvr_id
  FROM serv_inst);

SELECT * FROM TABLE(dbms_xplan.display);

enable the use of column statistics for DDP functions
_use_column_stats_for_function=<FALSE | 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