List of all Oracle Server Parameters
for Oracle 9i and 10g, Green = New in 10g
(//z 2012-5-11 14:07:27 PM is2120@csdn)
Grey = 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 = {TRUE|FALSE} 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 = {TRUE|FALSE} CIRCUITS = int CLUSTER_DATABASE = {TRUE|FALSE} 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 = {TRUE|FALSE} 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 = {TRUE|FALSE} Enforce password encryption for distributed login DBWR_IO_SLAVES = int DDL_WAIT_FOR_LOCKS = {TRUE|FALSE} Disable NOWAIT DML lock acquisitions DG_BROKER_CONFIG_FILEn = filename (where n = 1 or 2) DG_BROKER_START = {TRUE|FALSE} DISK_ASYNCH_IO = {TRUE|FALSE} 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 = {TRUE|FALSE} 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 = {TRUE|FALSE} 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 = {TRUE|FALSE} 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 = {RDBMS|ASM} 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 = [SEND|NOSEND] [RECEIVE|NORECEIVE] [ 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 = {TRUE|FALSE} 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 = {TRUE|FALSE} 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 bytes|UNLIMITED} [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 = {TRUE|FALSE} 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 = {BYTE|CHAR}} Default when creating new columns nls_nchar_conv_excp = {TRUE|FALSE} 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.0|8.0.3|8.0.4|8.0.5|8.0.6|8.0.7|8.1.0|8.1.3|8.1.4|8.1.5|8.1.6|8.1.7|9.0.0|9.0.1|9.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 = {TRUE|FALSE} 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 = {TRUE|FALSE} Retrieve roles from the operating system PARALLEL_ADAPTIVE_MULTI_USER = {TRUE | FALSE} Tune degree of parallelism PARALLEL_AUTOMATIC_TUNING = {TRUE|FALSE} 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 = {TRUE|FALSE} 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 = {TRUE|FALSE} 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 = {TRUE|FALSE} 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
Session & other Parameters
The following parameters are not initialization parameters:
CONSTRAINT, CREATE_STORED_OUTLINES, CURRENT_SCHEMA, ERROR_ON_OVERLAP_TIME, FLAGGER, INSTANCE, ISOLATION_LEVEL , SQL_TRACE?, SKIP_UNUSABLE_INDEXES, TIME_ZONE, USE_PRIVATE_OUTLINES, USE_STORED_OUTLINES.
You cannot set values for these in the parameter file (pfile/spfile)
SQL_TRACE is an initialization parameter, but when changed in a user session, does not update V$PARAMETER.
TRANSACTIONS can be changed in the parameter file or using ALTER SYSTEM…SCOPE=SPFILE
(//z 2012-5-11 14:07:27 PM is2120@csdn)
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 "doublequotes"
The default value for many of these parameters does vary across Operating System platforms.
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, 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, smtp_out_server, sp_name, sqltune_category, streams_pool_size.
New parameters in 11G
asm_preferred_read_failure_groups,client_result_cache_lag,client_result_cache_size,commit_logging,commit_wait,control_management_pack_access,db_lost_write_protect,db_securefile,db_ultra_safe,ddl_lock_timeout,diagnostic_dest,global_txn_processes,java_jit_enabled,ldap_directory_sysauth,memory_max_target,memory_target,optimizer_capture_sql_plan_baselines,optimizer_use_invisible_indexes,optimizer_use_pending_statistics,optimizer_use_sql_plan_baselines,parallel_io_cap_enabled,plscope_settings,redo_transport_user,resource_manager_cpu_allocation,result_cache_max_result,result_cache_max_size, result_cache_mode,result_cache_remote_expiration,sec_case_sensitive_logon,sec_max_failed_login_attempts,sec_protocol_error_further_action,sec_protocol_error_trace_action,sec_return_server_release_banner,xml_db_events
(//z 2012-5-11 14:07:27 PM is2120@csdn)