Oracle Initialization Parameters

Oracle Initialization Parameters

Initialization parameters are defined in the PFILE
( or init$ORACLE_SID.ora ).
Also see: Undocumented Parameters and
V$OBSOLETE_PARAMETER. Database parameters can be viewed using
or query V$SYSTEM_PARAMETER. Query
V$SPPARAMETER to determine current SPFILE
To find to parameter values specified in PFILE:
find /u01/app/oracle/admin/*/pfile/init*.ora -print | xargs grep -i COMPATIBLE

Type Documented Parameter Description
static active_instance_count Oracle9i Real Application Clusters parameter.
Number of active instances in the cluster database.
always_anti_join 8i obsolete in 9.0.1. Became underscore parameters.
always_semi_join 8i obsolete in 9.0.1. Became underscore parameters.
aq_tm_processes Advanced Queueing. number of AQ Time Managers to start
asm_diskgroups 10g Automatic Storage Management parameter.
List of names of disk groups to be mounted.
asm_diskstring 10g Automatic Storage Management parameter.
An O/S-dependent value used to limit the set of disks considered for discovery.
asm_power_limit 10g Automatic Storage Management parameter.
Maximum power on used for rebalancing disk.
archive_lag_target 9i maximum number of seconds of redos the standby could lose
static audit_file_dest directory in which auditing files are to reside
static audit_sys_operations 9i enable/disable sys auditing
static audit_syslog_level 10g enable/disables OS audit logs to be written to the system via the SYSLOG utility.
static audit_trail enable system auditing
static background_core_dump core size for background processes
background_dump_dest detached process dump directory
backup_tape_io_slaves BACKUP Tape I/O slaves.
dynamic, but deferred only.
static bitmap_merge_area_size depreciated unless using MTS.
See: pga_aggregate_target.
maximum memory allow for BITMAP MERGE.
static blank_trimming blank trimming semantics parameter
static buffer_pool_keep deprecated. See:
static buffer_pool_recycle deprecated. See: db_recycle_cache_size
number of database blocks/latches in recycle buffer pool
static* circuits 9i max number of circuits. *Dynamic in 10g.
commit_write 10g used to control how redo for transaction commits is written to the redo logs
static cluster_database 9i Oracle9i Real Application Clusters parameter.
if TRUE startup in cluster database mode
static cluster_database_instances 9i Oracle9i Real Application Clusters parameter.
number of instances to use for sizing cluster db SGA structures
static cluster_interconnects 9i Oracle9i Real Application Clusters parameter.
interconnects for RAC use
static commit_point_strength bias this node has toward not preparing in a two-phase commit
static compatible database will be completely compatible with this software version
control_file_record_keep_time control file record keep time in days
static control_files specifies one or more names of control files, separated by commas
core_dump_dest core dump directory
static cpu_count initial number of cpu’s for this instance
static create_bitmap_area_size depreciated unless using MTS.
See: pga_aggregate_target.
size of create bitmap buffer for bitmap index
create_stored_outlines 10g whether or not an outline is stored for each query submitted during the session.
cursor_sharing cursor sharing mode
static cursor_space_for_time use more memory in order to get faster execution
db_2k_cache_size 9i size of cache for 2K buffers
db_4k_cache_size 9i size of cache for 4K buffers
db_8k_cache_size 9i size of cache for 8K buffers
db_16k_cache_size 9i size of cache for 16K buffers
db_32k_cache_size 9i size of cache for 32K buffers
db_block_buffers depreciated. See: db_cache_size
number of database blocks cached in memory
db_block_checking data and index block checking
db_block_checksum store checksum in db blocks and check during reads
db_block_lru_latches 8i obsolete in 9.0.1.( has become an undocumented parameter )
db_block_max_dirty_target 8i obsolete in 9.0.1.
static db_block_size size of database block in bytes
db_cache_advice 9i buffer cache sizing advisory
db_nk_cache_size 9i specifies the size of the cache for the nK buffers.
db_cache_size 9i size of DEFAULT database buffer cache/pool for standard block size buffers.
db_create_file_dest 9i default database location. Used by Oracle Managed Files (OMF)
db_create_online_log_dest_n 9i online log/controlfile destination(s) — up to 5. Used by Oracle Managed Files (OMF)
static db_domain directory part of global database name stored with CREATE DATABASE command
db_file_multiblock_read_count db block to be read each IO
static db_file_name_convert datafile name convert patterns and strings for standby/clone db
static db_files max allowable # db files
db_keep_cache_size 9i size of KEEP buffer pool for standard block size buffers
static db_name database name specified in CREATE DATABASE
db_recycle_cache_size 9i size of RECYCLE buffer pool for standard block size buffers
static db_writer_processes number of background database writer processes to start
static dblink_encrypt_login enforce password for distributed login always be encrypted
static dbwr_io_slaves DBWR I/O slaves
dg_broker_config_file 9i data guard broker configuration file 1 & 2
dg_broker_start 9i start data guard broker framework (DMON process)
static disk_asynch_io use asynch I/O for random access devices
dispatchers 9i MTS/Shared Server parameter.
specifications of dispatchers
static distributed_lock_timeout 9i number of seconds a distributed transaction waits for a lock
static distributed_transactions 8i obsolete in 9.2. See open_links
and open_links_per_instance
static dml_locks dml locks – one for each table modified in a transaction
drs_start 8i depreciated. See dg_broker_start
static enqueue_resources resources for enqueues
static event debug event control – default null string
fal_client 9i FAL client
fal_server 9i FAL server list
fast_start_io_target depreciated. See fast_start_mttr_target.
fast_start_mttr_target 9i MTTR target of forward crash recovery in seconds
fast_start_parallel_rollback max number of parallel recovery slaves that may be used
file_mapping 9i enable file mapping
filesystemio_options 9i IO operations on filesystem files
fixed_date fixed SYSDATE value
gc_defer_time 8i obsolete in 9.0.1.
static gc_files_to_locks mapping between file numbers and global cache locks (DFS)
gc_releasable_locks 8i obsolete in 9.0.1.
gc_rollback_locks 8i obsolete in 9.0.1.
static global_context_pool_size 9i global application context pool size in bytes
global_names enforce that database links have same name as remote database
static* hash_area_size depreciated unless using MTS.
See: pga_aggregate_target.
size of in-memory hash work area
static* hash_join_enabled enable/disable hash join
hash_multiblock_io_count 8i obsolete in 9.0.1.
static hi_shared_memory_address SGA starting address (high order 32-bits on 64-bit platforms)
hs_autoregister Heterogeneous Services Parameter.
enable automatic server DD updates in HS agent self-registration
static ifile include file in pfile
static instance_groups Oracle9i Real Application Clusters parameter.
list of instance group names
static instance_name instance name supported by the instance
static instance_number Oracle9i Real Application Clusters parameter.
instance number
static java_max_sessionspace_size max allowed size in bytes of a Java sessionspace
static java_pool_size size in bytes of the Java pool
static java_soft_sessionspace_limit warning limit on size in bytes of a Java sessionspace
job_queue_interval 8i obsolete in 9.0.1.
job_queue_processes number of job queue slave processes
large_pool_size size in bytes of the large allocation pool
license_max_sessions maximum number of non-system user sessions allowed
license_max_users maximum number of named users that can be created in the database
license_sessions_warning warning level for number of non-system user sessions
lm_locks 8i number of locks configured for the lock manager
lm_ress 8i number of resources configured for the lock manager
local_listener local listener
static lock_name_space lock name space used for generating lock names for standby/clone
static lock_sga lock entire SGA in physical memory
log_archive_dest_n archival destination text string
log_archive_dest_state_n archival destination state text string
log_archive_duplex_dest 8i depreciated. See log_archive_dest_n
duplex archival destination text string
static log_archive_format archival destination format
log_archive_max_processes maximum number of active ARC0 processes
log_archive_min_succeed_dest minimum number of archive destinations that must succeed
static log_archive_start start archival process on SGA initialization
log_archive_trace establish archivelog operation tracing level
static log_buffer redo circular buffer size
log_checkpoint_interval # redo blocks checkpoints
threshold. Use fast_start_mttr_target instead.
log_checkpoint_timeout maximum time interval between checkpoints in seconds. Use fast_start_mttr_target instead.
log_checkpoints_to_alert log checkpoint begin/end to alert file
static log_file_name_convert logfile name convert patterns and strings for standby/clone db
static log_parallelism 9i number of log buffer strands
static logmnr_max_persistent_sessions maximum number of threads to mine
static max_commit_propagation_delay Oracle9i Real Application Clusters parameter.
Max age of new snapshot in .01 seconds
static max_dispatchers 9i max number of dispatchers
max_dump_file_size maximum size (blocks) of dump file
static max_enabled_roles max number of roles a user can have enabled
static max_rollback_segments max. number of rollback segments in SGA cache
static max_shared_servers 9i max number of shared servers
mts_circuits depreciated. See circuits
mts_dispatchers depreciated. See dispatchers
mts_listener_address obsolete in 8.1.
mts_max_dispatchers depreciated. See max_dispatchers
mts_max_servers depreciated. See max_shared_servers
mts_multiple_listeners obsolete in 8.1.
mts_servers depreciated. See shared_servers
mts_service obsolete in 8.1.
mts_sessions depreciated. See shared_servers_sessions
static* nls_calendar NLS calendar system name
static* nls_comp NLS comparison
static* nls_currency NLS local currency symbol
static* nls_date_format NLS Oracle date format
static* nls_date_language NLS date language name
static* nls_dual_currency dual currency symbol
static* nls_iso_currency NLS ISO currency territory name
static* nls_language NLS language name
nls_length_semantics 9i create columns using byte or char semantics by default
nls_nchar_conv_excp 9i NLS raise an exception instead of allowing implicit conversion
static* nls_numeric_characters NLS numeric characters
static* nls_sort NLS linguistic definition name
static* nls_territory NLS territory name
static* nls_time_format time format
static* nls_time_tz_format time with timezone format
static* nls_timestamp_format time stamp format
static* nls_timestamp_tz_format timestamp with timezone format
static o7_dictionary_accessibility version 7 dictionary accessibility support
object_cache_max_size_percent percentage of maximum size over optimal of the user session’s ob.
dynamic, but deferred only.
object_cache_optimal_size optimal size of the user session’s object cache in bytes.
dynamic, but deferred only.
olap_page_pool_size 9i On Line Analytical Processing parameter.
Size of the olap page pool in bytes.
dynamic, but deferred only.
open_cursors max # cursors per session
static open_links max # open links per session
static open_links_per_instance max # open links per instance
ops_interconnects 8i obsolete in 9.0.1.
optimizer_dynamic_sampling 9i optimizer dynamic sampling
static optimizer_features_enable optimizer plan compatibility parameter
static* optimizer_index_caching optimizer percent index caching
static* optimizer_index_cost_adj optimizer index cost adjustment
static* optimizer_max_permutations optimizer maximum join permutations per query block
static* optimizer_mode optimizer mode
optimizer_percent_parallel 8i obsolete in 9.0.1. Became
static oracle_trace_collection_name Oracle TRACE default collection name
static oracle_trace_collection_path Oracle TRACE collection path
static oracle_trace_collection_size Oracle TRACE collection file max. size
oracle_trace_enable Oracle Trace enabled/disabled
static oracle_trace_facility_name Oracle TRACE default facility name
static oracle_trace_facility_path Oracle TRACE facility path
static os_authent_prefix prefix for auto-logon accounts
static os_roles retrieve roles from the operating system
parallel_adaptive_multi_user enable adaptive setting of degree for multiple user streams
static parallel_automatic_tuning enable intelligent defaults for parallel execution parameters
parallel_broadcast_enabled 8i obsolete in 9.2.
static parallel_execution_message_size message buffer size for parallel execution
parallel_instance_group Oracle9i Real Application Clusters parameter. Instance group to use
for all parallel operations
static parallel_max_servers maximum parallel query servers per instance
static* parallel_min_percent minimum percent of threads required for parallel query
static parallel_min_servers minimum parallel query servers per instance
parallel_server depreciated. See cluster_database
parallel_server_instances depreciated. See cluster_database_instances
parallel_threads_per_cpu number of parallel execution threads per CPU
partition_view_enabled depreciated. See table partitioning
pga_aggregate_target 9i target size for the aggregate PGA memory consumed by the instance
plsql_compiler_flags 9i PL/SQL compiler flags
plsql_native_c_compiler 9i plsql native C compiler
plsql_native_library_dir 9i PL/SQL native library dir
plsql_native_library_subdir_count 9i PL/SQL native library number of subdirectories
plsql_native_linker 9i PL/SQL native linker
plsql_native_make_file_name 9i PL/SQL native compilation make file
plsql_native_make_utility 9i PL/SQL native compilation make utility
plsql_v2_compatibility PL/SQL version 2.x compatibility flag
static pre_page_sga pre-page SGA for process
static processes user processes
query_rewrite_enabled allow rewrite of queries using materialized views if enabled
query_rewrite_integrity perform rewrite using materialized views with desired integrity
static rdbms_server_dn RDBMS’s Distinguished Name
static read_only_open_delayed if TRUE delay opening of read only files until first access
static recovery_parallelism number of server processes to use for parallel recovery
static remote_archive_enable 9i remote archival enable setting
remote_dependencies_mode remote-procedure-call dependencies mode parameter
remote_listener 9i remote listener
static remote_login_passwordfile password file usage parameter
static remote_os_authent allow non-secure remote clients to use auto-logon accounts
static remote_os_roles allow non-secure remote clients to use os roles
replication_dependency_tracking Replication parameter.
tracking dependency for replication parallel propagation
resource_limit master switch for resource limit
resource_manager_plan resource mgr top plan
static rollback_segments undo segment list
static row_locking row-locking
static serial_reuse reuse the frame segments
serializable unsupported starting 8.1.6
service_names service names supported by the instance
static* session_cached_cursors number of cursors to save in the session cursor cache
static session_max_open_files maximum number of open files allowed per session
static sessions user and system sessions
static sga_max_size 9i max total SGA size
static shadow_core_dump core size for shadow processes
static shared_memory_address SGA starting address (low order 32-bits on 64-bit platforms)
static shared_pool_reserved_size size in bytes of reserved area of shared pool
shared_pool_size size in bytes of shared pool
static shared_server_sessions max number of shared server sessions
shared_servers 9i number of shared servers to start up
sort_area_retained_size depreciated unless using MTS.
See: pga_aggregate_target.
size sort work area retained between fetch calls.
sort_area_size depreciated unless using MTS.
See: pga_aggregate_target.
size of in-memory sort work area.
sort_multiblock_read_count 8i obsolete in 9.0.1.
static spfile 9i server parameter file
static* sql_trace enable SQL trace
sql_version NOT LISTED IN DOC.
SQL language version parameter for compatibility issues
static sql92_security require select privilege for searched update/delete
standby_archive_dest standby database archivelog destination text string
standby_file_management 9i if auto then files are created/dropped automatically on standby
static* star_transformation_enabled enable the use of star transformation
statistics_level 9i statistics level
static tape_asynch_io use asynch I/O requests for tape devices
text_enable 8i obsolete in 9.0.1.
static thread Oracle9i Real Application Clusters parameter.
redo thread to mount
timed_os_statistics internal os statistic gathering interval in seconds
timed_statistics maintain internal timing statistics
trace_enabled 9i enable KST tracing — provides low overhead memory tracing
static* tracefile_identifier trace file custom identifier
transaction_auditing transaction auditing records generated in the redo log.
dynamic, but deferred only.
static transactions max. number of concurrent active transactions
static transactions_per_rollback_segment number of active transactions per rollback segment
static undo_management 9i instance runs in SMU mode if TRUE, else in RBU mode
undo_retention 9i undo retention in seconds
undo_suppress_errors 9i suppress RBU errors in SMU mode
undo_tablespace 9i use/switch undo tablespace
static use_indirect_data_buffers 8i obsolete in 9.0.1.
user_dump_dest user process dump directory
static utl_file_dir utl_file accessible directories list
workarea_size_policy 9i policy used to size SQL working areas (MANUAL/AUTO)

One Response

  1. For most recent news you have to go to see world wide web and on world-wide-web I found
    this web site aas a bes web page for most recent updates.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: