Modifiable Parameters

Some initialization parameters can be modified 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:

  • ASM_DISKSTRING
  • ASM_POWER_LIMIT
  • COMMIT_LOGGING
  • COMMIT_WAIT
  • COMMIT_WRITE
  • CREATE_STORED_OUTLINES
  • CURSOR_BIND_CAPTURE_DESTINATION
  • 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
  • DEFERRED_SEGMENT_CREATION
  • DST_UPGRADE_INSERT_CONV
  • ENABLE_DDL_LOGGING
  • GLOBAL_NAMES
  • HASH_AREA_SIZE
  • HEAT_MAP
  • JAVA_JIT_ENABLED
  • INMEMORY_CLAUSE_DEFAULT
  • INMEMORY_QUERY
  • 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_FEATURES
  • OPTIMIZER_ADAPTIVE_REPORTING_ONLY
  • 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_IO_CAP_ENABLED
  • PARALLEL_MIN_PERCENT
  • PARALLEL_MIN_TIME_THRESHOLD
  • PDB_FILE_NAME_CONVERT
  • 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:

  • AQ_TM_PROCESSES
  • AWR_SNAPSHOT_TIME_OFFSET
  • ARCHIVE_LAG_TARGET
  • ASM_DISKGROUPS
  • ASM_DISKSTRING
  • ASM_POWER_LIMIT
  • ASM_PREFERRED_READ_FAILURE_GROUPS
  • BACKGROUND_DUMP_DEST
  • CIRCUITS
  • COMMIT_LOGGING
  • COMMIT_WAIT
  • COMMIT_WRITE
  • CONNECTION_BROKERS
  • CONTROL_FILE_RECORD_KEEP_TIME
  • CONTROL_MANAGEMENT_PACK_ACCESS
  • CORE_DUMP_DEST
  • CPU_COUNT
  • CREATE_STORED_OUTLINES
  • CURSOR_BIND_CAPTURE_DESTINATION
  • CURSOR_SHARING
  • 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
  • DEFERRED_SEGMENT_CREATION
  • DG_BROKER_CONFIG_FILEn
  • DG_BROKER_START
  • DIAGNOSTIC_DEST
  • DISPATCHERS
  • DST_UPGRADE_INSERT_CONV
  • ENABLE_DDL_LOGGING
  • ENABLE_GOLDENGATE_REPLICATION
  • 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_CLAUSE_DEFAULT
  • INMEMORY_FORCE
  • INMEMORY_MAX_POPULATE_SERVERS
  • INMEMORY_QUERY
  • INMEMORY_SIZE
  • INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT
  • 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
  • MAX_DISPATCHERS
  • MAX_DUMP_FILE_SIZE
  • MAX_SHARED_SERVERS
  • MEMORY_TARGET
  • NLS_LENGTH_SEMANTICS
  • NLS_NCHAR_CONV_EXCP
  • OPEN_CURSORS
  • OPTIMIZER_ADAPTIVE_FEATURES
  • OPTIMIZER_ADAPTIVE_REPORTING_ONLY
  • 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
  • PARALLEL_ADAPTIVE_MULTI_USER
  • PARALLEL_DEGREE_LIMIT
  • PARALLEL_DEGREE_POLICY
  • PARALLEL_FORCE_LOCAL
  • PARALLEL_INSTANCE_GROUP
  • PARALLEL_IO_CAP_ENABLED
  • PARALLEL_MAX_SERVERS
  • PARALLEL_MIN_SERVERS
  • PARALLEL_MIN_TIME_THRESHOLD
  • PARALLEL_SERVERS_TARGET
  • PARALLEL_THREADS_PER_CPU
  • PDB_CONVERT_FILE_NAME
  • 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
  • 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_TARGET
  • SHARED_POOL_SIZE
  • SHARED_SERVER_SESSIONS
  • SHARED_SERVERS
  • 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
  • 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
  • SORT_AREA_RETAINED_SIZE
  • SORT_AREA_SIZE