INITIALIZATION PARAMETERS

Server Parameters for Oracle 9i and 10g
Green = New in 10g
Red = Valid in 9i but dropped (or hidden) in 10g
Bold = Static Parameter - change in Pfile/SPfile
Normal= Dynamic Parameter - change in Pfile/SPfile/ALTER SYSTEM /SESSION
PARAMETER DESCRIPTION
------------------------------ ----------------------------------------
ACTIVE_INSTANCE_COUNT = int Active instances in the cluster
AQ_TM_PROCESSES = int Number of AQ Time Managers to start
ARCHIVE_LAG_TARGET = int Max no. seconds of redos the standby could lose
asm_diskgroups = string Disk groups to mount automatically
asm_diskstring = string Disk set locations for discovery
asm_power_limit = int Number of processes for disk rebalancing
AUDIT_FILE_DEST = 'directory' Directory in which auditing files are to reside
AUDIT_SYS_OPERATIONS = {TRUEFALSE}
AUDIT_TRAIL = {NONE FALSE DB TRUE OS} Enable system auditing 9i
AUDIT_TRAIL = {NONE DB DB_EXTENDED OS} Enable system auditing 10g
BACKGROUND_CORE_DUMP = {PARTIAL FULL}
BACKGROUND_DUMP_DEST = 'path or directory'
BACKUP_TAPE_IO_SLAVES = {TRUE FALSE} DEFERRED
BITMAP_MERGE_AREA_SIZE = int Memory for BITMAP MERGE
BLANK_TRIMMING = {TRUEFALSE}
CIRCUITS = int
CLUSTER_DATABASE = {TRUEFALSE} If TRUE startup in cluster database mode
CLUSTER_DATABASE_INSTANCES = int
CLUSTER_INTERCONNECTS = ipaddr [:ipaddr...] Interconnects for RAC use
COMMIT_POINT_STRENGTH = int
COMPATIBLE = release_number [CHAR: 9.2.0.0.0]
CONSTRAINT[S] = { IMMEDIATE DEFERRED DEFAULT }
CONTROL_FILE_RECORD_KEEP_TIME = int Time in Days
CONTROL_FILES =filename [,filename [...] ]
CORE_DUMP_DEST = 'text'
CPU_COUNT = int
CREATE_BITMAP_AREA_SIZE = int
CREATE_STORED_OUTLINES = {TRUE FALSE 'category_name' } [NOOVERRIDE]
CURSOR_SHARING = {SIMILAR EXACT FORCE}
CURSOR_SPACE_FOR_TIME = {TRUEFALSE}
CURRENT_SCHEMA = schema Change the current schema of the session
DB_2k_cache_size = int bytes Size of cache for 2K buffers
DB_4k_cache_size = int bytes Size of cache for 4K buffers
DB_8k_cache_size = int bytes Size of cache for 8K buffers
DB_16k_cache_size = int bytes Size of cache for 16K buffers
DB_32k_cache_size = int bytes Size of cache for 32K buffers
DB_BLOCK_BUFFERS = int Deprecated in favour of DB_CACHE_ SIZE
DB_BLOCK_CHECKING = {TRUE FALSE} DEFERRED
DB_BLOCK_CHECKSUM = {TRUE FALSE}
DB_BLOCK_SIZE = int [bytes] Do not alter after db creation
DB_CACHE_ADVICE = {ON READY OFF}
DB_CACHE_SIZE = int [bytes]
DB_CREATE_FILE_DEST = directory
DB_CREATE_ONLINE_LOG_DEST_n = directory (where n = 1-5)
Default locn for Oracle-managed control files and online redo logs.
DB_DOMAIN = domain_name Directory part of global database name
DB_FILE_MULTIBLOCK_READ_COUNT = int
DB_FILE_NAME_CONVERT = [(]'dbfile1' , 'dbfile2'...[)]
Datafile name convert patterns and strings
for standby/clone db [old string, new string]
DB_FILES = int
db_flashback_retention_target = int Max Flashback Database log retention (minutes)
DB_KEEP_CACHE_SIZE = int [bytes]
DB_NAME = database_name
db_recovery_file_dest = string Default database recovery file location
db_recovery_file_dest_size = int Database recovery files size limit
DB_RECYCLE_CACHE_SIZE = int [bytes]
db_unique_name = string Database Unique Name
DB_WRITER_PROCESSES = int Number of background database writer
processes to start
DBLINK_ENCRYPT_LOGIN = {TRUEFALSE} Enforce password encryption for distributed login
DBWR_IO_SLAVES = int
DDL_WAIT_FOR_LOCKS = {TRUEFALSE} Disable NOWAIT DML lock acquisitions
DG_BROKER_CONFIG_FILEn = filename (where n = 1 or 2)
DG_BROKER_START = {TRUEFALSE}
DISK_ASYNCH_IO = {TRUEFALSE}
DISPATCHERS = 'dispatch_clause' (see SQL ref manual for detail)(MTS_Dispatchers in Ora 8/9)
DISTRIBUTED_LOCK_TIMEOUT = int
DML_LOCKS = int One for each table modified in a transaction
DRS_START = {TRUEFALSE} Start DG Broker monitor (DMON process)
ERROR_ON_OVERLAP_TIME = {TRUE FALSE}
ENQUEUE_RESOURCES = int Resources for enqueues
EVENT = debug_string Debug event control
FAL_CLIENT = string Fetch archive log Client
FAL_SERVER = string Fetch archive log Server
FAST_START_IO_TARGET = int Upper bound on recovery reads(Deprecated)
FAST_START_MTTR_TARGET = int
FAST_START_PARALLEL_ROLLBACK = {FALSE LOW HIGH}
Max number of parallel recovery slaves
FILE_MAPPING = {TRUEFALSE}
FILEIO_NETWORK_ADAPTERS = char Network Adapters for File I/O
FILESYSTEMIO_OPTIONS = {none setall directIO asynch}
FIXED_DATE = {'YYYY_MM_DD_HH24_MI-SS' 'date in default format'}
Fix SYSDATE value for debugging
GC_FILES_TO_LOCKS = '{file_list=lock_count[!blocks][EACH][:...]}'
RAC/OPS - lock granularity number of
global cache locks per file (DFS)
GCS_SERVER_PROCESSES = int Number of background gcs server processes to start
GLOBAL_CONTEXT_POOL_SIZE = {1 MB int MB}
GLOBAL_NAMES = {TRUE FALSE} Enforce that database links have same
name as remote database
HASH_AREA_SIZE = int Size of in-memory hash work area (Shared Server)
HASH_JOIN_ENABLED = {TRUEFALSE}
HI_SHARED_MEMORY_ADDRESS = int SGA starting address (high order 32-bits
on 64-bit platforms)
HS_AUTOREGISTER = {TRUE FALSE} Enable automatic server DD updates in HS
agent self-registration
IFILE = parameter_file_name Include file in init.ora
INSTANCE = int Connect to a different RAC instance
INSTANCE_GROUPS = group_name [,group_name ... ]
INSTANCE_NAME = instance_id
INSTANCE_NUMBER = int
INSTANCE_TYPE = {RDBMSASM} Type of instance to be executed
RDBMS or Automated Storage Management
ISOLATION_LEVEL = {SERIALIZABLE READ COMMITTED}
JAVA_MAX_SESSIONSPACE_SIZE = int [bytes]
JAVA_POOL_SIZE = int [bytes]
JAVA_SOFT_SESSIONSPACE_LIMIT = int
JOB_QUEUE_PROCESSES = int
LARGE_POOL_SIZE = int [bytes]
LICENSE_MAX_SESSIONS = int Maximum number of non-system user sessions
(concurrent licensing)
LICENSE_MAX_USERS = int Maximum number of named users that can be created
(named user licensing)
LICENSE_SESSIONS_WARNING = int Warning level for number of non-system
user sessions
LOCAL_LISTENER = network_name Define which listeners instances register with
LOCK_NAME_SPACE = namespace Used for generating lock names for standby/primary database
assign each a unique name space
LOCK_SGA = {TRUE FALSE}
LOG_ARCHIVE_CONFIG = [SENDNOSEND] [RECEIVENORECEIVE] [ DG_CONFIG]
LOG_ARCHIVE_DEST = string
LOG_ARCHIVE_DEST_n = {null_string
{LOCATION=local_pathname SERVICE=tnsnames_service}
[MANDATORY OPTIONAL] [REOPEN[=integer]]}
LOG_ARCHIVE_DEST_STATE_n = {ENABLE DEFER} (n = 1-10)
LOG_ARCHIVE_DUPLEX_DEST = string
LOG_ARCHIVE_FORMAT = string [CHAR: "MyApp%S.ARC"]
LOG_ARCHIVE_LOCAL_FIRST = {TRUEFALSE} Establish EXPEDITE attribute default value
LOG_ARCHIVE_MAX_PROCESSES = int
LOG_ARCHIVE_MIN_SUCCEED_DEST = int Minimum number of archive destinations
that must succeed
LOG_ARCHIVE_START = {TRUE FALSE}
LOG_ARCHIVE_TRACE = int Tracing level for Archive logs
LOG_BUFFER = int bytes Redo circular buffer size
LOG_CHECKPOINT_INTERVAL = int Checkpoint threshold, # redo blocks
LOG_CHECKPOINT_TIMEOUT = int Checkpoint threshold, maximum time interval between
checkpoints in seconds
LOG_CHECKPOINTS_TO_ALERT = {TRUEFALSE} Log checkpoint begin/end to alert file
LOG_FILE_NAME_CONVERT = ['old string','new string']
Convert patterns/strings for standby/clone db
LOG_PARALLELISM = int Number of log buffer strands
LOGMNR_MAX_PERSISTENT_SESSIONS = int Maximum no of threads to mine
MAX_DISPATCHERS = int Max number of dispatchers
MAX_DUMP_FILE_SIZE = {size bytesUNLIMITED} [DEFERRED]
MAX_ENABLED_ROLES = int Max number of roles a user can have enabled
MAX_ROLLBACK_SEGMENTS = int Max number of rollback segments in SGA cache
MAX_SHARED_SERVERS = int Max number of shared servers]
mts_circuits = int Max number of circuits (10g see CIRCUITS)
mts_dispatchers Specifications of dispatchers (10g see DISPATCHERS)
MTS_LISTENER_ADDRESS Address(es) of network listener [CHAR]
mts_max_dispatchers Max number of dispatchers (10g see MAX_DISPATCHERS)
mts_max_servers Max number of shared servers (10g see MAX_SHARED_SERVERS)
MTS_MULTIPLE_LISTENERS = {TRUEFALSE} Are multiple listeners enabled?
MTS_SERVERS = int Number of shared servers to start up [NUMBER]
mts_service = string Service supported by dispatchers [CHAR]
mts_sessions = int max number of shared server sessions [NUMBER]

nls_calendar ='string' NLS calendar system name (Default=GREGORIAN)
nls_comp = {BINARY ANSI} NLS comparison, Enterprise Edition
nls_currency ='string' NLS local currency symbol
nls_date_format ='format' NLS Oracle date format
nls_date_language =language NLS date language name (Default=AMERICAN)]
nls_dual_currency = currency_symbol
nls_iso_currency = territory Override the default set by NLS_TERRITORY
nls_language = language NLS language name (session default)
nls_length_semantics = {BYTECHAR}} Default when creating new columns
nls_nchar_conv_excp = {TRUEFALSE} Raise an exception instead of
allowing an implicit conversion
nls_numeric_characters ="decimal_character group_separator"
nls_sort = {BINARY linguistic_def} Case-sensitive or insensitive sort
linguistic_def may be BINARY, BINARY_CI, BINARY_AI,
GERMAN, GERMAN_CI, etc
nls_territory = territory Territory name (country settings)
nls_time_format =time_format Time format
nls_time_tz_format = time_format Time with timezone format
nls_timestamp_format = time_format Timestamp format
nls_timestamp_tz_format = time_format Timestamp with timezone format

O7_DICTIONARY_ACCESSIBILITY = {TRUE FALSE} Allow Dictionary Access (as in Ora V7 )
OBJECT_CACHE_MAX_SIZE_PERCENT = int DEFERRED Space for application objects Max
OBJECT_CACHE_OPTIMAL_SIZE = int DEFERRED Space for application objects Min
OLAP_PAGE_POOL_SIZE =int bytes
OPEN_CURSORS = int Max # cursors per session
OPEN_LINKS = int Max # open links per Session
OPEN_LINKS_PER_INSTANCE = int Max # open links per instance
OPTIMIZER_DYNAMIC_SAMPLING = int
OPTIMIZER_FEATURES_ENABLE = {8.0.08.0.38.0.48.0.58.0.68.0.78.1.08.1.38.1.48.1.58.1.68.1.79.0.09.0.19.2.0}
Configure qry optimiser based on an Oracle release No.
OPTIMIZER_INDEX_CACHING = int Percent to cache (favour nested loop joins & IN-list)
OPTIMIZER_INDEX_COST_ADJ = int Adjust the cost of index vs FTS
OPTIMIZER_MAX_PERMUTATIONS = int Max join permutations per qry block
OPTIMIZER_MODE = [RULE CHOOSE FIRST_ROWS ALL_ROWS]
oracle_trace_collection_name =collection Name for use by Oracle TRACE
oracle_trace_collection_path =path Path to .cdf & .dat files (ORACLE_HOME/otrace/admin/cdf)
oracle_trace_collection_size =int bytes Max trace file size
oracle_trace_enable = {TRUEFALSE} Enable Oracle Trace
oracle_trace_facility_name ={ORACLED ORACLEE ORACLESM ORACLEC} TRACE event set
oracle_trace_facility_path =path TRACE definition files: ORACLE_HOME/otrace/admin/fdf/
OS_AUTHENT_PREFIX = prefix Prefix for auto-logon accounts [string]
OS_ROLES = {TRUEFALSE} Retrieve roles from the operating system
PARALLEL_ADAPTIVE_MULTI_USER = {TRUE FALSE} Tune degree of parallelism
PARALLEL_AUTOMATIC_TUNING = {TRUEFALSE} Automatic tuning
PARALLEL_EXECUTION_MESSAGE_SIZE = int bytes Message buffer size
PARALLEL_INSTANCE_GROUP = 'group' RAC: Limit instances used
PARALLEL_MAX_SERVERS = int
PARALLEL_MIN_PERCENT = int Min percent of threads required for
parallel query
PARALLEL_MIN_SERVERS = int
PARALLEL_SERVER = [TRUE FALSE] Startup in parallel server mode
PARALLEL_SERVER_instances = int No. of instances (used for sizing SGA)
PARALLEL_THREADS_PER_CPU = int
PARTITION_VIEW_ENABLED = {TRUEFALSE} Deprecated (use partition TABLES)
PGA_AGGREGATE_TARGET = int bytes Automatically size the SQL working area
plsql_code_type ={INTERPRETED NATIVE} Code-type
PLSQL_COMPILER_FLAGS = { [DEBUG NON_DEBUG] [INTERPRETED NATIVE] }
plsql_debug ={TRUE FALSE}
plsql_native_c_compiler
plsql_native_library_dir = ['Path_to_directory']
plsql_native_library_subdir_count = int
plsql_native_linker =path Path to linker
plsql_native_make_file_name =path Pathname of make file
plsql_native_make_utility =path Pathname of make utility
plsql_optimize_level Optimize level
PLSQL_V2_COMPATIBILITY = {TRUE FALSE} [DEFERRED]
plsql_warnings =string Compiler warnings settings
See also DBMS_WARNING and DBA_PLSQL_OBJECT_SETTINGS
PRE_PAGE_SGA = {TRUEFALSE} Pre-page sga for process
PROCESSES = int User processes
QUERY_REWRITE_ENABLED = {FORCE TRUE FALSE} [DEFERRED NOOVERRIDE]
QUERY_REWRITE_INTEGRITY = {ENFORCED TRUSTED STALE_TOLERATED}
RDBMS_SERVER_DN = Distinguished Name
READ_ONLY_OPEN_DELAYED = {TRUE FALSE} Delay opening read_only files until first access
RECOVERY_PARALLELISM = int Server processes to use for parallel recovery
REMOTE_ARCHIVE_ENABLE = [RECEIVE[,SEND] FALSE TRUE]
Enable or disable sending archived redo logs to/from remote destinations
REMOTE_DEPENDENCIES_MODE = {TIMESTAMP SIGNATURE}
Remote-procedure-call dependencies mode
REMOTE_LISTENER =network_name
REMOTE_LOGIN_PASSWORDFILE ={NONE SHARED EXCLUSIVE} Use a password file
REMOTE_OS_AUTHENT = {TRUE FALSE} Allow non-secure remote clients to use
auto-logon accounts
REMOTE_OS_ROLES = {TRUE FALSE} Allow non-secure remote clients to use os roles
REPLICATION_DEPENDENCY_TRACKING = {TRUE FALSE}
RESOURCE_LIMIT = {TRUE FALSE} Master switch for resource limit
RESOURCE_MANAGER_PLAN = plan_name Turn on Resource Manager plan
resumable_timeout =seconds Set resumable_timeout
ROLLBACK_SEGMENTS = (rbs1 [, rbs2] ... )
ROW_LOCKING = [ALWAYS DEFAULT INTENT]
SERIAL_REUSE = {DISABLE SELECT DML PLSQL ALL} Cursor memmory management
SERVICE_NAMES = db_service_name [,db_service_name [,...] ]
SESSION_CACHED_CURSORS = int Session cursors to cache
SESSION_MAX_OPEN_FILES = int Max no. of BFiles (LOB) each session can open
SESSIONS = int Max no. of user and system sessions
SGA_MAX_SIZE =int bytes Initial SGA size
sga_target = int bytes Target size of SGA
SHADOW_CORE_DUMP = {PARTIAL FULL NONE} Include SGA in core file
SHARED_MEMORY_ADDRESS = int SGA starting address (platform specific)
SHARED_POOL_RESERVED_SIZE = int bytes Reserved area of shared pool
SHARED_POOL_SIZE = int Size of shared pool
SHARED_SERVERS = int Number of shared servers to start up (MTS)
SHARED_SERVER_SESSIONS = int Max number of shared server sessions
SKIP_UNUSABLE_INDEXES = {TRUE FALSE}
smtp_out_server = server_clause utl_smtp server and port configuration parameter
SORT_AREA_RETAINED_SIZE =int bytes [DEFERRED] UGA Memory to retain (Shared Server)
SORT_AREA_SIZE = int bytes [DEFERRED] In-memory sort work area (Shared Server)
SORT_MULTIBLOCK_READ_COUNT Obsolete in 9i
SPFILE =spfile_name Parameter file
sp_name =name Service Provider Name
SQL92_SECURITY = {TRUE FALSE} Require select privilege for update/delete
SQL_TRACE = {TRUE FALSE} Enable SQL trace
sqltune_category =category Qualifier for applying hintsets
SQL_VERSION =version Sql language version, for compatibility
STANDBY_ARCHIVE_DEST = 'filespec' Standby database archivelog destination
STANDBY_FILE_MANAGEMENT = {MANUAL AUTO}
Automate file mmanagement on standby DB
STAR_TRANSFORMATION_ENABLED = {TEMP_DISABLE TRUE FALSE}
STATISTICS_LEVEL = {ALL TYPICAL BASIC} Collect Statistics
streams_pool_size = int bytes Size of the streams pool
TAPE_ASYNCH_IO = {TRUE FALSE} Allow I/O requests to tape devices at the same time as CPU processing
THREAD =int Redo thread to use (RAC)
TIMED_OS_STATISTICS = int Gather OS statistics every x seconds
TIMED_STATISTICS = {TRUE FALSE} Collect time statistics
TIME_ZONE = '[+ -] hh:mm' LOCAL DBTIMEZONE 'time_zone_region'
TRACE_ENABLED = {TRUE FALSE} Trace execution path (Internal use only-Oracle support services)
TRACEFILE_IDENTIFIER = "traceid" Trace file custom identifier
TRANSACTION_AUDITING = {TRUE FALSE} [DEFERRED]
TRANSACTIONS = int Max. number of concurrent active transactions
TRANSACTIONS_PER_ROLLBACK_SEGMENT = int
UNDO_MANAGEMENT = {MANUAL AUTO} Undo space management mode (Manual=rollback segs)
UNDO_RETENTION = int Undo retention in second
UNDO_SUPPRESS_ERRORS = {TRUE FALSE} Suppress RBU errors in SMU mode
UNDO_TABLESPACE =undoname Select an undo tablespace
USE_INDIRECT_DATA_BUFFERS = {TRUEFALSE} Configure SGA Memory cache for >4Gb RAM
USE_PRIVATE_OUTLINES = {TRUE FALSE category_name }
USE_STORED_OUTLINES = { TRUE FALSE category_name} [NOOVERRIDE]
USER_DUMP_DEST = 'directory_name' User process dump 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 = {AUTO MANUAL} Policy used to size SQL working areas

http://www.ss64.com/orasyntax/initora.html