1. Database Reference
  2. Initialization Parameters
  3. Initialization Parameters
  4. Changing Parameter Values in a Parameter File

1.4 Changing Parameter Values in a Parameter File

You can change the value of a parameter in a parameter file in several ways.

  • By editing an initialization parameter file

    In most cases, the new value takes effect the next time you start an instance of the database.

  • By issuing an ALTER SYSTEM SET ... SCOPE=SPFILE statement to update a server parameter file

  • By issuing an ALTER SYSTEM RESET statement to clear an initialization parameter value.

    See Also:

    Oracle Database Administrator’s Guide for more information about using the ALTER SYSTEM RESET statement to clear initialization parameter values

1.4.1 Parameters by Functional Category

This section lists initialization parameters by their functional category.

1.4.2 Modifiable Parameters

You can modify some initialization parameters using the ALTER SESSION or ALTER SYSTEM statements while an instance is running.

Use the following syntax to modify initialization parameters:

ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value [DEFERRED]

Whenever a parameter is modified using the ALTER SYSTEM statement, the Oracle Database records the statement that modifies the parameter in the alert log.

The ALTER SESSION statement changes the value of the specified parameter for the duration of the session that invokes the statement. The value of the parameter does not change for other sessions in the instance. The value of the following initialization parameters can be changed with ALTER SESSION:

  • APPROX_FOR_AGGREGATION
  • APPROX_FOR_COUNT_DISTINCT
  • APPROX_FOR_PERCENTILE
  • ASM_DISKSTRING
  • ASM_POWER_LIMIT
  • COMMIT_LOGGING
  • COMMIT_WAIT
  • COMMIT_WRITE
  • CONTAINERS_PARALLEL_DEGREE
  • CREATE_STORED_OUTLINES
  • CURSOR_BIND_CAPTURE_DESTINATION
  • CURSOR_INVALIDATION
  • CURSOR_SHARING
  • DB_BLOCK_CHECKING
  • DB_CREATE_FILE_DEST
  • DB_CREATE_ONLINE_LOG_DEST_n
  • DB_FILE_MULTIBLOCK_READ_COUNT
  • DB_FILE_NAME_CONVERT
  • DB_INDEX_COMPRESSION_INHERITANCE
  • DB_SECUREFILE
  • DB_UNRECOVERABLE_SCN_TRACKING
  • DDL_LOCK_TIMEOUT
  • DEFAULT_SHARING
  • DEFERRED_SEGMENT_CREATION
  • DST_UPGRADE_INSERT_CONV
  • ENABLE_DDL_LOGGING
  • GLOBAL_NAMES
  • HASH_AREA_SIZE
  • HEAT_MAP
  • INMEMORY_CLAUSE_DEFAULT
  • INMEMORY_QUERY
  • JAVA_JIT_ENABLED
  • LOG_ARCHIVE_DEST_n
  • LOG_ARCHIVE_DEST_STATE_n
  • LOG_ARCHIVE_MIN_SUCCEED_DEST
  • MAX_DUMP_FILE_SIZE
  • NLS_CALENDAR
  • NLS_COMP
  • NLS_CURRENCY
  • NLS_DATE_FORMAT
  • NLS_DATE_LANGUAGE
  • NLS_DUAL_CURRENCY
  • NLS_ISO_CURRENCY
  • NLS_LANGUAGE
  • NLS_LENGTH_SEMANTICS
  • NLS_NCHAR_CONV_EXCP
  • NLS_NUMERIC_CHARACTERS
  • NLS_SORT
  • NLS_TERRITORY
  • NLS_TIMESTAMP_FORMAT
  • NLS_TIMESTAMP_TZ_FORMAT
  • OBJECT_CACHE_MAX_SIZE_PERCENT
  • OBJECT_CACHE_OPTIMAL_SIZE
  • OLAP_PAGE_POOL_SIZE
  • OPTIMIZER_ADAPTIVE_PLANS
  • OPTIMIZER_ADAPTIVE_REPORTING_ONLY
  • OPTIMIZER_ADAPTIVE_STATISTICS
  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
  • OPTIMIZER_DYNAMIC_SAMPLING
  • OPTIMIZER_FEATURES_ENABLE
  • OPTIMIZER_INDEX_CACHING
  • OPTIMIZER_INDEX_COST_ADJ
  • OPTIMIZER_INMEMORY_AWARE
  • OPTIMIZER_MODE
  • OPTIMIZER_USE_INVISIBLE_INDEXES
  • OPTIMIZER_USE_PENDING_STATISTICS
  • OPTIMIZER_USE_SQL_PLAN_BASELINES
  • PARALLEL_DEGREE_LIMIT
  • PARALLEL_DEGREE_POLICY
  • PARALLEL_FORCE_LOCAL
  • PARALLEL_INSTANCE_GROUP
  • PARALLEL_MIN_PERCENT
  • PARALLEL_MIN_TIME_THRESHOLD
  • PDB_FILE_NAME_CONVERT
  • PDB_LOCKDOWN
  • PLSCOPE_SETTINGS
  • PLSQL_CCFLAGS
  • PLSQL_CODE_TYPE
  • PLSQL_DEBUG
  • PLSQL_OPTIMIZE_LEVEL
  • PLSQL_V2_COMPATIBILITY
  • PLSQL_WARNINGS
  • QUERY_REWRITE_ENABLED
  • QUERY_REWRITE_INTEGRITY
  • RECYCLEBIN
  • REMOTE_DEPENDENCIES_MODE
  • RESULT_CACHE_MODE
  • RESULT_CACHE_REMOTE_EXPIRATION
  • RESUMABLE_TIMEOUT
  • SESSION_CACHED_CURSORS
  • SKIP_UNUSABLE_INDEXES
  • SMTP_OUT_SERVER
  • SORT_AREA_RETAINED_SIZE
  • SORT_AREA_SIZE
  • SPATIAL_VECTOR_ACCELERATION
  • SQL_TRACE
  • SQLTUNE_CATEGORY
  • STAR_TRANSFORMATION_ENABLED
  • STATISTICS_LEVEL
  • TEMP_UNDO_ENABLED
  • TIMED_OS_STATISTICS
  • TIMED_STATISTICS
  • TRACEFILE_IDENTIFIER
  • WORKAREA_SIZE_POLICY
  • XML_DB_EVENTS

The ALTER SYSTEM statement without the DEFERRED keyword modifies the global value of the parameter for all sessions in the instance, for the duration of the instance (until the database is shut down). The value of the following initialization parameters can be changed with ALTER SYSTEM:

  • ALLOW_GLOBAL_DBLINKS
  • APPROX_FOR_AGGREGATION
  • APPROX_FOR_COUNT_DISTINCT
  • APPROX_FOR_PERCENTILE
  • AQ_TM_PROCESSES
  • ASM_IO_PROCESSES
  • AWR_SNAPSHOT_TIME_OFFSET
  • ARCHIVE_LAG_TARGET
  • ASM_DISKGROUPS
  • ASM_DISKSTRING
  • ASM_POWER_LIMIT
  • ASM_PREFERRED_READ_FAILURE_GROUPS
  • AUTOTASK_MAX_ACTIVE_PDBS
  • AWR_PDB_AUTOFLUSH_ENABLED
  • BACKGROUND_CORE_DUMP
  • BACKGROUND_DUMP_DEST
  • CIRCUITS
  • COMMIT_LOGGING
  • COMMIT_WAIT
  • COMMIT_WRITE
  • CONNECTION_BROKERS
  • CONTAINERS_PARALLEL_DEGREE
  • CONTROL_FILE_RECORD_KEEP_TIME
  • CONTROL_MANAGEMENT_PACK_ACCESS
  • CORE_DUMP_DEST
  • CPU_COUNT
  • CREATE_STORED_OUTLINES
  • CURSOR_BIND_CAPTURE_DESTINATION
  • CURSOR_INVALIDATION
  • CURSOR_SHARING
  • DATA_GUARD_SYNC_LATENCY
  • DATA_TRANSFER_CACHE_SIZE
  • DB_nK_CACHE_SIZE
  • DB_BIG_TABLE_CACHE_PERCENT_TARGET
  • DB_BLOCK_CHECKING
  • DB_BLOCK_CHECKSUM
  • DB_CACHE_ADVICE
  • DB_CACHE_SIZE
  • DB_CREATE_FILE_DEST
  • DB_CREATE_ONLINE_LOG_DEST_n
  • DB_FILE_MULTIBLOCK_READ_COUNT
  • DB_FLASH_CACHE_FILE
  • DB_FLASH_CACHE_SIZE
  • DB_FLASHBACK_RETENTION_TARGET
  • DB_INDEX_COMPRESSION_INHERITANCE
  • DB_KEEP_CACHE_SIZE
  • DB_LOST_WRITE_PROTECT
  • DB_RECOVERY_FILE_DEST
  • DB_RECOVERY_FILE_DEST_SIZE
  • DB_RECYCLE_CACHE_SIZE
  • DB_SECUREFILE
  • DB_UNRECOVERABLE_SCN_TRACKING
  • DDL_LOCK_TIMEOUT
  • DEFAULT_SHARING
  • DEFERRED_SEGMENT_CREATION
  • DG_BROKER_CONFIG_FILEn
  • DG_BROKER_START
  • DIAGNOSTIC_DEST
  • DISPATCHERS
  • DST_UPGRADE_INSERT_CONV
  • ENABLE_AUTOMATIC_MAINTENANCE_PDB
  • ENABLE_DDL_LOGGING
  • ENABLE_GOLDENGATE_REPLICATION
  • ENABLED_PDBS_ON_STANDBY
  • ENCRYPT_NEW_TABLESPACES
  • FAL_CLIENT
  • FAL_SERVER
  • FAST_START_MTTR_TARGET
  • FAST_START_PARALLEL_ROLLBACK
  • FILE_MAPPING
  • FIXED_DATE
  • GLOBAL_NAMES
  • GLOBAL_TXN_PROCESSES
  • HEAT_MAP
  • HS_AUTOREGISTER
  • INMEMORY_ADG_ENABLED
  • INMEMORY_CLAUSE_DEFAULT
  • INMEMORY_EXPRESSIONS_USAGE
  • INMEMORY_FORCE
  • INMEMORY_MAX_POPULATE_SERVERS
  • INMEMORY_QUERY
  • INMEMORY_SIZE
  • INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT
  • INMEMORY_VIRTUAL_COLUMNS
  • JAVA_JIT_ENABLED
  • JAVA_POOL_SIZE
  • JOB_QUEUE_PROCESSES
  • LARGE_POOL_SIZE
  • LDAP_DIRECTORY_ACCESS
  • LICENSE_MAX_SESSIONS
  • LICENSE_MAX_USERS
  • LICENSE_SESSIONS_WARNING
  • LISTENER_NETWORKS
  • LOCAL_LISTENER
  • LOG_ARCHIVE_CONFIG
  • LOG_ARCHIVE_DEST
  • LOG_ARCHIVE_DEST_n
  • LOG_ARCHIVE_DEST_STATE_n
  • LOG_ARCHIVE_DUPLEX_DEST
  • LOG_ARCHIVE_MAX_PROCESSES
  • LOG_ARCHIVE_MIN_SUCCEED_DEST
  • LOG_ARCHIVE_TRACE
  • LOG_CHECKPOINT_INTERVAL
  • LOG_CHECKPOINT_TIMEOUT
  • LOG_CHECKPOINTS_TO_ALERT
  • LONG_MODULE_ACTION
  • MAX_DATAPUMP_JOBS_PER_PDB
  • MAX_DISPATCHERS
  • MAX_DUMP_FILE_SIZE
  • MAX_IDLE_TIME
  • MAX_IOPS
  • MAX_MBPS
  • MAX_PDBS
  • MAX_SHARED_SERVERS
  • MEMORY_TARGET
  • NLS_LENGTH_SEMANTICS
  • NLS_NCHAR_CONV_EXCP
  • OFS_THREADS
  • OPEN_CURSORS
  • OPTIMIZER_ADAPTIVE_PLANS
  • OPTIMIZER_ADAPTIVE_REPORTING_ONLY
  • OPTIMIZER_ADAPTIVE_STATISTICS
  • OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
  • OPTIMIZER_DYNAMIC_SAMPLING
  • OPTIMIZER_FEATURES_ENABLE
  • OPTIMIZER_INDEX_CACHING
  • OPTIMIZER_INDEX_COST_ADJ
  • OPTIMIZER_INMEMORY_AWARE
  • OPTIMIZER_MODE
  • OPTIMIZER_SECURE_VIEW_MERGING
  • OPTIMIZER_USE_INVISIBLE_INDEXES
  • OPTIMIZER_USE_PENDING_STATISTICS
  • OPTIMIZER_USE_SQL_PLAN_BASELINES
  • OUTBOUND_DBLINK_PROTOCOLS
  • PARALLEL_ADAPTIVE_MULTI_USER
  • PARALLEL_DEGREE_LIMIT
  • PARALLEL_DEGREE_POLICY
  • PARALLEL_FORCE_LOCAL
  • PARALLEL_INSTANCE_GROUP
  • PARALLEL_MAX_SERVERS
  • PARALLEL_MIN_SERVERS
  • PARALLEL_MIN_TIME_THRESHOLD
  • PARALLEL_SERVERS_TARGET
  • PARALLEL_THREADS_PER_CPU
  • PDB_CONVERT_FILE_NAME
  • PDB_LOCKDOWN
  • PGA_AGGREGATE_LIMIT
  • PGA_AGGREGATE_TARGET
  • PLSCOPE_SETTINGS
  • PLSQL_CCFLAGS
  • PLSQL_CODE_TYPE
  • PLSQL_DEBUG
  • PLSQL_OPTIMIZE_LEVEL
  • PLSQL_V2_COMPATIBILITY
  • PLSQL_WARNINGS
  • QUERY_REWRITE_ENABLED
  • QUERY_REWRITE_INTEGRITY
  • REDO_TRANSPORT_USER
  • REMOTE_DEPENDENCIES_MODE
  • REMOTE_LISTENER
  • REMOTE_RECOVERY_FILE_DEST
  • RESOURCE_LIMIT
  • RESOURCE_MANAGER_CPU_ALLOCATION
  • RESOURCE_MANAGER_PLAN
  • RESULT_CACHE_MAX_RESULT
  • RESULT_CACHE_MAX_SIZE
  • RESULT_CACHE_MODE
  • RESULT_CACHE_REMOTE_EXPIRATION
  • RESUMABLE_TIMEOUT
  • SEC_CASE_SENSITIVE_LOGON
  • SEC_PROTOCOL_ERROR_FURTHER_ACTION
  • SEC_PROTOCOL_ERROR_TRACE_ACTION
  • SERVICE_NAMES
  • SGA_MIN_SIZE
  • SGA_TARGET
  • SHADOW_CORE_DUMP
  • SHARED_POOL_SIZE
  • SHARED_SERVER_SESSIONS
  • SHARED_SERVERS
  • SHRD_DUPL_TABLE_REFRESH_RATE
  • SKIP_UNUSABLE_INDEXES
  • SMTP_OUT_SERVER
  • SPATIAL_VECTOR_ACCELERATION
  • SPFILE
  • SQL_TRACE
  • SQLTUNE_CATEGORY
  • STANDBY_ARCHIVE_DEST
  • STANDBY_FILE_MANAGEMENT
  • STAR_TRANSFORMATION_ENABLED
  • STATISTICS_LEVEL
  • STREAMS_POOL_SIZE
  • TEMP_UNDO_ENABLED
  • THREAD
  • TIMED_OS_STATISTICS
  • TIMED_STATISTICS
  • TRACE_ENABLED
  • UNDO_RETENTION
  • UNDO_TABLESPACE
  • UNIFORM_LOG_TIMESTAMP_FORMAT
  • USE_DEDICATED_BROKER
  • USER_DUMP_DEST
  • WORKAREA_SIZE_POLICY
  • XML_DB_EVENTS

The ALTER SYSTEM ... DEFERRED statement does not modify the global value of the parameter for existing sessions, but the value will be modified for future sessions that connect to the database. The value of the following initialization parameters can be changed with ALTER SYSTEM ... DEFERRED:

  • AUDIT_FILE_DEST
  • BACKUP_TAPE_IO_SLAVES
  • OBJECT_CACHE_MAX_SIZE_PERCENT
  • OBJECT_CACHE_OPTIMAL_SIZE
  • OLAP_PAGE_POOL_SIZE
  • RECYCLEBIN
  • SESSION_CACHED_CURSORS
  • SORT_AREA_RETAINED_SIZE
  • SORT_AREA_SIZE

1.4.3 Displaying Current Parameter Values

You can use the SQL*Plus SHOW PARAMETERS statement to see the current settings for initialization parameters.

For example: 

SQL> SHOW PARAMETERS

This statement displays all parameters in alphabetical order, along with their current values.

Enter the following text string to display all parameters having BLOCK in their names: 

SQL> SHOW PARAMETERS BLOCK

You can use the SPOOL command to write the output to a file.

1.4.4 Parameters You Should Not Specify in the Parameter File

You should not specify these types of parameters in your parameter files:

  • Parameters that you never alter unless instructed to do so by Oracle to resolve a problem

  • Derived parameters, which normally do not need altering because their values are calculated automatically by the Oracle database server

1.4.5 When Parameters Are Set Incorrectly

When a parameter is set incorrectly, the effect can be different for different parameters, or based on how low or high the parameter is set.

Some parameters have a minimum setting below which an Oracle instance will not start. For other parameters, setting the value too low or too high may cause Oracle to perform badly, but it will still run. Also, Oracle may convert some values outside the acceptable range to usable levels.

If a parameter value is too low or too high, or you have reached the maximum for some resource, then Oracle returns an error. Frequently, you can wait a short while and retry the operation when the system is not as busy. If a message occurs repeatedly, then you should shut down the instance, adjust the relevant parameter, and restart the instance.