_ac_strict_SCN_checkOracle Startup Parameters
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. 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.
Purpose  
Page Index
 
Documented
Advanced Queuing (AQ) & Streams
AQ_TM_PROCESSES

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

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

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

If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool.
streams_pool_size=0
  _aq_Txn_ht_sz 1024 Message cache Txn Hash table size
_aq_addpt_batch_size 1 Add PT batch Size
_aq_disable_x FALSE AQ - Disable new cross processes at an instance
_aq_dq_prefetch_Siz 5 AQ PT DQ prefech Size
_aq_droppt_batch_size 5 Drop PT batch Size
_aq_init_shards 5 Minimum enqueue shards per queue at an instance
_aq_ipc_max_slave 10 maximum number of slaves for knlpipcm
_aq_latency_absolute_threshold 100 Absolute threshold greater than average latency
_aq_latency_relative_threshold 30 Relative threshold of average latency
_aq_lb_cycle 120 Time(seconds) between consecutive AQ load balancing efforts
_aq_lb_stats_collect_cycle 120 Time(seconds) between consecutive AQ load statistics collection
_aq_lookback_size 30 AQ PT Look Back Size
_aq_max_scan_delay 1500 Maximum allowable scan delay for AQ indexes and IOTs
_aq_pt_processes 15 Partition background processes
_aq_pt_shrink_frequency 30 PT shrink window Size
_aq_pt_statistics_window 30 PT statistics sample window Size
_aq_qt_prefetch_Size 5 AQ PT QT prefech Size
_aq_shard_bitmap_child_latches 32 Bitmap child latches
_aq_shard_child_latches 512 Shard child latches
_aq_shard_prty_latches 16 Shard priority child latches
_aq_shard_retry_child_latches 32 Retry child latches
_aq_shard_sub_child_Elem_latches 1024 Subscriber Element child latches
_aq_shard_sub_child_latches 512 Subscriber child latches
_aq_shard_txn_child_latches 128 Txn child latches
_aq_stop_backgrounds FALSE Stop all AQ background processes
_aq_streaming_threshold 10485760 large payload threshold size
_aq_subshard_Size 20000 Sub Shard Size
_aq_subshards_per_dqpartition 1 SubShards Per Deq Partition
_aq_subshards_per_qpartition 1 SubShards Per Q Partition
_aq_tm_deqcountinterval 0 dequeue count interval for Time Managers to cleanup DEQ IOT BLOCKS
_aq_tm_scanlimit 0 scan limit for Time Managers to clean up IOT
_aq_tm_statistics_duration 0 statistics collection window duration
_aq_truncpt_batch_size 1 Trunc PT batch Size
_aq_x_msg_size 32768 AQ cross single message buffer size
_aqsharded_cache_limit 0 Limit for cached enqueue/dequeue operations
 
   
 
 
ANSI Compliance
BLANK_TRIMMING

Default: FALSE
Specifies the data assignment semantics of character datatypes. Allows the data assignment of a source character string or variable to a destination character column or variable even though the source length is longer than the destination length. In this case, however, the additional length over the destination length is all blanks.
Range of values: {TRUE | FALSE}
blank_trimming=TRUE
 
Archive Logging
ARCHIVE_LAG_TARGET

Default: 0
Limits the amount of data that can be lost and effectively increases the availability of the standby database by forcing a log switch after the specified amount of time elapses.

The ARCHIVE_LAG_TARGET initialization parameter causes Oracle to examine an instance's current online redo log periodically. If the following conditions are met the instance will switch the log:
  • The current log was created prior to n seconds ago, and the estimated archival time for the current log is m seconds (proportional to the number of redo blocks used in the current log), where n + m exceeds the value of the ARCHIVE_LAG_TARGET initialization parameter.
  • The current log contains redo records.
In an Oracle Real Application Clusters environment, the instance also kicks other threads into switching and archiving logs if they are falling behind. This can be particularly useful when one instance in the cluster is less active than the other instances.

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

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

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

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

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

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

Default: NULL
Similar to LOG_ARCHIVE_DEST specifies a second archive destination: the duplex archive destination. This duplex archive destination can be either a must-succeed or a best-effort archive destination, depending on how many archive destinations must succeed (as specified in the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter).

The default setting of a null string ("") or (' ') indicates that a duplex archive destination does not exist.
log_archive_dest='/app/oracle/product/flash_recovery_area/arch'
log_archive_duplex_dest='/u02/arch'
LOG_ARCHIVE_FORMAT

Default: Operating system dependent. For OEL %t_%s_%r.dbf
Use a text string and variables to specify the default filename format when archiving redo log files. The string generated from this format is appended to the string specified in the LOG_ARCHIVE_DEST parameter.

The following variables can be used in the format:
%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID ensures unique names are constructed for the archived log files across multiple DB incarnations

Using uppercase letters for the variables (for example, %S) causes the value to be fixed length and padded to the left with zeros. An example of specifying the archive redo log filename format follows:
log_archive_format=%t+%s+%r.arc
LOG_ARCHIVE_LOCAL_FIRST Deprecated
LOG_ARCHIVE_MAX_PROCESSES

Default: 4
Specifies the number of archiver background processes (ARC0 through ARCn) Oracle initially invokes. If the LOG_ARCHIVE_START initialization parameter has the value true, then this value is evaluated at instance startup though LOG_ARCHIVE_START initialization parameter was deprecated as of 10gR1. Otherwise, this parameter is evaluated when the archiver process is first invoked by SQL*Plus or SQL syntax.
Range of values:
{1 .. 40}
log_archive_max_processes=6
LOG_ARCHIVE_MIN_SUCCEED_DEST

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

Default 0
Value Description
0 Disable archivelog tracing
1 Track archival of redo log file
2 Track archival status of each archivelog destination
4 Track archival operational phase
8 Track archivelog destination activity
16 Track detailed archivelog destination activity
32 Track archivelog destination parameter modifications
64 Track ARCn process state activity
128 Track FAL (fetch archived log) server related activities
256 Track RFS Logical Client
512 Track LGWR redo shipping network activity
1024 Track RFS Physical Client
2048 Track RFS/ARCn Ping Heartbeat
4096 Track Real Time Apply
8192 Track Redo Apply (Media Recovery or Physical Standby)
log_archive_trace=128
 
ASM
ASM_DISKGROUPS

Default: NULL
Specifies a list of names of disk groups to be mounted by an Automatic Storage Management instance at instance startup or when an ALTER DISKGROUP ALL MOUNT statement is issued. Range of values Comma-separated list of strings, of up to 30 characters in length.
asm_diskgroups=dgroupA, dgroupB
ASM_DISKSTRING

Default: NULL
An operating system-dependent value used by Automatic Storage Management to limit the set of disks considered for discovery. Value is a comma delimited string of disks.
asm_diskstring='dev/rdsk/*s2, /dev/rdsk/c1*'
ASM_POWER_LIMIT

Default: 1
Specifies the maximum power on an Automatic Storage Management instance for disk rebalancing.
Range of values
 {1 to 11}
asm_power_limit=5
ASM_PREFERRED_READ_FAILURE_ GROUPS

Default: NULL
Specifies the failure groups that contain preferred read disks. Preferred disks are instance specific.
TBD
_asm_access

ASM File access mechanism
Default: AUTO
 
_asm_acd_chunks

initial ACD chunks created
Default: 1
 
_asm_admin_with_sysdba

does the sysdba role have administrative privileges on ASM?
FALSE
 
_asm_allow_appliance_dropdisk_noforce

allow DROP DISK/FAILUREGROUP NOFORCE on ASM Appliances
FALSE
 
  _asm_allow_dangerous_unprotected_volumes FALSE Disable checking for unprotected volumes in mirrored disk groups
 
  _asm_allow_lvm_resilvering TRUE Enable disk resilvering for external redundancy
 
  _asm_allow_only_raw_disks TRUE Discovery only raw devices
 
  _asm_allow_small_memory_target FALSE Allow a small memory_target for ASM instances
 
  _asm_allow_system_alias_rename FALSE if system alias renaming is allowed
 
  _asm_allow_unsafe_reconnect TRUE attempt unsafe reconnect to ASM
 
  _asm_allowdegeneratemounts FALSE Allow force-mounts of DGs w/o proper quorum
 
  _asm_appliance_config_file Appliance configuration file name
 
  _asm_appliance_ignore_oak FALSE Ignore OAK appliance library
 
  _asm_appliance_slot_from_path FALSE Get appliance disk slot from disk path
 
  _asm_ausize 1048576 allocation unit size
 
  _asm_automatic_rezone TRUE automatically rebalance free space across zones
 
  _asm_avoid_pst_scans TRUE Avoid PST Scans
 
  _asm_blksize 4096 metadata block size
 
  _asm_check_for_misbehaving_cf_clients FALSE check for misbehaving CF-holding clients
 
  _asm_compatibility 10.1 default ASM compatibility level
 
  _asm_dba_batch 500000 ASM Disk Based Allocation Max Batch Size
 
  _asm_dba_spcchk_thld 100000 ASM Disk Based Allocation Space Check Threshold
 
  _asm_dba_threshold 0 ASM Disk Based Allocation Threshold
 
  _asm_dbmsdg_nohdrchk FALSE dbms_diskgroup.checkfile does not check block headers
 
  _asm_diag_dead_clients 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
 
  _asm_partner_target_fg_rel 4 target maximum number of failure group relationships for repartnering
 
  _asm_primary_load 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
 
  _asm_stripesize 131072 ASM file stripe size
 
  _asm_stripewidth 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
 
  _asm_xrov_nvios 4 Specify number of VIO processes
 
  _asm_xrov_rsnmod 1 Specify 'reason' mode
 
_asm_xrov_single

 
TRUE Enable single issues of IOs
 
_asmsid

ASM instance id
Default: asm
TBD
 
Auditing
AUDIT_FILE_DEST

Default:
$ORACLE_BASE/admin/ORACLE_SID/adump
The operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set to os.
audit_file_dest=/app/oracle/product/admin/orabase/adump
AUDIT_SYS_OPERATIONS

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

Default: NULL
Allows SYS and standard OS audit records to be written to the system audit log using the SYSLOG utility. If you use this parameter, it is best to assign a file corresponding to every combination of facility and priority (especially KERN.EMERG) in syslog.conf. Sometimes these are assigned to print to the console in the default syslog.conf file. This can become annoying and will be useless as audit logs. Also, if you use this parameter, it is best to set the maximum length of syslog messages in the system to 512 bytes. If AUDIT_SYSLOG_LEVEL is set and SYS auditing is enabled (AUDIT_SYS_OPERATIONS = TRUE), then SYS audit records are written to the system audit log. If AUDIT_SYSLOG_LEVEL is set and standard audit records are being sent to the operating system (AUDIT_TRAIL = os), then standard audit records are written to the system audit log.

AUDIT_SYSLOG_LEVEL = 'facility_clause.priority_clause'

Facility Clause Options: { USER | LOCAL[0 | 1 | 2 | 3 | 4 | 5 | 6 | 7] | SYSLOG | DAEMON | KERN | MAIL | AUTH | LPR | NEWS | UUCP | CRON }
Priority Options: { NOTICE | INFO | DEBUG | WARNING | ERR | CRIT | ALERT | EMERG }
audit_syslog_level='KERN.EMERG'
AUDIT_TRAIL

Default: DB
Enables or disables database auditing.
Syntax: AUDIT_TRAIL = {DB | OS | NONE | TRUE | FALSE | DB_EXTENDED}
audit_trail='DB'
 
Backup and Restore
BACKUP_TAPE_IO_SLAVES

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

Default: 7
specifies the minimum number of days before a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed.
Range of values: 0 to 365 (days)
ALTER SYSTEM SET control_file_record_keep_time=30 SCOPE=BOTH;
FAST_START_IO_TARGET Deprecated in favor of FAST_START_MTTR_TARGET
FAST_START_MTTR_TARGET Specify the number of seconds the database takes to perform crash recovery of a single instance. The value Is overridden by FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL so do not set them when using this parameter. The estimated MTTR can be found in v$intance_recovery.
Range of values: 1 to 3600 (seconds)
fast_start_mttr_target=15
RECOVERY_PARALLELISM

Default: System-determined parallel recovery
Specifies the number of processes to participate in instance or crash recovery. To force serial crash and instance recovery, set the parameter to 0 or 1. 0 disables parallel instance and crash recovery on a system that has multiple CPUs. An alert log displays the degree of parallelism that was chosen when the database instance/recovery starts.
recovery_parallelism=0
TAPE_ASYNCH_IO

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

Default: 10
Specifies the maximum number of BFILEs that can be opened in any session. Once this number is reached, subsequent attempts to open more files in the session by using DBMS_LOB.FILEOPEN() or OCILobFileOpen() will fail.
session_max_open_files=6
 
Buffer Cache and I/O
DB_nK_CACHE_SIZE

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

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

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

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

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

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

Default: 8
One of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan (applies to full table scans and index fast full scans, so non-random I/O.). The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

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

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

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

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

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

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

Default: 1 or CPU_COUNT / 8, whichever is greater
Specifies the initial number of database writer processes for an instance.
Range of values: 1 to 36
db_writer_processes=2
DBWR_IO_SLAVES

Default: 0
Relevant only on systems with only one database writer process (DBW0). It specifies the number of I/O server processes used by the DBW0 process. The DBW0 process and its server processes always write to disk. By default, the value is 0 and I/O server processes are not used.

If you set DBWR_IO_SLAVES to a nonzero value, the number of I/O server processes used by the ARCH and LGWR processes is set to 4. However, the number of I/O server processes used by Recovery Manager is set to 4 only if asynchronous I/O is disabled (either your platform does not support asynchronous I/O or disk_asynch_io is set to false).

Typically, I/O server processes are used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or that implement it inefficiently. However, you can use I/O server processes even when asynchronous I/O is being used. In that case the I/O server processes will use asynchronous I/O.

I/O server processes are also useful in database environments with very large I/O throughput, even if asynchronous I/O is enabled.

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

Default: TRUE
Controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans. Oracle recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.

If you set DISK_ASYNCH_IO to false, then you should also set DBWR_IO_SLAVES to a value other than its default of zero in order to simulate asynchronous I/O.

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

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

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

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

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

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

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

Default: Exact
FORCE: Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
SIMILAR: Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
EXACT: Only allows statements with identical text to share the same cursor.

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

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

Default: 50
Specifies the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.
Range of values: 0 to an operating system dependent limit
session_cached_cursors=220
 
Data Guard
DG_BROKER_CONFIG_FILE1
DG_BROKER_CONFIG_FILE2

Default: Operating system dependent
Specifies the names for the Data Guard broker configuration files. Every database that is part of a Data Guard broker configuration has two broker configuration files, which contain entries that describe the state and properties of the configuration (such as the sites and databases that are part of the configuration, the roles and properties of each of the databases, and the state of each of the elements of the configuration). Two files are provided so as to always maintain the last known good state of the configuration.

If DG_BROKER_CONFIG_FILEn is not explicitly defined, then it is set to an operating system-specific default value at instance startup. The parameter can only be altered when the DMON (Data Guard broker) process is not running.
Range of values per "n" listed: One filename
dg_broker_config_file=$ORACLE_BASE/admin/orabasee/dr1db_orabase.dat
dg_broker_config_file=$ORACLE_BASE/admin/orabase/dr2db_orabase.dat
DG_BROKER_START

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

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

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

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

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

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

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

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

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

Default: MANUAL
Enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database. STANDBY_FILE_MANAGEMENT is only applicable on physical standbys.
Range of values: {MANUAL | AUTO}
standby_file_management=MANUAL
 
Database / Instance / Identification
DB_DOMAIN

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

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

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

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

Default: FALSE
Enables or disables the writing of DDL statements to the alert log
enable_ddl_logging=TRUE
EVENT

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

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

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

Default: TYPICAL
Specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.
Valid values are: {ALL | TYPICAL | BASIC}
statistics_level=ALL
TIMED_OS_STATISTICS

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

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

Default: FALSE
Controls tracing of the execution history, or code path, of Oracle. Oracle Support Services uses this information for debugging.
Valid values are: {TRUE | FALSE}
trace_enable=FALSE
TRACEFILE_IDENTIFIER

No default value is set
Specifies a custom identifier that becomes part of the Oracle Trace file name. A custom identifier is used to identify a trace file simply from its name and without having to open it or view its contents.
tracefile_identifier='test_plan1';
USER_DUMP_DEST Deprecated
  _ash_compression_enable TRUE To enable or disable string compression in ASH
 
  _ash_disk_filter_ratio 10 Ratio of the number of in-memory samples to the number of samples actually written to disk
 
  _ash_disk_write_enable TRUE To enable or disable Active Session History flushing
 
  _ash_dummy_test_param 0 Oracle internal dummy ASH parameter used ONLY for testing!
 
  _ash_eflush_trigger 66 The percentage above which if the in-memory ASH is full the emergency flusher will be triggered
 
  _ash_enable TRUE To enable or disable Active Session sampling and flushing
 
  _ash_min_mmnl_dump 90 Minimum Time interval passed to consider MMNL Dump
 
  _ash_sample_all FALSE To enable or disable sampling every connected session including ones waiting for idle waits
 
  _ash_sampling_interval 1000 Time interval between two successive Active Session samples in millisecs
 
  _ash_size 1048618 To set the size of the in-memory Active Session History buffers
 
 
Distributed Replication
COMMIT_POINT_STRENGTH

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

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

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

Default: TRUE
Enables or disables automatic self-registration of Heterogeneous Services (HS) agents. When enabled, information is uploaded into the server's data dictionary to describe a previously unknown agent class or a new agent version.
Range of values: {TRUE | FALSE}
hs_autoregister=FALSE
OPEN_LINKS

Default: 4
Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.
Range of values: {0 - 255}
open_links=10
OPEN_LINKS_PER_INSTANCE

Default: 4
Specifies the maximum number of migratable open connections globally for each database instance. XA transactions use migratable open connections so that the connections are cached after a transaction is committed. Another transaction can use the connection, provided the user who created the connection is the same as the user who owns the transaction.
Range of values: {0 - 255}
open_links_per_instance=5
REPLICATION_DEPENDENCY_TRACKING

Default: TRUE
Enables or disables dependency tracking for read/write operations to the database. Dependency tracking is essential for propagating changes in a replicated environment in parallel.
Range of values: {TRUE | FALSE}
replication_dependency_tracking=FALSE
 
Exadata Storage Server
CELL_OFFLOAD_COMPACTION

Cell packet compaction strategy
Default: ADAPTIVE
?
CELL_OFFLOAD_DECRYPTION

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

additional cell offload parameters
Default: Not set
TBD
CELL_OFFLOAD_PLAN_DISPLAY

cell offload explain plan display
Default: AUTO
cell_offload_plan_display=MANUAL
CELL_OFFLOAD_PROCESSING

enable SQL processing offload to cells
Default: TRUE
cell_offload_processing=FALSE
CELL_OFFLOADGROUP_NAME

set the offload group name
Default: Not set
?
_ALLOW_CELL_SMART_SCAN_ATTR

allow checking smart_scan_capable Attr
_allow_cell_smart_scan_attr=<FALSE | TRUE>
_allow_cell_smart_scan_attr=TRUE
_CELL_FAST_FILE_CREATE

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

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

cell file format chunk size in MB
Default is 0
_cell_file_format_chunk_size=8192
_CELL_INDEX_SCAN_ENABLED

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

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

enable offload of expressions underlying virtual columns to cells
_cell_materialize_virtual_columns=<FALSE | TRUE>
_cell_materialize_virtual_columns=TRUE
_CELL_OBJECT_EXPIRATION_HOURS

flashcache object expiration timeout
Default is 24
_cell_object_expiration_hours=12
_CELL_OFFLOAD_BACKUP_COMPRESSION

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

specifies capability table to load
Default is 1
_cell_offload_capabilities_enabled=1
_CELL_OFFLOAD_COMPLEX_PROCESSING

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

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

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

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

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

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

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

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

cell storage index mode
_cell_storidx_minmax_enabled=<FALSE | TRUE>
_cell_storidx_minmax_enabled=FALSE
_CELL_STORIDX_MODE

cell storage index mode
Default is EVA
_cell_storidx_mode='EVA'
_CONTROLFILE_CELL_FLASH_CACHING

flash cache hint for control file accesses
Default is 3
_controlfile_cell_flash_caching=3
_DB_CHECK_CELL_HINTS

undocumented
_db_check_cell_hints=<FALSE | TRUE>
_db_check_cell_hints=TRUE
_DISABLE_CELL_OPTIMIZED_BACKUPS

disable cell optimized backups
_disable_cell_optimized_backups=<FALSE | TRUE>
_disable_cell_optimized_backups=FALSE
_KCFIS_CELL_PASSTHRU_DATAONLY

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

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

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

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

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

Enable usage of cellsrv flash cache for kept objects
_kcfis_kept_in_cellfc_enabled=<FALSE | TRUE>
 
_KCFIS_NONKEPT_IN_CELLFC_ENABLED

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

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

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

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

Default: 200
Specifies the maximum number of database files that can be opened for this database.
Minimum: the largest among the absolute file numbers of the datafiles in the database
Maximum: operating system-dependent
db_files=100
DB_RECOVERY_FILE_DEST Specifies the default location for the flash recovery area.
Syntax DB_RECOVERY_FILE_DEST = {DIRECTORY | DISK GROUP}
db_recovery_file_dest='c:\oracle\product\flash_recovery_area'
DB_RECOVERY_FILE_DEST_SIZE Specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the fast recovery area
Syntax DB_RECOVERY_FILE_DEST_SIZE = integer [K | M | G]
db_recovery_file_dest_size=4G
FILE_MAPPING Deprecated but still supported for backward compatibility
IFILE
No Default
Embed the named parameter file in the init.ora as it is read.
ifile=?/dbs/proda_dg.ora
SPFILE

No Default
The value of this parameter is the name of the current server parameter file (SPFILE) in use. This parameter can be defined in a client side PFILE to indicate the name of the server parameter file to use.
spfile=/app/oracle/product/11.1.0/db_1/dbs/spfileorabase.ora
UTL_FILE_DIR

No Default
Alllow the specification of one or more directories that Oracle should use for PL/SQL file I/O. If you are specifying multiple directories, you must repeat the UTL_FILE_DIR parameter for each directory on separate lines of the initialization parameter file. This parameter should not be used in favor of DIRECTORY objects.
utl_file_dir=*
utl_file_dir=/stage
 
Flashback
DB_FLASHBACK_RETENTION_TARGET

Default: 1440
Specifies the upper limit (in minutes) on how far back in time the database may be flashed back.
Range of values 0 to 232 - 1 (max value represented by 32 bits)
db_flashback_retention_target=2880
RECYCLEBIN
Default: ON
Specifies if the recyclebin holds dropped tables and associated indexes.
Range of values: {ON | OFF}
recyclebin=ON
 
I/O
FILEIO_NETWORK_ADAPTERS Specifies a list of network adapters that can be used to access the disk storage. On platforms where the database files reside in network attached storage, this parameter provides the storage access library the list of network adapters that can be used to access the storage.
fileio_metwork_adapters=netapp1.mlib.org
FILESYSTEMIO_OPTIONS

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

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

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

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

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

Default: 1048576 for W2K & Linux
For systems containing bitmap indexes: Specifies the amount of memory Oracle uses to merge bitmaps retrieved from a range scan of the index. The default value is 1 MB. A larger value usually improves performance, because the bitmap segments must be sorted before being merged into a single bitmap. Range of values Operating system-dependent
bitmap_merge_area_size=1048576
BUFFER_POOL_KEEP Deprecated
BUFFER_POOL_RECYCLE Deprecated
CREATE_BITMAP_AREA_SIZE

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

Default: Derived: 2 * SORT_AREA_SIZE
Relevant to parallel execution operations and to the query portion of DML or DDL statements. It specifies the maximum amount of memory, in bytes, to be used for hash joins.
hash_area_size=2097152
HI_SHARED_MEMORY_ADDRESS
Default: 0
Specifies the starting address at runtime of the SGA. It is ignored on platforms that specify the SGA's starting address at link time.
hi_shared_memory_address=0
LARGE_POOL_SIZE Specifies (in bytes) the size of the large pool allocation heap. The large pool allocation heap is used in shared server systems for session memory, by parallel execution for message buffers, and by backup processes for disk I/O buffers. Parallel execution allocates buffers out of the large pool only when SGA_TARGET is set.
large_pool_size=0
LOCK_SGA

Default: False
Locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space. This parameter is ignored on platforms that do not support it.
lock_sga=FALSE
LOG_BUFFER Specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers. The LGWR process writes redo log entries from the log buffer to a redo log file.
Range of values: 2 MB to 64 MB (32-bit O/S), 2 MB to 256 MB (64-bit O/S)
log_buffer=4964352
MEMORY_MAX_TARGET

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

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

Default: 10
The object cache is a memory block on the client that allows applications to store entire objects and to navigate among them without round trips to the server. OBJECT_CACHE_MAX_SIZE_PERCENT specifies the percentage of the optimal cache size that the session object cache can grow past the optimal size. The maximum size is equal to the optimal size plus the product of this percentage and the optimal size. When the cache size exceeds this maximum size, the system will attempt to shrink the cache to the optimal size.
object_cache_max_size_percent=10
OBJECT_CACHE_OPTIMAL_SIZE
Default: 100K
Specifies (in bytes) the size to which the session object cache is reduced when the size of the cache exceeds the maximum size.
object_cache_optimal_size=102400
OLAP_PAGE_POOL_SIZE
Default: 0
Specifies (in bytes) the size of the OLAP page pool.
olap_page_pool_size=0
PGA_AGGREGATE_TARGET
Default: 10 MB or 20% of the size of the SGA, whichever is greater
Specifies the target aggregate PGA memory available to all server processes attached to the instance.
Minimum: 10 MB, Maximum: 4096 GB - 1
pga_aggregate_target=0
PRE_PAGE_SGA

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

Default: 0
Specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:
* Buffer cache (DB_CACHE_SIZE)
* Shared pool (SHARED_POOL_SIZE)
* Large pool (LARGE_POOL_SIZE)
* Java pool (JAVA_POOL_SIZE)
* Streams pool (STREAMS_POOL_SIZE)


If these automatically tuned memory pools are set to nonzero values, then those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:
* Log buffer
* Other buffer caches, such as KEEP, RECYCLE, and other block sizes
* Fixed SGA and other internal allocations


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

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

This parameter to specifies the entire address on 32-bit platforms and to specify the low-order 32 bits of a 64-bit address on 64-bit platforms. Use HI_SHARED_MEMORY_ADDRESS to specify the high-order 32 bits of a 64-bit address on 64-bit platforms. If both parameters are 0 or unspecified, the SGA address defaults to a platform-specific location.
shared_memory_address=0
SHARED_POOL_RESERVED_SIZE

Default: 5% of the value of SHARED_POOL_SIZE
Specifies (in bytes) the shared pool space that is reserved for large contiguous requests for shared pool memory.
share_pool_reservde_size=16567500
SHARED_POOL_SIZE

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

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

Default: 65536
Specifies (in bytes) the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.
sort_area_size=1048576
_assm_default

ASSM default
TRUE
 
_assm_force_fetchmeta

enable metadata block fetching in ASSM segment scan
FALSE
 
_assm_high_gsp_threshold

number of blocks rejected before growing segment
Default: 11024
 
_assm_low_gsp_threshold

number of blocks rejected before collecting stats
Default: 10000
 
_assm_test_force_rej

assm min number of blocks to cbk-reject
Default: 0
 
_assm_test_force_rej2

assm min number of blocks to kdt-reject
Default: 0
 
_assm_test_reentrant_gsp

assm test reentrant gsp
FALSE
 
 
Miscellaneous
COMPATIBLE Allows you to use a new release of Oracle, while at the same time guaranteeing backward compatibility with an earlier release.
compatible=11.2.0.0.0
CONTROL_MANAGEMENT_PACK _ACCESS

EE Default: DIAGNOSTIC+TUNING
Specifies which of the Server Manageability Packs should be active.
Range of values: {NONE | DIAGNOSTIC | DIAGNOSTIC+TUNING}
control_management_pack_access=DIAGNOSTIC+TUNING
CPU_COUNT Specifies the number of CPUs available to Oracle. Range of values 0 to unlimited.
cpu_count=2
DB_UNIQUE_NAME Specifies a globally unique name for the database. Databases with the same DB_NAME within the same DB_DOMAIN. Every database's DB_UNIQUE_NAME must be unique within the enterprise.
Syntax DB_UNIQUE_NAME = database_unique_name
db_unique_name=orabase
DB_UNRECOVERABLE_SCN_TRACKING When TRUE turns off control file writes to update fields that track the highest unrecoverable SCN and Timing during a NOLOGGING Direct Path operation.
Syntax DB_UNRECOVERABLE_SCN_TRACKING = [TRUE | FALSE]
db_unique_name=orabase
DDL_LOCK_TIMEOUT

Default: 0
Specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.
ddl_lock_timeout=0
FIXED_DATE Enables you to set a constant date that SYSDATE will always return instead of the current date. To undo a fixed date setting, specify FIXED_DATE=NONE. This parameter is useful primarily for testing. The value can be in the format shown above or in the default Oracle date format, without a time.
Format: FIXED_DATE = [YYYY-MM-DD-HH24:MI:SS (or the default date format) | NONE]
fixed_date=NONE
JOB_QUEUE_PROCESSES

Default: 1000
Specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999). Replication uses job queues for data refreshes. Advanced queuing uses job queues for message propagation. You can create user job requests through the DBMS_JOB package.
job_queue_processes=1000
LOG_CHECKPOINT_INTERVAL

Default: 0
Specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical o/s blocks, not database blocks.
Range of values: {0 to 2^31 - 1}
log_checkpoint_interval=0
LOG_CHECKPOINT_TIMEOUT

Default: 1800
Specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty for more than integer seconds.
Range of values {0 to 2^31 - 1}
log_checkpoint_timeout=1800
LOG_CHECKPOINTS_TO_ALERT

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

Default: 100
Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters.
processes=1250
RDBMS_SERVER_DN

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

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

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

No Default
Specifies a network name that resolves to an address or address list of Oracle Net remote listeners (that is, listeners that are not running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repositories.
Syntax: remote_listener=<network_name>
TBD
RESOURCE_LIMIT

Default: FALSE
Determines whether resource limits are enforced in database profiles.
resource_limit=TRUE
RESOURCE_MANAGER_CPU_ALLOCATION Deprecated
RESOURCE_MANAGER_PLAN

No Default
Specifies the top-level resource plan to use for an instance. The resource manager will load this top-level plan along with all its descendants (subplans, directives, and consumer groups). If you do not specify this parameter, the resource manager is off by default.
resource_manager_plan='SCHEDULER[0x2C58]:DEFAULT_MAINTENANCE_PLAN'
RESUMABLE_TIMEOUTS

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

Default: No specified as UNDO is used
Allocates one or more rollback segments by name to this instance. If you set this parameter, the instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance (calculated as TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT).
Range of Values: {Any rollback segment names listed in DBA_ROLLBACK_SEGS except SYSTEM}
TBD
SERIAL_REUSE Deprecated
SERVICE_NAMES

Default:
DB_UNIQUE_NAME.DB_DOMAIN if defined
Specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. When a client requests a service, the listener determines which instances offer the requested service and routes the client to the appropriate instance.
service_name=orabase
SESSIONS

Default: Derived: (1.1 * PROCESSES) + 5
Specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.
sessions=200
SKIP_UNUSABLE_INDEXES

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

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

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

Default: FALSE
The SQL standard specifies that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in a WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements.
sql92_security=FALSE
SQL_TRACE Deprecated
SQLTUNE_CATEGORY

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

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

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

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

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

Default: If SHARED_SERVERS is greater than 0, then DISPATCHERS defaults to '(PROTOCOL=tcp)', causing 1 TCP/IP dispatcher to be created.
Configures dispatcher processes in the shared server architecture. The parsing software supports a name-value syntax to enable the specification of attributes in a position-independent, case-insensitive manner.

Range of values:
dispatch_clause::= (PROTOCOL = protocol) | (ADDRESS = address) | (DESCRIPTION = description )
options_clause::=(DISPATCHERS = integer | SESSIONS = integer | CONNECTIONS = integer |
TICKS = seconds | POOL = {1 | ON | YES | TRUE | BOTH | ({IN | OUT} = ticks) | 0 | OFF | NO | FALSE | ticks} |
MULTIPLEX = {1 | ON | YES | TRUE | 0 | OFF | NO | FALSE | BOTH | IN | OUT} | LISTENER = tnsname | SERVICE = service | INDEX = integer)
dispatchers='(PROTOCOL=TCP) (SERVICE=orabaseXDB)'
dispatchers='(PROTOCOL=TCP)(DISPATCHERS=3)'
LICENSE_MAX_SESSIONS

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

Default: 0
Specifies the maximum number of users you can create in the database. When you reach this limit, you cannot create more users. You can, however, increase the limit. Do not enable both concurrent usage (session) licensing and user licensing. Set either LICENSE_MAX_SESSIONS or LICENSE_MAX_USERS to zero.
license_max_users=0
LICENSE_SESSIONS_WARNING

Default: 0
Specifies a warning limit on the number of concurrent user sessions. When this limit is reached, additional users can connect, but Oracle writes a message in the alert file for each new connection. Users with RESTRICTED SESSION privilege who connect after the limit is reached receive a warning message stating that the system is nearing its maximum capacity.
license_sessions_warning=0
LISTENER_NETWORKS

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

Default: (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521))
Specifies a network name that resolves to an address or address list of Oracle Net local listeners (that is, listeners that are running on the same machine as this instance). The address or address list is specified in the TNSNAMES.ORA file or other address repository as configured for your system.
Syntax: LOCAL_LISTENER = <network_name>
local_listener=(PROTOCOL=TCP)(HOST=bigdog.mlog.org)(PORT=1521))
MAX_DISPATCHERS

No Default
Specifies the maximum number of dispatcher processes allowed to be running simultaneously. It can be overridden by the DISPATCHERS parameter and is maintained for backward compatibility with older releases.
max_dispatchers=10
MAX_SHARED_SERVERS

No Default
Specifies the maximum number of shared server processes allowed to be running simultaneously. Setting this parameter enables you to reserve process slots for other processes, such as dedicated servers.
max_shared_servers=10
SHARED_SERVER_SESSIONS

No Default
Specifies the number of shared server sessions to allow. Setting this parameter enables reserving user sessions for dedicated servers.
shared_server_sessions=60
SHARED_SERVERS

Default: 0 (not enabled)
Specifies the number of server processes that you want to create when an instance is started. If system load decreases, then this minimum number of servers is maintained. Therefore, you should take care not to set SHARED_SERVERS too high at system startup.
shared_servers=10
 
NLS Parameters
NLS_CALENDAR

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

Default: BINARY
Specifies which calendar system Oracle uses
Range of values: {BINARY | LINGUISTIC | ANSI}
nls_comp=BINARY
NLS_CURRENCY Specifies the string to use as the local currency symbol for the L number format element. The default value of this parameter is determined by NLS_TERRITORY.
Range of values: Any valid character string, with a maximum of 10 bytes (not including null)
nls_currency=$
NLS_DATE_FORMAT Specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY.
nls_date_format="MM/DD/YYYY"
NLS_DATE_LANGUAGE Specifies the language to use for the spelling of day and month names and date abbreviations (a.m., p.m., AD, BC) returned by the TO_DATE and TO_CHAR functions.
nls_date_language=AMERICAN
NLS_DUAL_CURRENCY Specifies the dual currency symbol (such as "Euro") for the territory. The default is the dual currency symbol defined in the territory of your current language environment.
TBD
NLS_ISO_CURRENCY Specifies the string to use as the international currency symbol for the C number format element.
TBD
NLS_LANGUAGE Specifies the default language of the database. This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism. This parameter also determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT.
nls_language=AMERICAN
NLS_LENGTH_SEMANTICS Enables creationof CHAR and VARCHAR2 columns using either byte or character length semantics. Existing columns are not affected.
nls_length_semantics=BYTE
NLS_NCHAR_CONV_EXCP

Default: FALSE
Determines whether an error is reported when there is data loss during an implicit or explicit character type conversion between NCHAR/NVARCHAR and CHAR/VARCHAR2. The default value results in no error being reported.
nls_nchar_conv_excp=TRUE
NLS_NUMERIC_CHARACTERS Specifies the characters to use as the group separator and decimal character. It overrides those characters defined implicitly by NLS_TERRITORY. The group separator separates integer groups (that is, thousands, millions, billions, and so on). The decimal separates the integer portion of a number from the decimal portion.
nls_numeric_characters=", "
NLS_SORT Specifies the collating sequence for ORDER BY queries.
Range of values: {BINARY | linguistic_definition}
nls_sort=BINARY
NLS_TERRITORY Specifies the name of the territory whose conventions are to be followed for day and week numbering.
nls_territory=AMERICA
NLS_TIMESTAMP_FORMAT Defines the default timestamp format to use with the TO_CHAR and TO_TIMESTAMP functions.
nls_timestamp_format='YYYY-MM-DD HH:MI:SS.FF'
NLS_TIMESTAMP_TZ_FORMAT Defines the default timestamp with time zone format to use with the TO_CHAR and TO_TIMESTAMP_TZfunctions.
nls_timestamp_tz_format='YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
 
Optimizer
CREATE_STORED_OUTLINES

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

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

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

Default: 11.2.0.2
Acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.
optimizer_features_enable=11.1.0.7
OPTIMIZER_INDEX_CACHING A point that is often overlooked with optimizer_index_caching is that it supposed to apply only for index costs during nested loops (although it does seem to be applied for inlist iterators).

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

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

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

by: Jonathan Lewis / 4 Nov 2004

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

by: Niall Litchfield
optimizer_index_caching=20
OPTIMIZER_INDEX_COST_ADJ

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

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

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

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

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

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

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

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

Default: ENFORCED
Determines the degree to which Oracle must enforce query rewriting. At the safest level, Oracle does not use query rewrite transformations that rely on unenforced relationships.
Range of values: {ENFORCED |  STALE _TOLERATED | TRUSTED}
query_rewrite_integrity='TRUSTED'
_ALWAYS_ANTI_JOIN

always use this method for anti-join when possible
CHOOSE
_always_anti_join='CHOOSE'
_ALWAYS_SEMI_JOIN

always use this method for semi-join when possible
CHOOSE
_always_semi_join='CHOOSE'
_ALWAYS_STAR_TRANSFORMATION

always favor use of star transformation
FALSE
 
_ALWAYS_VECTOR_TRANSFORMATION

always favor use of vector transformation
FALSE
 
 
PL/SQL
PLSCOPE_SETTINGS

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

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

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

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

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

equivalent to:
ALTER SYSTEM SET plsql_code_type = 'NATIVE' SCOPE=BOTH;
PLSQL_DEBUG

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

Default: DISABLE:ALL
Enables or disables feedback on Critical, Informational, and Performance warnings during PL/SQL object compilation.
plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
 
Real Application Clusters
ACTIVE_INSTANCE_COUNT Deprecated
CLUSTER_DATABASE

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

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

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

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

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

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

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

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

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

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

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

Default: TRUE
When set to true, enables an adaptive algorithm designed to improve performance in multiuser environments that use parallel execution. The algorithm automatically reduces the requested degree of parallelism based on the system load at query startup time. The effective degree of parallelism is based on the default degree of parallelism, or the degree from the table or hints, divided by a reduction factor.
Range of values: {TRUE | FALSE}
parallel_adaptive_multi_user=TRUE
PARALLEL_DEGREE_LIMIT

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

new in 11.2

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

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

Default: Operating System and Compatible Parameter value dependent
The size of messages used for parallel execution (formerly parallel query, PDML, Parallel Recovery, replication).
Range of values : 2148 to 32768
parallel_execution_message_size=16384
PARALLEL_FORCE_LOCAL

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

No Default
PARALLEL_INSTANCE_GROUP = group_name

PARALLEL_INSTANCE_GROUP is a Real Application Clusters parameter that you can specify in parallel mode only. Used in conjunction with the INSTANCE_GROUPS parameter, it lets you restrict parallel query operations to a limited number of instances.

This parameter identifies the parallel instance group Oracle will use for spawning parallel execution processes. Parallel operations will spawn parallel execution processes only on instances that specify a matching group in their INSTANCE_GROUPS parameter.

If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a group that does not exist, then the operation runs serially. No parallelism is used.
parallel_instance_group='SYS$USERS'
PARALLEL_IO_CAP_ENABLED Deprecated
PARALLEL_MAX_SERVERS

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

Default: 0
Range of values: 0 to 109
parallel_min_percent=0
PARALLEL_MIN_SERVERS
Default: See explanation at right
Default is (CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2)
Range of values Default value to the value of PARALLEL_MAX_SERVERS
parallel_min_servers=0
PARALLEL_MIN_TIME_THRESHOLD
Default: AUTO
Range of values: {AUTO | integer}
parallel_min_time_threshold=AUTO
PARALLEL_SERVER Deprecated
PARALLEL_SERVER_INSTANCES Deprecated
PARALLEL_SERVERS_TARGET

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
parallel_servers_target=4
PARALLEL_THREADS_PER_CPU

Default: O/S dependent but usually 2
Any non-zero integer.
parallel_threads_per_cpu=4
THREAD

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

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

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

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

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

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

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

Default: FALSE
Version 7 Dictionary Accessibility support. Range of values TRUE or FALSE
07_dictionary_accessibility=FALSE
DB_SECUREFILE

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

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

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

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

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

Default: EXCLUSIVE
Specifies whether Oracle checks for a password file.
Range of Values: {SHARED | EXCLUSIVE | NONE}
remote_login_passwordfile='EXCLUSIVE'
REMOTE_OS_AUTHENT Deprecated
REMOTE_OS_ROLES

Default:FALSE
Specifies whether operating system roles are allowed for remote clients. The default value, false, causes Oracle to identify and manage roles for remote clients.
remote_os_rules=FALSE
SEC_CASE_SENSITIVE_LOGON Deprecated
SEC_MAX_FAILED_LOGIN_ATTEMPTS

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

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

Default: TRACE
Specifies the action that the database should take when bad packets are received from a possibly malicious client.
Range of Values: {NONE | TRACE | LOG | ALERT}
sec_protocol_eror_trace_action=TRACE
SEC_RETURN_SERVER_RELEASE_BANNER

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

No Default
An advanced parameter used to control when the redo for a commit is flushed to the redo logs.
Range of values: {NOWAIT | WAIT | FORCE_WAIT}
commit_wait=NOWAIT
COMMIT_WRITE Deprecated
DML_LOCKS Default value Derived: 4 * TRANSACTIONS. Specifies the maximum number of DML locks—one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.
Range of values 20 to unlimited; a setting of 0 disables enqueues
dml_locks=748
FAST_START_PARALLEL_ROLLBACK Determines the maximum number of processes that can exist for performing parallel rollback. This parameter is useful on systems in which some or all of the transactions are long running
Range of values {HI | LO | FALSE}
fast_start_parallel_rollback=LOW
GLOBAL_TXN_PROCESSES

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

Default: Derived: (1.1 * SESSIONS)
Specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.
Range of values: {4 to 2^32}
transactions=187
TRANSACTIONS_PER_ROLLBACK _SEGMENT

Default 5
Specifies the number of concurrent transactions you expect each rollback segment to have to handle. The minimum number of rollback segments acquired at startup is TRANSACTIONS divided by the value for this parameter. For example, if TRANSACTIONS is 101 and this parameter is 10, then the minimum number of rollback segments acquired would be the ratio 101/10, rounded up to 11.
transactions_per_rollback_segment=6
 
Undo Tablespace
UNDO_MANAGEMENT

Default: AUTO
Specifies which undo space management mode the system should use. When set to AUTO, the instance starts in automatic undo management mode. In manual undo management mode, undo space is allocated externally as rollback segments.
Range of values: {MANUAL | AUTO}
undo_management=AUTO
UNDO_RETENTION

Default: 900
Specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.
Range of values: 0 to 2^31-1
undo_retention=1440
UNDO_TABLESPAC E

No Default
Name of the undo tablespace to be used during startup.
undo_tablespace='UNDOTBS1'
 
Undocumented
Query for undocumented initialization parameters set pagesize 35
set linesize 150
col NAME format a40
col VALUE format a20
col DESCRIPTION format a60
set pause on
set pause 'Hit enter to continue'

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc DESCRIPTION
FROM x$ksppi x, x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND SUBSTR(x.ksppinm,1,1) = '_'
ORDER BY 1;
__DB_CACHE_SIZE Undocumented
__db_cache_size=136M
__JAVA_CACHE_SIZE Undocumented
__java_pool_size=12M
__LARGE_POOL_SIZE Undocumented
__large_pool_size=4M
__ORACLE_BASE Set from the environment
__oracle_base='c:\oracle\product'
__PGA_AGGREGATE_TARGET Undocumented
__pga_aggregate_target=308M
__SGA_TARGET Undocumented
__sga_target=512M
__SHARED_IO_POOL_SIZE Undocumented
__shared_io_pool_size=48M
__SHARED_POOL_SIZE Undocumented
__shared_pool_size=300M
__STREAMS_POOL_SIZE Undocumented
__streams_pool_size=4M
  _abort_on_mrp_crash FALSE abort database instance when MRP crashes
 
  _abort_recovery_on_join FALSE 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
 
_adaptive_direct_read

enable adaptive direct read
TRUE
 
_adaptive_direct_write

enable adaptive direct write
TRUE
 
  _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
 
_adaptive_log_file_sync_use_postwait_threshold

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

Permille of foreground load from when post/wait was last used
1001
 
  _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
 
_AWR_DISABLED_FLUSH_TABLES

disable flushing of specified AWR tables
Undocumented
TBD
  _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
 
   
 
_AWR_RESTRICT_MODE

enable AWR restrict mode
By default the value is FALSE which indicates that AWR snapshots will not be made when the database is in restricted mode.
See the DBMS_WORKLOAD_REPOSITORY.CONTROL_RESTRICTED_SNAPSHOT Demo using the link at page bottom
_AWR_snapshot_level

set Default AWR snapshot level
Default: BESTFIT
 
_AWR_sql_child_limit

setting for AWR SQL Child Limit
Default: 200
 
_B_TREE_BITMAP_PLANS

Default: TRUE
Undocumented
_b_tree_bitmap_plans=TRUE
_BCT_BITMAPS_PER_FILE

Default: 8
Number of bitmaps to store for each datafile
_bct_bitmaps_per_file=12
_BCT_BUFFER_ALLOCATION_MAX

Default: 104857600
maximum size of all change tracking buffer allocations, in bytes
TBD
_BCT_BUFFER_ALLOCATION_MIN_EXTENTS

Default: 1
mininum number of extents to allocate per buffer alloca
_bct_buffer_allocation_min_extents=2;
_BCT_BUFFER_ALLOCATION_SIZE

Default: 2097152
size of one change tracking buffer allocation, in bytes
TBD
_BCT_CHUNK_SIZE

Default: 0
change tracking datafile chunk size, in bytes
TBD
_BCT_CRASH_RESERVE_SIZE

Default: 262144
change tracking reserved crash recovery SGA space, in bytes
_bct_crash_reserve_size=393216;
_BCT_FILE_BLOCK_SIZE

Default: 0
block size of change tracking file, in bytes
_bct_file_block_size=8192;
_BCT_FILE_EXTENT_SIZE

Default: 0
extent size of change tracking file, in bytes
_bct_file_extent_size=65536;
_BCT_FIXTAB_FILE

Default: NULL
change tracking file for fixed tables
_bct_fixtab_file='/u01/oradata/flash_recovery_area/bct/bctfile.bct';
_BCT_INITIAL_PRIVATE_DBA_BUFFER_SIZE

Default: 0
initial number of entries in the private change tracking dba buffers
_bct_initial_private_dba_buffer_size=8192;
_BCT_PUBLIC_DBA_BUFFER_SIZE

Default: 0
total size of all public change tracking dba buffers, in bytes
_bct_public_dba_buffer_size=104857600
_BLOOM_FILTER_ENABLED

Default: TRUE
Undocumented
_bloom_filter_enabled=TRUE
_BLOOM_FOLDING_ENABLED

Default: TRUE
Undocumented
_bloom_folding_enabled=TRUE
_BLOOM_PRUNING_ENABLED

Default: TRUE
Undocumented
_bloom_pruning_enabled=TRUE
_COMPLEX_VIEW_MERGING Undocumented
_complex_view_merging=TRUE
_CONVERT_SET_TO_JOIN Undocumented
_convert_set_to_join=FALSE
_CORRUPTED_ROLLBACK_SEGMENTS

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

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

SQL> create pfile from spfile;

SQL> shutdown abort

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

SQL> create spfile from pfile

SQL> startup

SQL> drop rollback segment "_SYSSMU1_1222867085$";
_COST_EQUALITY_SEMI_JOIN Undocumented
_cost_equality_semi_join=TRUE
_CPU_TO_IO Undocumented
_cpu_to_io=0
_CURSOR_FEATURE_ENABLED Number of cursors per parent before obsoletion. Use if too many obsolete cursors are being created.
_cursor_feature_enabled=1026
_CURSOR_OBSOLETE_THRESHOLD Number of cursors per parent before obsoletion. Use if too many obsolete cursors are being created.
-- required
_cursor_feature_enabled
=1026 -- in 11.2.0.2 and diff. for ecery version
events= "106001 trace name context forever, level 1024"
-- then

_cursor_obsolete_threshold
=25
_DB_ALWAYS_CHECK_SYSTEM_TS

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

Default: TRUE
Alows Oracle the ability to artificually age a transaction for increased automatic cache management. Required for In-Memory Undo
SQL> ALTER SYSTEM SET "_db_writer_flush_imu" = FALSE;
_DIMENSION_SKIP_NULL Undocumented
_dimension_skip_null=TRUE
_ELIMINATE_COMMON_SUBEXPR Undocumented
_eliminate_common_subexpr=TRUE
_ENABLE_SCHEMA_SYNONYMS Undocumented
ALTER SYSTEM SET "_enable_schema_synonyms"=TRUE SCOPE=SPFILE;
-- for examples of schema synonym creation click on the Synonyms link at page bottom
_ENABLE_TYPE_DEP_SELECTIVITY Undocumented
_enable_type_dep_selectivity=TRUE
_FAST_FULL_SCAN_ENABLED Undocumented
_fast_full_scan_enabled=TRUE
_FIRST_K_ROWS_DYNAMIC_PRORATION Undocumented
_first_k_rows_dynamic_proration=TRUE
_GBY_HASH_AGGREGATION_ENABLED Undocumented
_gby_hash_aggregation_enabled=TRUE
_GENERALIZED_PRUNING_ENABLED Undocumented
_generalized_pruning_enabled=TRUE
_GLOBALINDEX_PNUM_FILTER_ENABLED Undocumented
_globalindex_pnum_filter_enabled=TRUE
_GS_ANTI_SEMI_JOIN_ALLOWED Undocumented
_gs_anti_semi_join_allowed=TRUE
_HIGH_PRIORITY_PROCESSES High priority process name mask

More information
_high_priority_processes=LMS*|VKTM
_IMPROVED_OUTERJOIN_CARD Undocumented
_improved_outerjoin_card=TRUE
_IMPROVED_ROW_LENGTH_ENABLED Undocumented
_improved_row_length_enabled=TRUE
_IMU_POOLS

Default: 3
Determines the number of pools used by In-Memory Undo
_imu_pools=5
_IN_MEMORY_UNDO

Default: TRUE
Determines whether In-Memory Undo is active
_in_memory_undo=FALSE
_INDEX_JOIN_ENABLED Undocumented
_index_join_enabled=TRUE
_KSB_RESTART_POLICY_TIMES Undocumented
_ksb_restart_policy_times='0'
_ksb_restart_policy_times
='60'
_ksb_restart_policy_times='120'
_ksb_restart_policy_times='240' # internal update to set default
_LEFT_NESTED_LOOPS_RANDOM Undocumented
_left_nested_loops_random=TRUE
_LOCAL_COMMUNICATION_COSTING_ENABLED Undocumented
_local_communication_costing_enabled=TRUE
_LOG_IO_SIZE

Default: 0
Automatically initiate log write if this many redo blocks in buffer
_log_io_size=60
_MEMORY_BROKER_STAT_INTERVAL Sets the interval between AMM checks to resize the SGA. The range of values is 1 to 999 seconds
_minimal_stats_aggregation=999
_MINIMAL_STATS_AGGREGATION Undocumented
_minimal_stats_aggregation=TRUE
_MMV_QUERY_REWRITE_ENABLED Undocumented
_mmv_query_rewrite_enabled=TRUE
_NEW_INITIAL_JOIN_ORDERS Undocumented
_new_initial_join_orders=TRUE
_NEWSORT_ENABLED

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

controls when new sort avoids sorting ordered input
Default: 63
 
_NEWSORT_TYPE

specifies options for the new sort algorithm
Default: 0
 
_NEW_SORT_COST_ESTIMATE

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

enable batching of the RHS IO in NLJ
Default is 1
_nlj_batching_enabled=1
_NONCDB_TO_PDB

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

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

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

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

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

optimizer adaptive cursor sharing
_optimizer_adaptive_cursor_sharing=<FALSE | TRUE>
_optimizer_adaptive_cursor_sharing=TRUE
_OPTIMIZER_BETTER_INLIST_COSTING

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

cost factor for cost-based query transformation
Default is 50
_optimizer_cbqt_no_size_restriction=TRUE
_OPTIMIZER_CBQT_NO_SIZE_RESTRICTION

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

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

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

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

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

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

enables cost-based query transformation
Default is LINEAR
_optimizer_cost_based_transformation='LINEAR'
_OPTIMIZER_COST_HJSMJ_MULTIMATCH

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

optimizer cost model
Default is CHOOSE
_optimizer_cost_model='CHOOSE'
_OPTIMIZER_DIM_SUBQ_JOIN_SEL

use join selectivity in choosing star transformation dimensions
_optimizer_dim_subq_join_sel=<FALSE | TRUE>
_optimizer_dim_subq_join_sel=TRUE
_OPTIMIZER_DISTINCT_ELIMINATION Undocumented
_optimizer_distinct_elimination=TRUE
_OPTIMIZER_ENABLE_DENSITY_IMPROVEMENTS Undocumented
_optimizer_enable_density_improvements=TRUE
_OPTIMIZER_ENABLE_EXTENDED_STATS Undocumented
_optimizer_enable_extended_stats=TRUE
_OPTIMIZER_ENHANCED_FILTER_PUSH Undocumented
_optimizer_enhanced_filter_push=TRUE
_OPTIMIZER_EXTEND_JPPD_VIEW_TYPES Undocumented
_optimizer_extend_jppd_view_types=TRUE
_OPTIMIZER_EXTENDED_CURSOR_SHARING Undocumented
_optimizer_extended_cursor_sharing='UDO'
_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL Undocumented
_optimizer_extended_cursor_sharing_rel='SIMPLE'
_OPTIMIZER_EXTENDED_STATS_USAGE_CONTROL Undocumented
_optimizer_extended_stats_usage_control=224
_OPTIMIZER_FILTER_PRED_PULLUP Undocumented
_optimizer_filter_pred_pullup=TRUE
_OPTIMIZER_FKR_INDEX_COST_BIAS Undocumented
_optimizer_fkr_index_cost_bias=10
_OPTIMIZER_GROUP_BY_PLACEMENT Undocumented
_optimizer_group_by_placement=TRUE
_OPTIMIZER_IMPROVE_SELECTIVITY Undocumented
_optimizer_improve_selectivity=TRUE
_OPTIMIZER_INVALIDATION_PERIOD

time window for invalidation of cursors of analyzed objects
time window for invalidation of cursors of analyzed obj
Default is 18000
_optimizer_invalidate_period=16000
_OPTIMIZER_JOIN_ELIMINATION_ENABLED Undocumented
_optimizer_join_elimination_enabled=TRUE
_OPTIMIZER_JOIN_ORDER_CONTROL Undocumented
_optimizer_join_order_control=3
_OPTIMIZER_JOIN_SEL_SANITY_CHECK Undocumented
_optimizer_join_sel_sanity_check=TRUE
_OPTIMIZER_MAX_PERMUTATIONS Undocumented
_optimizer_max_permutations=2000
_OPTIMIZER_MODE_FORCE Undocumented
_optimizer_mode_force=TRUE
_OPTIMIZER_MULTI_LEVEL_PUSH_PRED Undocumented
_optimizer_multi_level_push_pred=TRUE
_OPTIMIZER_NATIVE_FULL_OUTER_JOIN Undocumented
_optimizer_native_full_outer_join='FORCE'
_OPTIMIZER_NEW_JOIN_CARD_COMPUTATION Undocumented
_optimizer_new_join_card_computation=TRUE
_OPTIMIZER_NULL_AWARE_ANTIJOIN Undocumented
_optimizer_null_aware_antijoin=TRUE
_OPTIMIZER_OR_EXPANSION Undocumented
_optimizer_or_expansion='DEPTH'
_OPTIMIZER_ORDER_BY_ELIMINATION_ENABLED Undocumented
_optimizer_order_by_elimination_enabled=TRUE
_OPTIMIZER_OUTER_TO_ANTI_ENABLED Undocumented
_optimizer_outer_to_anti_enabled=TRUE
_OPTIMIZER_PUSH_DOWN_DISTINCT Undocumented
_optimizer_push_down_distinct=0
_OPTIMIZER_PUSH_PRED_COST_BASED Undocumented
_optimizer_push_pred_cost_based=TRUE
_OPTIMIZER_ROWNUM_BIND_DEFAULT Undocumented
_optimizer_rownum_bind_default=10
_OPTIMIZER_ROWNM_PRED_BASED_FKR Undocumented
_optimizer_rownum_pred_based_fkr=TRUE
_OPTIMIZER_SKIP_SCAN_ENABLED Undocumented
_optimizer_skip_scan_enabled=TRUE
_OPTIMIZER_SORTMERGE_JOIN_INEQUALITY Undocumented
_optimizer_sortmerge_join_inequality=TRUE
_OPTIMIZER_SQU_BOTTOMUP Undocumented
_optimizer_squ_bottomup=TRUE
_OPTIMIZER_STAR_TRAN_IN_WITH_CLAUSE Undocumented
_optimizer_star_tran_in_with_clause=TRUE
_OPTIMIZER_SYSTEM_STATS_USAGE Undocumented
_optimizer_system_stats_usage=TRUE
_OPTIMIZER_TRANSITIVITY_RETAIN

retain equi-join pred upon transitive equality pred generation
_optimizer_transitivity_retain=<FALSE | TRUE>
_optimizer_transitivity_retain=TRUE
_OPTIMIZER_UNDO_COST_CHANGE Default is 12.1.0.1
_optimizer_undo_cost_change='12.1.0.2'
_ORACLE_SCRIPT

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

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

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

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

TRUE to obey force parallel query/dml/ddl under System PL/SQL
_parallel_syspls_obey_force=<FALSE | TRUE>
_parallel_syspls_obey_force=TRUE
_PARTITION_VIEW_ENABLED Undocumented
_partition_view_enabled=TRUE
_PIVOT_IMPLEMENTATION_METHOD Undocumented
_pivot_implementation_method='CHOOSE'
_PRE_REWRITE_PUSH_PRED Undocumented
_pre_rewrite_push_pred=TRUE
_PRED_MOVE_AROUND Undocumented
_pred_move_around=TRUE
_PUSH_JOIN_PREDICATE Undocumented
_push_join_predicate=TRUE
_PUSH_JOIN_UNION_VIEW Undocumented
_push_join_union_view=TRUE
_PUSH_JOIN_UNION_VIEW2 Undocumented
_push_join_union_view2=TRUE
_PX_MINUX_INTERSECT Undocumented
_px_minus_intersect=TRUE
_PX_PWG_ENABLED Undocumented
_px_pwg_enabled=TRUE
_PX_UAL_SERIAL_INPUT Undocumented
_px_ual_serial_input=TRUE
_QUERY_REWRITE_SETOPGRW_ENABLE

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

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

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

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

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

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

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

Parsed category qualifier for applying hintsets
Default = 'DEFAULT'
_sqltune_category_parsed='DEFAULT'
_SUBQUERY_PRUNING_ENABLED

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

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

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

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_TEMP

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

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

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

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

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

expand queries with GSets into UNIONs for rewrite
Undocumented
_union_rewrite_for_gs='YES_GSET_MVS'
_UNNEST_SUBQUERY

enables unnesting of complex subqueries
_unnest_subquery=<FALSE | TRUE>
EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
  SELECT srvr_id
  FROM serv_inst);

SELECT * FROM TABLE(dbms_xplan.display);

alter session set "_unnest_subquery"=FALSE;

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

SELECT * FROM TABLE(dbms_xplan.display);
_USE_COLUMN_STATS_FOR_FUNCTION

enable the use of column statistics for DDP functions
_use_column_stats_for_function=<FALSE | TRUE>
_use_column_stats_for_function=TRUE

Related Topics
DBMS_WORKLOAD_REPOSITORY
Init.ora & SPFILE
Synonyms

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