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_
n
K_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_FILE
n
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