You change the value of a parameter in a parameter file in one of the following 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 ... SCOPE=SPFILE statement to remove a parameter from a server parameter file, causing the default value to take effect the next time you start an instance of the database.
The following list shows the initialization parameters by their functional category:
ANSI Compliance
Backup and Restore
BFILEs
Buffer Cache and I/O
CLIENT_RESULT_CACHE_LAGCLIENT_RESULT_CACHE_SIZEDB_nK_CACHE_SIZEDB_BLOCK_BUFFERSDB_BLOCK_SIZEDB_CACHE_ADVICEDB_CACHE_SIZEDB_FILE_MULTIBLOCK_READ_COUNTDB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEDB_WRITER_PROCESSESDBWR_IO_SLAVESDISK_ASYNCH_IODNFS_BATCH_SIZEFILESYSTEMIO_OPTIONSREAD_ONLY_OPEN_DELAYEDRESULT_CACHE_MAX_RESULTRESULT_CACHE_MAX_SIZERESULT_CACHE_MODEUSE_INDIRECT_DATA_BUFFERSCursors and Library Cache
Database/Instance Identification
Diagnostics and Statistics
Distributed, Replication
File Locations, Names, and Sizes
Globalization
Java
Job Queues
License Limits
Memory
Miscellaneous
Networking
Objects and LOBs
OLAP
Optimizer
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINESOPTIMIZER_DYNAMIC_SAMPLINGOPTIMIZER_FEATURES_ENABLEOPTIMIZER_INDEX_CACHINGOPTIMIZER_INDEX_COST_ADJOPTIMIZER_MODEOPTIMIZER_SECURE_VIEW_MERGINGOPTIMIZER_USE_PENDING_STATISTICSOPTIMIZER_USE_SQL_PLAN_BASELINESQUERY_REWRITE_ENABLEDQUERY_REWRITE_INTEGRITYSTAR_TRANSFORMATION_ENABLEDParallel Execution
PL/SQL
PL/SQL Compiler
SGA Memory
Oracle RAC
Redo Logs, Archiving, and Recovery
CONTROL_FILE_RECORD_KEEP_TIMEDB_CREATE_ONLINE_LOG_DEST_nDB_RECOVERY_FILE_DESTDB_RECOVERY_FILE_DEST_SIZEDB_UNRECOVERABLE_SCN_TRACKINGFAST_START_MTTR_TARGETLOG_BUFFERLOG_CHECKPOINT_INTERVALLOG_CHECKPOINT_TIMEOUTLOG_CHECKPOINTS_TO_ALERTLOG_ARCHIVE_CONFIGLOG_ARCHIVE_DEST_nLOG_ARCHIVE_DEST_STATE_nLOG_ARCHIVE_DUPLEX_DESTLOG_ARCHIVE_FORMATLOG_ARCHIVE_MAX_PROCESSESLOG_ARCHIVE_MIN_SUCCEED_DESTLOG_ARCHIVE_TRACEREDO_TRANSPORT_USERRECOVERY_PARALLELISMResource Manager
Security and Auditing
AUDIT_FILE_DESTAUDIT_SYS_OPERATIONSAUDIT_SYSLOG_LEVELAUDIT_TRAILCOMMIT_LOGGINGCOMMIT_WAITO7_DICTIONARY_ACCESSIBILITYOS_AUTHENT_PREFIXOS_ROLESRDBMS_SERVER_DNREMOTE_LOGIN_PASSWORDFILEREMOTE_OS_AUTHENTREMOTE_OS_ROLESSEC_CASE_SENSITIVE_LOGONSEC_MAX_FAILED_LOGIN_ATTEMPTSSEC_PROTOCOL_ERROR_FURTHER_ACTIONSEC_PROTOCOL_ERROR_TRACE_ACTIONSEC_RETURN_SERVER_RELEASE_BANNERSQL92_SECURITYSessions and Processes
Shared Server Architecture
Standby Database
Temporary Sort Space
Transactions
Undo Management
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_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_SECUREFILEDB_UNRECOVERABLE_SCN_TRACKINGDDL_LOCK_TIMEOUTDEFERRED_SEGMENT_CREATIONDST_UPGRADE_INSERT_CONVENABLE_DDL_LOGGINGGLOBAL_NAMESHASH_AREA_SIZEJAVA_JIT_ENABLEDLOG_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_CAPTURE_SQL_PLAN_BASELINESOPTIMIZER_DYNAMIC_SAMPLINGOPTIMIZER_FEATURES_ENABLEOPTIMIZER_INDEX_CACHINGOPTIMIZER_INDEX_COST_ADJOPTIMIZER_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_THRESHOLDPLSCOPE_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_SIZESQL_TRACESQLTUNE_CATEGORYSTAR_TRANSFORMATION_ENABLEDSTATISTICS_LEVELTIMED_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_PROCESSESARCHIVE_LAG_TARGETASM_DISKGROUPSASM_DISKSTRINGASM_POWER_LIMITASM_PREFERRED_READ_FAILURE_GROUPSAWR_SNAPSHOT_TIME_OFFSETBACKGROUND_DUMP_DESTCIRCUITSCOMMIT_LOGGINGCOMMIT_WAITCOMMIT_WRITECONTROL_FILE_RECORD_KEEP_TIMECONTROL_MANAGEMENT_PACK_ACCESSCORE_DUMP_DESTCPU_COUNTCREATE_STORED_OUTLINESCURSOR_BIND_CAPTURE_DESTINATIONCURSOR_SHARINGDB_nK_CACHE_SIZEDB_BLOCK_CHECKINGDB_BLOCK_CHECKSUMDB_CACHE_ADVICEDB_CACHE_SIZEDB_CREATE_FILE_DESTDB_CREATE_ONLINE_LOG_DEST_nDB_FILE_MULTIBLOCK_READ_COUNTDB_FLASH_CACHE_SIZEDB_FLASHBACK_RETENTION_TARGETDB_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_PROCESSESHS_AUTOREGISTERJAVA_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_LOCAL_FIRSTLOG_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_CAPTURE_SQL_PLAN_BASELINESOPTIMIZER_DYNAMIC_SAMPLINGOPTIMIZER_FEATURES_ENABLEOPTIMIZER_INDEX_CACHINGOPTIMIZER_INDEX_COST_ADJOPTIMIZER_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_CPUPGA_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_SERVERSPFILESQL_TRACESQLTUNE_CATEGORYSTANDBY_ARCHIVE_DESTSTANDBY_FILE_MANAGEMENTSTAR_TRANSFORMATION_ENABLEDSTATISTICS_LEVELSTREAMS_POOL_SIZETHREADTIMED_OS_STATISTICSTIMED_STATISTICSTRACE_ENABLEDUNDO_RETENTIONUNDO_TABLESPACEUSER_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_SIZETo see the current settings for initialization parameters, use the following SQL*Plus command:
SQL> SHOW PARAMETERS
This command 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.
You should not specify the following two types of parameters in your parameter files:
Parameters that you never alter except when 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
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.