| Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-02 |
|
|
View PDF |
| Oracle® Database Reference 10g Release 2 (10.2) Part Number B14237-02 |
|
|
View PDF |
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
The following list shows the initialization parameters by their functional category:
ANSI Compliance
Backup and Restore
BFILEs
Buffer Cache and I/O
Cursors 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
Parallel Execution
PL/SQL
PL/SQL Compiler
SGA Memory
Real Application Clusters
Redo Logs, Archiving, and Recovery
Resource Manager
Security and Auditing
Sessions 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_WRITECREATE_STORED_OUTLINESCURSOR_SHARINGDB_BLOCK_CHECKINGDB_CREATE_FILE_DESTDB_CREATE_ONLINE_LOG_DEST_nDB_FILE_MULTIBLOCK_READ_COUNTDB_FILE_NAME_CONVERTFILESYSTEMIO_OPTIONSGLOBAL_NAMESHASH_AREA_SIZELOG_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_DYNAMIC_SAMPLINGOPTIMIZER_FEATURES_ENABLEOPTIMIZER_INDEX_CACHINGOPTIMIZER_INDEX_COST_ADJOPTIMIZER_MODEPARALLEL_INSTANCE_GROUPPARALLEL_MIN_PERCENTPLSQL_CODE_TYPEPLSQL_DEBUGPLSQL_OPTIMIZE_LEVELPLSQL_V2_COMPATIBILITYPLSQL_WARNINGSQUERY_REWRITE_ENABLEDQUERY_REWRITE_INTEGRITYREMOTE_DEPENDENCIES_MODERESUMABLE_TIMEOUTSESSION_CACHED_CURSORSSKIP_UNUSABLE_INDEXESSORT_AREA_RETAINED_SIZESORT_AREA_SIZESQLTUNE_CATEGORYSTAR_TRANSFORMATION_ENABLEDSTATISTICS_LEVELTIMED_OS_STATISTICSTIMED_STATISTICSTRACEFILE_IDENTIFIERWORKAREA_SIZE_POLICYThe 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_LIMITBACKGROUND_DUMP_DESTCIRCUITSCOMMIT_WRITECONTROL_FILE_RECORD_KEEP_TIMECORE_DUMP_DESTCPU_COUNTCREATE_STORED_OUTLINESCURSOR_SHARINGDB_nK_CACHE_SIZEDB_BLOCK_CHECKINGDB_BLOCK_CHECKSUMDB_CACHE_ADVICEDB_CACHE_SIZEDB_CREATE_FILE_DESTDB_CREATE_ONLINE_LOG_DEST_nDB_FILE_MULTIBLOCK_READ_COUNTDB_FLASHBACK_RETENTION_TARGETDB_KEEP_CACHE_SIZEDB_RECOVERY_FILE_DESTDB_RECOVERY_FILE_DEST_SIZEDB_RECYCLE_CACHE_SIZEDG_BROKER_CONFIG_FILEnDG_BROKER_STARTDISPATCHERSFAL_CLIENTFAL_SERVERFAST_START_MTTR_TARGETFAST_START_PARALLEL_ROLLBACKFILE_MAPPINGFILESYSTEMIO_OPTIONSFIXED_DATEGLOBAL_NAMESHS_AUTOREGISTERJAVA_POOL_SIZEJOB_QUEUE_PROCESSESLARGE_POOL_SIZELDAP_DIRECTORY_ACCESSLICENSE_MAX_SESSIONSLICENSE_MAX_USERSLICENSE_SESSIONS_WARNINGLOCAL_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_SERVERSNLS_LENGTH_SEMANTICSNLS_NCHAR_CONV_EXCPOPEN_CURSORSOPTIMIZER_DYNAMIC_SAMPLINGOPTIMIZER_FEATURES_ENABLEOPTIMIZER_INDEX_CACHINGOPTIMIZER_INDEX_COST_ADJOPTIMIZER_MODEPARALLEL_ADAPTIVE_MULTI_USERPARALLEL_INSTANCE_GROUPPARALLEL_MAX_SERVERSPARALLEL_MIN_SERVERSPARALLEL_THREADS_PER_CPUPGA_AGGREGATE_TARGETPLSQL_CODE_TYPEPLSQL_DEBUGPLSQL_NATIVE_LIBRARY_DIRPLSQL_NATIVE_LIBRARY_SUBDIR_COUNTPLSQL_OPTIMIZE_LEVELPLSQL_V2_COMPATIBILITYPLSQL_WARNINGSQUERY_REWRITE_ENABLEDQUERY_REWRITE_INTEGRITYREMOTE_DEPENDENCIES_MODEREMOTE_LISTENERRESOURCE_LIMITRESOURCE_MANAGER_PLANRESUMABLE_TIMEOUTSERVICE_NAMESSGA_TARGETSHARED_POOL_SIZESHARED_SERVER_SESSIONSSHARED_SERVERSSKIP_UNUSABLE_INDEXESSQLTUNE_CATEGORYSTANDBY_ARCHIVE_DESTSTANDBY_FILE_MANAGEMENTSTAR_TRANSFORMATION_ENABLEDSTATISTICS_LEVELSTREAMS_POOL_SIZETIMED_OS_STATISTICSTIMED_STATISTICSTRACE_ENABLEDUNDO_RETENTIONUNDO_TABLESPACEUSER_DUMP_DESTWORKAREA_SIZE_POLICYThe 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_SIZESORT_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.
