INITORA File 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_diskgroupsstring Disk groups to mount automatically asm_diskstringstring Disk set locations for discovery asm_power_limitint Number of processes for disk rebalancing AUDIT_FILE_DEST = 'directory' Directory in which auditing files are to reside AUDIT_SYS_OPERATIONS FALSE } AUDIT_TRAIL AUDIT_TRAIL 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 BLANK_TRIMMING = {TRUE|FALSE } CIRCUITS int CLUSTER_DATABASE = {TRUE|FALSE } If TRUE startup in cluster database mode CLUSTER_DATABASE_INSTANCESint 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 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 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_targetint 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_sizeint 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 FALSE } Enforce password encryption for distributed login DBWR_IO_SLAVES int DDL_WAIT_FOR_LOCKS 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_ADAPTERSchar 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_PROCESSESint 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_ADDRESSint 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_GROUPSgroup_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_SPACEnamespace Used for generating lock names for standby/primary database assign each a unique name space LOCK_SGA FALSE } LOG_ARCHIVE_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 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_SEGMENTSint Max number of rollback segments in SGA cache MAX_SHARED_SERVERS = int Max number of shared servers] mts_circuitsint 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 Are multiple listeners enabled? MTS_SERVERSint Number of shared servers to start up [NUMBER] mts_servicestring Service supported by dispatchers [CHAR] mts_sessionsint 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 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_namepath Pathname of make file plsql_native_make_utilitypath Pathname of make utility plsql_optimize_level Optimize level PLSQL_V2_COMPATIBILITY = {TRUE | FALSE } [DEFERRED] plsql_warningsstring Compiler warnings settings See also DBMS_WARNING and DBA_PLSQL_OBJECT_SETTINGS PRE_PAGE_SGA = {TRUE|FALSE } Pre-page sga for process PROCESSESint 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_timeoutseconds 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 = {TRUE | FALSE} smtp_out_server 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 In-memory sort work area (Shared Server) SORT_MULTIBLOCK_READ_COUNT Obsolete in 9i SPFILE =spfile_name Parameter file sp_namename Service Provider Name SQL92_SECURITY = {TRUE | FALSE } Require select privilege for update/delete SQL_TRACE = {TRUE | FALSE } Enable SQL trace sqltune_categorycategory 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_SEGMENTint 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 areasNotes
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.
To see the current value of any parameter:
select name,value,description,issys_modifiable
from v$parameter
where name='Some_Parameter'issys_modifiable, shows the type of parameter:
FALSE = Static parameter that cannot change its value in the lifetime of the instance; shown in bold in the list above.
'Internal' parameters (starting with _ ) should not be modified unless advised by Oracle Support.
Changing a Parameter value
Use the ALTER SYSTEM command to set parameters:
ALTER SYSTEM set parameter = value SCOPE = MEMORY;
parameter = value SCOPE = spfile;
parameter = value SCOPE = BOTH;
-- In memory. This affects the database now; but will not remain after a restart.
Some parameters can be modified immediately with ALTER SYSTEM, and some can be modified for a single session with ALTER SESSION. Static parameters must be modified with scope=spfile
Restoring default Parameter values
For string parameters, setting to an empty string will restore the default.
ALTER SYSTEM SET parameter = '' scope=spfile;
For any parameter the RESET option will restore the default.
ALTER SYSTEM RESET parameter scope=spfile sid='*' ;
note
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.
