|
|
init.ora Parameters for Oracle 9i and 10g
Parameters that are new in 10g are shown in Green,
Parameters that are valid in 9i but have been dropped (or hidden) in 10g are
shown in Red.
PARAMETER DESCRIPTION
------------------------------ ----------------------------------------
O7_DICTIONARY_ACCESSIBILITY Version 7 Dictionary Accessibility
support [TRUE | FALSE]
active_instance_count Number of active instances in the
cluster database [NUMBER]
aq_tm_processes Number of AQ Time Managers to start [NUMBER]
archive_lag_target Maximum number of seconds of redos the
standby could lose [NUMBER]
asm_diskgroups Disk groups to mount automatically [CHAR]
asm_diskstring Disk set locations for discovery [CHAR]
asm_power_limit Number of processes for disk rebalancing [NUMBER]
audit_file_dest Directory in which auditing files are to reside ['Path']
audit_sys_operations Enable sys auditing [TRUE|FALSE]
audit_trail Enable system auditing [NONE|DB|DB_EXTENDED|OS]
background_core_dump Core Size for Background Processes [partial | full]
background_dump_dest Detached process dump directory [file_path]
backup_tape_io_slaves BACKUP Tape I/O slaves [TRUE | FALSE]
bitmap_merge_area_size Maximum memory allow for BITMAP MERGE [NUMBER]
blank_trimming Blank trimming semantics parameter [TRUE | FALSE]
buffer_pool_keep Number of database blocks/latches in
keep buffer pool [CHAR: (buffers:n, latches:m)]
buffer_pool_recycle Number of database blocks/latches in
recycle buffer pool [CHAR: (buffers:n, latches:m)]
circuits Max number of virtual circuits [NUMBER]
cluster_database If TRUE startup in cluster database mode [TRUE | FALSE]
cluster_database_instances Number of instances to use for sizing
cluster db SGA structures [NUMBER]
cluster_interconnects Interconnects for RAC use [CHAR]
commit_point_strength Bias this node has toward not preparing
in a two-phase commit [NUMBER (0-255)]
compatible Database will be completely compatible
with this software version [CHAR: 9.2.0.0.0]
control_file_record_keep_time Control file record keep time in days [NUMBER]
control_files Control file names list [file_path,file_path..]
core_dump_dest Core dump directory [file_path]
cpu_count Initial number of cpu's for this instance [NUMBER]
create_bitmap_area_size Size of create bitmap buffer for bitmap
index [INTEGER]
cursor_sharing Cursor sharing mode [EXACT | SIMILAR | FORCE]
create_stored_outlines Create stored outlines for DML statements [TRUE | FALSE | category_name]
cursor_space_for_time Use more memory in order to get faster
execution [TRUE | FALSE]
db_16k_cache_size Size of cache for 16K buffers [bytes]
db_2k_cache_size Size of cache for 2K buffers [bytes]
db_32k_cache_size Size of cache for 32K buffers [bytes]
db_4k_cache_size Size of cache for 4K buffers [bytes]
db_8k_cache_size Size of cache for 8K buffers [bytes]
db_block_buffers Number of database blocks to cache in memory
[bytes: 8M or NUMBER of blocks (Ora7)]
db_block_checking Data and index block checking [TRUE | FALSE]
db_block_checksum Store checksum in db blocks and check
during reads [TRUE | FALSE]
db_block_size Size of database block [bytes]
db_cache_advice Buffer cache sizing advisory [internal use only]
db_cache_size Size of DEFAULT buffer pool for standard
block size buffers [bytes]
db_create_file_dest Default database location ['Path_to_directory']
db_create_online_log_dest_n Online log/controlfile destination (where n=1-5) ['Path']
db_domain Directory part of global database name
stored with CREATE DATABASE [CHAR]
db_file_multiblock_read_count Db blocks to be read each IO [NUMBER]
db_file_name_convert Datafile name convert patterns and
strings for standby/clone db [, ]
db_files Max allowable # db files [NUMBER]
db_flashback_retention_target Maximum Flashback Database log retention time in minutes [NUMBER]
db_keep_cache_size Size of KEEP buffer pool for standard
block size buffers [bytes]
db_name Database name specified in CREATE
DATABASE [CHAR]
db_recovery_file_dest Default database recovery file location [CHAR]
db_recovery_file_dest_size Database recovery files size limit [bytes]
db_recycle_cache_size Size of RECYCLE buffer pool for standard
block size buffers [bytes]
db_unique_name Database Unique Name [CHAR]
db_writer_processes Number of background database writer
processes to start [NUMBER]
dblink_encrypt_login Enforce password for distributed login
always be encrypted [TRUE | FALSE]
dbwr_io_slaves DBWR I/O slaves [NUMBER]
ddl_wait_for_locks Disable NOWAIT DML lock acquisitions [TRUE | FALSE]
dg_broker_config_file1 Data guard broker configuration file #1 ['Path']
dg_broker_config_file2 Data guard broker configuration file #2 ['Path']
dg_broker_start Start Data Guard broker framework (DMON
process) [TRUE | FALSE]
disk_asynch_io Use asynch I/O for random access devices [TRUE | FALSE]
dispatchers Specifications of dispatchers
(MTS_dispatchers in Ora 8) [CHAR]
distributed_lock_timeout Number of seconds a distributed transaction
waits for a lock [Internal]
dml_locks Dml locks - one for each table modified
in a transaction [NUMBER]
drs_start Start DG Broker monitor (DMON process)[TRUE | FALSE]
enqueue_resources Resources for enqueues [NUMBER]
event Debug event control - default null string [CHAR]
fal_client FAL client [CHAR]
fal_server FAL server list [CHAR]
fast_start_io_target Upper bound on recovery reads [NUMBER]
fast_start_mttr_target MTTR target of forward crash recovery
in seconds [NUMBER]
fast_start_parallel_rollback Max number of parallel recovery slaves
that may be used [LOW | HIGH | FALSE]
file_mapping Enable file mapping [TRUE | FALSE]
fileio_network_adapters Network Adapters for File I/O [CHAR]
filesystemio_options IO operations on filesystem files [Internal]
fixed_date Fix SYSDATE value for debugging[NONE or '2000_12_30_24_59_00']
gc_files_to_locks RAC/OPS - lock granularity number of
global cache locks per file (DFS) [CHAR]
gcs_server_processes Number of background gcs server processes to start [NUMBER]
global_context_pool_size Global Application Context Pool Size in
Bytes [bytes]
global_names Enforce that database links have same
name as remote database [TRUE | FALSE]
hash_area_size Size of in-memory hash work area (Shared Server)[bytes]
hash_join_enabled Enable/disable hash join (CBO) [TRUE | FALSE]
hi_shared_memory_address SGA starting address (high order 32-bits
on 64-bit platforms) [NUMBER]
hs_autoregister Enable automatic server DD updates in HS
agent self-registration [TRUE | FALSE]
ifile Include file in init.ora ['path_to_file']
instance_groups List of instance group names [CHAR]
instance_name Instance name supported by the instance [CHAR]
instance_number Instance number [NUMBER]
instance_type Type of instance to be executed
RDBMS or Automated Storage Management [RDBMS | ASM]
java_max_sessionspace_size Max allowed size in bytes of a Java
sessionspace [bytes]
java_pool_size Size in bytes of the Java pool [bytes]
java_soft_sessionspace_limit Warning limit on size in bytes of a Java
sessionspace [NUMBER]
job_queue_processes Number of job queue slave processes [NUMBER]
large_pool_size Size in bytes of the large allocation pool [bytes]
ldap_directory_access RDBMS's LDAP access option [NONE | PASSWORD | SSL]
license_max_sessions Maximum number of non-system user sessions
(concurrent licensing) [NUMBER]
license_max_users Maximum number of named users that can be created
(named user licensing) [NUMBER]
license_sessions_warning Warning level for number of non-system
user sessions [NUMBER]
local_listener Define which listeners instances register with [CHAR]
lock_name_space Used for generating lock names for standby/primary database
assign each a unique name space [CHAR]
lock_sga Lock entire SGA in physical memory [Internal]
log_archive_config Log archive config
[SEND|NOSEND] [RECEIVE|NORECEIVE] [ DG_CONFIG]
log_archive_dest Archive logs destination ['path_to_directory']
log_archive_dest_n Archive logging parameters (n=1-10)
Enterprise Edition [CHAR]
log_archive_dest_state_n Archive logging parameter status (n=1-10) [CHAR]
Enterprise Edition [CHAR]
log_archive_duplex_dest Duplex archival destination ['path_to_directory']
log_archive_format Archive log filename format [CHAR: "MyApp%S.ARC"]
log_archive_local_first Establish EXPEDITE attribute default value [TRUE | FALSE]
log_archive_max_processes Maximum number of active ARCH processes [NUMBER]
log_archive_min_succeed_dest Minimum number of archive destinations
that must succeed [NUMBER]
log_archive_start Start archival process on SGA initialization [TRUE | FALSE]
log_archive_trace Archive log tracing level [NUMBER]
log_buffer Redo circular buffer size [bytes]
log_checkpoint_interval Checkpoint threshold, # redo blocks [NUMBER]
log_checkpoint_timeout Checkpoint threshold, maximum time interval between
checkpoints in seconds [NUMBER]
log_checkpoints_to_alert Log checkpoint begin/end to alert file [TRUE | FALSE]
log_file_name_convert Logfile name convert patterns and
strings for standby/clone db [, ]
log_parallelism Number of log buffer strands [NUMBER]
logmnr_max_persistent_sessions Maximum number of threads to mine [NUMBER]
max_commit_propagation_delay Max age of new snapshot in .01 seconds [NUMBER]
max_dispatchers Max number of dispatchers [NUMBER]
max_dump_file_size Maximum size (blocks) of dump file [UNLIMITED or bytes]
max_enabled_roles Max number of roles a user can have enabled [NUMBER]
max_rollback_segments Max number of rollback segments in SGA cache [NUMBER]
max_shared_servers Max number of shared servers [NUMBER]
mts_circuits Max number of circuits [NUMBER]
mts_dispatchers Specifications of dispatchers [CHAR]
mts_listener_address Address(es) of network listener [CHAR]
mts_max_dispatchers Max number of dispatchers [NUMBER]
mts_max_servers Max number of shared servers [NUMBER]
mts_multiple_listeners Are multiple listeners enabled? [TRUE | FALSE]
mts_servers Number of shared servers to start up [NUMBER]
mts_service Service supported by dispatchers [CHAR]
mts_sessions max number of shared server sessions [NUMBER]
nls_calendar NLS calendar system name (Default=GREGORIAN) [CHAR]
nls_comp NLS comparison, Enterprise Edition [BINARY | ANSI]
nls_currency NLS local currency symbol [CHAR]
nls_date_format NLS Oracle date format [CHAR]
nls_date_language NLS date language name (Default=AMERICAN) [CHAR]
nls_dual_currency Dual currency symbol [CHAR]
nls_iso_currency NLS ISO currency territory name
override the default set by NLS_TERRITORY [CHAR]
nls_language NLS language name (session default) [CHAR]
nls_length_semantics Create columns using byte or char
semantics by default [BYTE | CHAR]
nls_nchar_conv_excp NLS raise an exception instead of
allowing implicit conversion [CHAR]
nls_numeric_characters NLS numeric characters [CHAR]
nls_sort Case-sensitive or insensitive sort [Language]
language may be BINARY, BINARY_CI, BINARY_AI,
GERMAN, GERMAN_CI, etc
nls_territory NLS territory name (country settings) [CHAR]
nls_time_format Time format [CHAR]
nls_time_tz_format Time with timezone format [CHAR]
nls_timestamp_format Time stamp format [CHAR]
nls_timestamp_tz_format Timestamp with timezone format [CHAR]
object_cache_max_size_percent Percentage of maximum size over optimal
of the user session's ob [NUMBER]
object_cache_optimal_size Optimal size of the user session's
object cache in bytes [bytes]
olap_page_pool_size Size of the olap page pool in bytes [bytes]
open_cursors Max # cursors per session [NUMBER]
open_links Max # open links per session [NUMBER]
open_links_per_instance Max # open links per instance [NUMBER]
optimizer_dynamic_sampling Optimizer dynamic sampling [NUMBER]
optimizer_features_enable Optimizer plan compatibility
(oracle version e.g. 8.1.7) [CHAR]
optimizer_index_caching Optimizer index caching percent [NUMBER]
optimizer_index_cost_adj Optimizer index cost adjustment [NUMBER]
optimizer_max_permutations Optimizer maximum join permutations per
query block [NUMBER]
optimizer_mode Optimizer mode [RULE | CHOOSE | FIRST_ROWS | ALL_ROWS]
oracle_trace_collection_name Oracle TRACE default collection name [CHAR]
oracle_trace_collection_path Oracle TRACE collection path [CHAR]
oracle_trace_collection_size Oracle TRACE collection file max. size [NUMBER]
oracle_trace_enable Oracle Trace enabled/disabled [TRUE | FALSE]
oracle_trace_facility_name Oracle TRACE default facility name [CHAR]
oracle_trace_facility_path Oracle TRACE facility path [CHAR]
os_authent_prefix Prefix for auto-logon accounts [CHAR]
os_roles Retrieve roles from the operating system [TRUE | FALSE]
parallel_adaptive_multi_user Enable adaptive setting of degree for
multiple user streams [TRUE | FALSE]
parallel_automatic_tuning Enable intelligent defaults for parallel
execution parameters [TRUE | FALSE]
parallel_execution_message_size Message buffer size for parallel
execution [bytes]
parallel_instance_group Instance group to use for all parallel
operations [CHAR]
parallel_max_servers Maximum parallel query servers per
instance [NUMBER]
parallel_min_percent Minimum percent of threads required for
parallel query [NUMBER]
parallel_min_servers Minimum parallel query servers per
instance [NUMBER]
parallel_server If TRUE startup in parallel server mode [TRUE | FALSE]
parallel_server_instances Number of instances to use for sizing
OPS SGA structures [NUMBER]
parallel_threads_per_cpu Number of parallel execution threads per
CPU [NUMBER]
partition_view_enabled Enable/disable partitioned views [TRUE | FALSE]
pga_aggregate_target Target size for the aggregate PGA memory
consumed by the instance [bytes]
plsql_code_type PL/SQL code-type [INTERPRETED | NATIVE]
plsql_compiler_flags PL/SQL compiler flags [CHAR]
plsql_debug PL/SQL debug [TRUE | FALSE]
plsql_native_c_compiler plsql native C compiler [CHAR]
plsql_native_library_dir plsql native library dir ['Path_to_directory']
plsql_native_library_subdir_count plsql native library number of
subdirectories [NUMBER]
plsql_native_linker plsql native linker [CHAR]
plsql_native_make_file_name plsql native compilation make file [CHAR]
plsql_native_make_utility plsql native compilation make utility [CHAR]
plsql_optimize_level PL/SQL optimize level [NUMBER]
plsql_v2_compatibility PL/SQL version 2.x compatibility flag [TRUE | FALSE]
plsql_warnings PL/SQL compiler warnings settings [CHAR]
See also DBMS_WARNING and DBA_PLSQL_OBJECT_SETTINGS
pre_page_sga Pre-page sga for process [TRUE | FALSE]
processes User processes [NUMBER]
query_rewrite_enabled Allow rewrite of queries using materialized views
if enabled [FORCE | TRUE | FALSE]
query_rewrite_integrity Perform rewrite using materialized views
with desired integrity [STALE_TOLERATED | TRUSTED | ENFORCED]
rdbms_server_dn RDBMS's Distinguished Name [CHAR]
read_only_open_delayed If TRUE delay opening of read only files
until first access [TRUE | FALSE]
recovery_parallelism Number of server processes to use for
parallel recovery [NUMBER]
remote_archive_enable Remote archival enable setting [RECEIVE[,SEND] | FALSE | TRUE]
remote_dependencies_mode Remote-procedure-call dependencies mode
parameter [TIMESTAMP | SIGNATURE]
remote_listener Remote listener [CHAR]
remote_login_passwordfile Use a password file [NONE | SHARED | EXCLUSIVE]
remote_os_authent Allow non-secure remote clients to use
auto-logon accounts [TRUE | FALSE]
remote_os_roles Allow non-secure remote clients to use
os roles [TRUE | FALSE]
replication_dependency_tracking Tracking dependency for Replication
parallel propagation [TRUE | FALSE]
resource_limit Master switch for resource limit [TRUE | FALSE]
resource_manager_plan Resource mgr top plan [Plan_Name]
resumable_timeout Set resumable_timeout, seconds [NUMBER]
rollback_segments Undo segment list [CHAR]
row_locking Row-locking [ALWAYS | DEFAULT | INTENT] (Default=always)
serial_reuse Reuse the frame segments [DISABLE | SELECT|DML|PLSQL|ALL|NULL]
serializable Serializable [Internal]
service_names Service names supported by the instance [CHAR]
session_cached_cursors Number of cursors to save in the session
cursor cache [NUMBER]
session_max_open_files Maximum number of open files allowed per
session [NUMBER]
sessions User and system sessions [NUMBER]
sga_max_size Max total SGA size [bytes]
sga_target Target size of SGA [bytes]
shadow_core_dump Core Size for Shadow Processes [PARTIAL | FULL | NONE]
shared_memory_address SGA starting address (low order 32-bits
on 64-bit platforms) [NUMBER]
shared_pool_reserved_size Size in bytes of reserved area of shared
pool [bytes]
shared_pool_size Size in bytes of shared pool [bytes]
shared_server_sessions Max number of shared server sessions [NUMBER]
shared_servers Number of shared servers to start up [NUMBER]
skip_unusable_indexes Skip unusable indexes if set to true [TRUE | FALSE]
sort_area_retained_size Size of in-memory sort work area
retained between fetch calls [bytes]
sort_area_size Size of in-memory sort work area [bytes]
smtp_out_server utl_smtp server and port configuration parameter [server_clause]
spfile Server parameter file [CHAR]
sp_name Service Provider Name [CHAR]
sql92_security Require select privilege for searched
update/delete [TRUE | FALSE]
sql_trace Enable SQL trace [TRUE | FALSE]
sqltune_category Category qualifier for applying hintsets [CHAR]
sql_version Sql language version parameter for
compatibility issues [CHAR]
standby_archive_dest Standby database archivelog destination
text string ['Path_to_directory']
standby_file_management If auto then files are created/dropped
automatically on standby [MANUAL | AUTO]
star_transformation_enabled Enable the use of star transformation
[TRUE | FALSE | DISABLE_TEMP_TABLE]
statistics_level Statistics level [ALL | TYPICAL | BASIC]
streams_pool_size Size in bytes of the streams pool [bytes]
tape_asynch_io Use asynch I/O requests for tape devices [TRUE | FALSE]
thread Redo thread to mount [NUMBER]
timed_os_statistics Internal os statistic gathering interval
in seconds [NUMBER]
timed_statistics Maintain internal timing statistics [TRUE | FALSE]
trace_enabled Enable KST tracing (Internal parameter) [TRUE | FALSE]
tracefile_identifier Trace file custom identifier [CHAR]
transaction_auditing Transaction auditing records generated
in the redo log [TRUE | FALSE]
transactions Max. number of concurrent active
transactions [NUMBER]
transactions_per_rollback_segment Number of active transactions per
rollback segment [NUMBER]
undo_management Instance runs in SMU mode if TRUE, else
in RBU mode [MANUAL | AUTO]
undo_retention Undo retention in seconds [NUMBER]
undo_suppress_errors Suppress RBU errors in SMU mode [TRUE | FALSE]
undo_tablespace Use or switch undo tablespace [Undo_tbsp_name]
use_indirect_data_buffers Enable indirect data buffers (very large
SGA on 32-bit platforms [TRUE | FALSE]
user_dump_dest User process dump directory ['Path_to_directory']
utl_file_dir utl_file accessible directories list
utl_file_dir='Path1', 'Path2'..
or
utl_file_dir='Path1' # Must be
utl_file_dir='Path2' # consecutive entries
workarea_size_policy Policy used to size SQL working areas [MANUAL | AUTO]
Notes
All Byte values can also be specified in K or M or G
e.g. you can enter 8388608 or 8192 K or 8M
All directory paths follow standard notation i.e UNIX 'quotes' or Windows "double quotes"
The default value for many of these parameters does vary across Operating System platforms.
Some parameters can be modified immediately with ALTER SYSTEM, and some can be modified for a single session with ALTER SESSION.
You can use a query like the one below to see the current value at any time:
select name,value,description
from v$parameter
where name='Some_Parameter'
'Internal' parameters (starting with _ ) should not be modified unless advised by Oracle Support.
New parameters in 9.2
audit_sys_operations, dg_broker_start, dg_broker_config_file_n, file_mapping, filesystem_io_options, log_parallelism, olap_page_pool_size, optimizer_dynamic_sampling, statistics_level.
New parameters in 10G
asm_diskgroups, asm_diskstring, asm_power_limit, create_stored_outlines, db_flashback_retention_target, db_recovery_file_dest, db_recovery_file_dest_size, db_unique_name, ddl_wait_for_locks, fileio_network_adapters, gcs_server_processes, instance_type, ldap_directory_access, log_archive_config, log_archive_local_first, plsql_code_type, plsql_debug, plsql_optimize_level, plsql_warnings, resumable_timeout, sga_target, skip_unusable_indexes, smtp_out_server, sp_name, sqltune_category, streams_pool_size.
How to change Parameters
Use the ALTER SYSTEM command to set parameters:
ALTER SYSTEM set parameter = value SCOPE = MEMORY;
ALTER SYSTEM set parameter = value SCOPE = SPFILE;
ALTER SYSTEM set parameter = value SCOPE = BOTH;
-- In memory. This affects the database now; but will not remain after a
restart.
-- SPFILE. This does not change the instance immediately, but it will modify
the SPFILE so will take effect after a restart.
-- Memory and SPFILE - changes the current instance as well as the SPFILE.
In Oracle9i, the SPFILE is stored in a binary format on the server. You no longer need to have a local copy to start the database remotely.
Changes made via the ALTER SYSTEM command may
now persist across server restarts - without any need to remember to update
the init.ora file.
An SPFILE (binary) can be created via the command:
CREATE SPFILE = 'filename'
FROM PFILE ='pfilename';
A PFILE (old style text init.ORA) can be created via the
command:
CREATE PFILE = 'pfilename'
FROM SPFILE = 'filename';
Using the above you can
export all parameters to a text file, make changes, and then create
a new
Binary SPFILE.
To startup a database with a specific PFILE, use the pfile=
option in the startup command:
SQL> startup pfile=filename
Related
V$PARAMETER
ALTER SYSTEM
Links
OraFAQ - List of all parameter settings
(including early versions)
Oracle Memory Architecture (Powerpoint)
Oracle 8i
Memory - older init.ora settings
