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_DISKSTRINGASM_POWER_LIMITCOMMIT_LOGGINGCOMMIT_WAITCOMMIT_WRITECREATE_STORED_OUTLINESCURSOR_BIND_CAPTURE_DESTINATIONCURSOR_SHARINGDB_BLOCK_CHECKINGDB_CREATE_FILE_DESTDB_CREATE_ONLINE_LOG_DEST_nDB_FILE_MULTIBLOCK_READ_COUNTDB_FILE_NAME_CONVERTDB_INDEX_COMPRESSION_INHERITANCEDB_SECUREFILEDB_UNRECOVERABLE_SCN_TRACKINGDDL_LOCK_TIMEOUTDEFERRED_SEGMENT_CREATIONDST_UPGRADE_INSERT_CONVENABLE_DDL_LOGGINGGLOBAL_NAMESHASH_AREA_SIZEHEAT_MAPJAVA_JIT_ENABLEDINMEMORY_CLAUSE_DEFAULTINMEMORY_QUERYLOG_ARCHIVE_DEST_nLOG_ARCHIVE_DEST_STATE_nLOG_ARCHIVE_MIN_SUCCEED_DESTMAX_DUMP_FILE_SIZENLS_CALENDARNLS_COMPNLS_CURRENCYNLS_DATE_FORMATNLS_DATE_LANGUAGENLS_DUAL_CURRENCYNLS_ISO_CURRENCYNLS_LANGUAGENLS_LENGTH_SEMANTICSNLS_NCHAR_CONV_EXCPNLS_NUMERIC_CHARACTERSNLS_SORTNLS_TERRITORYNLS_TIMESTAMP_FORMATNLS_TIMESTAMP_TZ_FORMATOBJECT_CACHE_MAX_SIZE_PERCENTOBJECT_CACHE_OPTIMAL_SIZEOLAP_PAGE_POOL_SIZEOPTIMIZER_ADAPTIVE_FEATURESOPTIMIZER_ADAPTIVE_REPORTING_ONLYOPTIMIZER_CAPTURE_SQL_PLAN_BASELINESOPTIMIZER_DYNAMIC_SAMPLINGOPTIMIZER_FEATURES_ENABLEOPTIMIZER_INDEX_CACHINGOPTIMIZER_INDEX_COST_ADJOPTIMIZER_INMEMORY_AWAREOPTIMIZER_MODEOPTIMIZER_USE_INVISIBLE_INDEXESOPTIMIZER_USE_PENDING_STATISTICSOPTIMIZER_USE_SQL_PLAN_BASELINESPARALLEL_DEGREE_LIMITPARALLEL_DEGREE_POLICYPARALLEL_FORCE_LOCALPARALLEL_INSTANCE_GROUPPARALLEL_IO_CAP_ENABLEDPARALLEL_MIN_PERCENTPARALLEL_MIN_TIME_THRESHOLDPDB_FILE_NAME_CONVERTPLSCOPE_SETTINGSPLSQL_CCFLAGSPLSQL_CODE_TYPEPLSQL_DEBUGPLSQL_OPTIMIZE_LEVELPLSQL_V2_COMPATIBILITYPLSQL_WARNINGSQUERY_REWRITE_ENABLEDQUERY_REWRITE_INTEGRITYRECYCLEBINREMOTE_DEPENDENCIES_MODERESULT_CACHE_MODERESULT_CACHE_REMOTE_EXPIRATIONRESUMABLE_TIMEOUTSESSION_CACHED_CURSORSSKIP_UNUSABLE_INDEXESSMTP_OUT_SERVERSORT_AREA_RETAINED_SIZESORT_AREA_SIZESPATIAL_VECTOR_ACCELERATIONSQL_TRACESQLTUNE_CATEGORYSTAR_TRANSFORMATION_ENABLEDSTATISTICS_LEVELTEMP_UNDO_ENABLEDTIMED_OS_STATISTICSTIMED_STATISTICSTRACEFILE_IDENTIFIERWORKAREA_SIZE_POLICYXML_DB_EVENTSThe 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_PROCESSESAWR_SNAPSHOT_TIME_OFFSETARCHIVE_LAG_TARGETASM_DISKGROUPSASM_DISKSTRINGASM_POWER_LIMITASM_PREFERRED_READ_FAILURE_GROUPSBACKGROUND_DUMP_DESTCIRCUITSCOMMIT_LOGGINGCOMMIT_WAITCOMMIT_WRITECONNECTION_BROKERSCONTROL_FILE_RECORD_KEEP_TIMECONTROL_MANAGEMENT_PACK_ACCESSCORE_DUMP_DESTCPU_COUNTCREATE_STORED_OUTLINESCURSOR_BIND_CAPTURE_DESTINATIONCURSOR_SHARINGDB_nK_CACHE_SIZEDB_BIG_TABLE_CACHE_PERCENT_TARGETDB_BLOCK_CHECKINGDB_BLOCK_CHECKSUMDB_CACHE_ADVICEDB_CACHE_SIZEDB_CREATE_FILE_DESTDB_CREATE_ONLINE_LOG_DEST_nDB_FILE_MULTIBLOCK_READ_COUNTDB_FLASH_CACHE_FILEDB_FLASH_CACHE_SIZEDB_FLASHBACK_RETENTION_TARGETDB_INDEX_COMPRESSION_INHERITANCEDB_KEEP_CACHE_SIZEDB_LOST_WRITE_PROTECTDB_RECOVERY_FILE_DESTDB_RECOVERY_FILE_DEST_SIZEDB_RECYCLE_CACHE_SIZEDB_SECUREFILEDB_UNRECOVERABLE_SCN_TRACKINGDDL_LOCK_TIMEOUTDEFERRED_SEGMENT_CREATIONDG_BROKER_CONFIG_FILEnDG_BROKER_STARTDIAGNOSTIC_DESTDISPATCHERSDST_UPGRADE_INSERT_CONVENABLE_DDL_LOGGINGENABLE_GOLDENGATE_REPLICATIONFAL_CLIENTFAL_SERVERFAST_START_MTTR_TARGETFAST_START_PARALLEL_ROLLBACKFILE_MAPPINGFIXED_DATEGLOBAL_NAMESGLOBAL_TXN_PROCESSESHEAT_MAPHS_AUTOREGISTERINMEMORY_CLAUSE_DEFAULTINMEMORY_FORCEINMEMORY_MAX_POPULATE_SERVERSINMEMORY_QUERYINMEMORY_SIZEINMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENTJAVA_JIT_ENABLEDJAVA_POOL_SIZEJOB_QUEUE_PROCESSESLARGE_POOL_SIZELDAP_DIRECTORY_ACCESSLICENSE_MAX_SESSIONSLICENSE_MAX_USERSLICENSE_SESSIONS_WARNINGLISTENER_NETWORKSLOCAL_LISTENERLOG_ARCHIVE_CONFIGLOG_ARCHIVE_DESTLOG_ARCHIVE_DEST_nLOG_ARCHIVE_DEST_STATE_nLOG_ARCHIVE_DUPLEX_DESTLOG_ARCHIVE_MAX_PROCESSESLOG_ARCHIVE_MIN_SUCCEED_DESTLOG_ARCHIVE_TRACELOG_CHECKPOINT_INTERVALLOG_CHECKPOINT_TIMEOUTLOG_CHECKPOINTS_TO_ALERTMAX_DISPATCHERSMAX_DUMP_FILE_SIZEMAX_SHARED_SERVERSMEMORY_TARGETNLS_LENGTH_SEMANTICSNLS_NCHAR_CONV_EXCPOPEN_CURSORSOPTIMIZER_ADAPTIVE_FEATURESOPTIMIZER_ADAPTIVE_REPORTING_ONLYOPTIMIZER_CAPTURE_SQL_PLAN_BASELINESOPTIMIZER_DYNAMIC_SAMPLINGOPTIMIZER_FEATURES_ENABLEOPTIMIZER_INDEX_CACHINGOPTIMIZER_INDEX_COST_ADJOPTIMIZER_INMEMORY_AWAREOPTIMIZER_MODEOPTIMIZER_SECURE_VIEW_MERGINGOPTIMIZER_USE_INVISIBLE_INDEXESOPTIMIZER_USE_PENDING_STATISTICSOPTIMIZER_USE_SQL_PLAN_BASELINESPARALLEL_ADAPTIVE_MULTI_USERPARALLEL_DEGREE_LIMITPARALLEL_DEGREE_POLICYPARALLEL_FORCE_LOCALPARALLEL_INSTANCE_GROUPPARALLEL_IO_CAP_ENABLEDPARALLEL_MAX_SERVERSPARALLEL_MIN_SERVERSPARALLEL_MIN_TIME_THRESHOLDPARALLEL_SERVERS_TARGETPARALLEL_THREADS_PER_CPUPDB_CONVERT_FILE_NAMEPGA_AGGREGATE_LIMITPGA_AGGREGATE_TARGETPLSCOPE_SETTINGSPLSQL_CCFLAGSPLSQL_CODE_TYPEPLSQL_DEBUGPLSQL_OPTIMIZE_LEVELPLSQL_V2_COMPATIBILITYPLSQL_WARNINGSQUERY_REWRITE_ENABLEDQUERY_REWRITE_INTEGRITYREDO_TRANSPORT_USERREMOTE_DEPENDENCIES_MODEREMOTE_LISTENERRESOURCE_LIMITRESOURCE_MANAGER_CPU_ALLOCATIONRESOURCE_MANAGER_PLANRESULT_CACHE_MAX_RESULTRESULT_CACHE_MAX_SIZERESULT_CACHE_MODERESULT_CACHE_REMOTE_EXPIRATIONRESUMABLE_TIMEOUTSEC_CASE_SENSITIVE_LOGONSEC_PROTOCOL_ERROR_FURTHER_ACTIONSEC_PROTOCOL_ERROR_TRACE_ACTIONSERVICE_NAMESSGA_TARGETSHARED_POOL_SIZESHARED_SERVER_SESSIONSSHARED_SERVERSSKIP_UNUSABLE_INDEXESSMTP_OUT_SERVERSPATIAL_VECTOR_ACCELERATIONSPFILESQL_TRACESQLTUNE_CATEGORYSTANDBY_ARCHIVE_DESTSTANDBY_FILE_MANAGEMENTSTAR_TRANSFORMATION_ENABLEDSTATISTICS_LEVELSTREAMS_POOL_SIZETEMP_UNDO_ENABLEDTHREADTIMED_OS_STATISTICSTIMED_STATISTICSTRACE_ENABLEDUNDO_RETENTIONUNDO_TABLESPACEUSE_DEDICATED_BROKERUSER_DUMP_DESTWORKAREA_SIZE_POLICYXML_DB_EVENTSThe 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_DESTBACKUP_TAPE_IO_SLAVESOBJECT_CACHE_MAX_SIZE_PERCENTOBJECT_CACHE_OPTIMAL_SIZEOLAP_PAGE_POOL_SIZERECYCLEBINSORT_AREA_RETAINED_SIZESORT_AREA_SIZE