for Oracle 9i and 10g, Green = New in 10g
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 can 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
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.
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.
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.
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
"Happiness, wealth, and success are by-products of goal setting; they cannot be the goal themselves" ~ Denis Waitley, The Joy of Working
Related
Server Parameter Files - Examples - How to Edit
V$PARAMETER
V$SpPARAMETER
ALTER SYSTEM
Server Parameters - OraFaq.com
Oracle Memory Architecture (Powerpoint)