Initialization Parameters, 4 of 222

Changing Parameter Values

You change the value of a parameter by editing the initialization parameter file. In most cases, the new value takes effect the next time you start an instance of the database. However, you can change the value of some parameters for the duration of the current session, as described in the following section.

Dynamic Parameters

Some initialization parameters are dynamic, that is, they can be modified using the ALTER SESSION or ALTER SYSTEM statements while an instance is running.

Use the following syntax to dynamically alter initialization parameters:

ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value [DEFERRED]

Whenever a dynamic parameter is modified using the ALTER SYSTEM statement, Oracle records the command 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:

`CURSOR_SHARING`	`DB_BLOCK_CHECKING`
`DB_CREATE_FILE_DEST`	`DB_CREATE_ONLINE_LOG_DEST_``n`
`DB_FILE_MULTIBLOCK_READ_COUNT`	`FILESYSTEMIO_OPTIONS`
`GLOBAL_NAMES`	`HASH_AREA_SIZE`
`HASH_JOIN_ENABLED`	`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_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_DYNAMIC_SAMPLING`
`OPTIMIZER_INDEX_CACHING`	`OPTIMIZER_INDEX_COST_ADJ`
`OPTIMIZER_MAX_PERMUTATIONS`	`OPTIMIZER_MODE`
`ORACLE_TRACE_ENABLE`	`PARALLEL_INSTANCE_GROUP`
`PARALLEL_MIN_PERCENT`	`PARTITION_VIEW_ENABLED`
`PLSQL_COMPILER_FLAGS`	`PLSQL_V2_COMPATIBILITY`
`QUERY_REWRITE_ENABLED`	`QUERY_REWRITE_INTEGRITY`
`REMOTE_DEPENDENCIES_MODE`	`SESSION_CACHED_CURSORS`
`SORT_AREA_RETAINED_SIZE`	`SORT_AREA_SIZE`
`STAR_TRANSFORMATION_ENABLED`	`STATISTICS_LEVEL`
`TIMED_OS_STATISTICS`	`TIMED_STATISTICS`
`TRACEFILE_IDENTIFIER`	`UNDO_SUPPRESS_ERRORS`
`WORKAREA_SIZE_POLICY`

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`	`ARCHIVE_LAG_TARGET`
`BACKGROUND_DUMP_DEST`	`CONTROL_FILE_RECORD_KEEP_TIME`
`CORE_DUMP_DEST`	`CURSOR_SHARING`
`DB_``n``K_CACHE_SIZE`	`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_KEEP_CACHE_SIZE`	`DB_RECYCLE_CACHE_SIZE`
`DG_BROKER_CONFIG_FILE``n`	`DG_BROKER_START`
`DISPATCHERS`	`DRS_START`
`FAL_CLIENT`	`FAL_SERVER`
`FAST_START_IO_TARGET`	`FAST_START_MTTR_TARGET`
`FAST_START_PARALLEL_ROLLBACK`	`FILE_MAPPING`
`FILESYSTEMIO_OPTIONS`	`FIXED_DATE`
`GLOBAL_NAMES`	`HS_AUTOREGISTER`
`JOB_QUEUE_PROCESSES`	`LARGE_POOL_SIZE`
`LICENSE_MAX_SESSIONS`	`LICENSE_MAX_USERS`
`LICENSE_SESSIONS_WARNING`	`LOCAL_LISTENER`
`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_CHECKPOINTS_TO_ALERT`
`LOG_CHECKPOINT_INTERVAL`	`LOG_CHECKPOINT_TIMEOUT`
`MAX_DUMP_FILE_SIZE`	`NLS_LENGTH_SEMANTICS`
`NLS_NCHAR_CONV_EXCP`	`OPEN_CURSORS`
`OPTIMIZER_DYNAMIC_SAMPLING`	`ORACLE_TRACE_ENABLE`
`PARALLEL_ADAPTIVE_MULTI_USER`	`PARALLEL_INSTANCE_GROUP`
`PARALLEL_THREADS_PER_CPU`	`PGA_AGGREGATE_TARGET`
`PLSQL_COMPILER_FLAGS`	`PLSQL_NATIVE_C_COMPILER`
`PLSQL_NATIVE_LIBRARY_DIR`	`PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT`
`PLSQL_NATIVE_LINKER`	`PLSQL_NATIVE_MAKE_FILE_NAME`
`PLSQL_NATIVE_MAKE_UTILITY`	`PLSQL_V2_COMPATIBILITY`
`QUERY_REWRITE_ENABLED`	`QUERY_REWRITE_INTEGRITY`
`REMOTE_DEPENDENCIES_MODE`	`REMOTE_LISTENER`
`RESOURCE_LIMIT`	`RESOURCE_MANAGER_PLAN`
`SERVICE_NAMES`	`SHARED_POOL_SIZE`
`SHARED_SERVERS`	`STANDBY_ARCHIVE_DEST`
`STANDBY_FILE_MANAGEMENT`	`STATISTICS_LEVEL`
`TIMED_OS_STATISTICS`	`TIMED_STATISTICS`
`TRACE_ENABLED`	`UNDO_RETENTION`
`UNDO_SUPPRESS_ERRORS`	`UNDO_TABLESPACE`
`USER_DUMP_DEST`	`WORKAREA_SIZE_POLICY`

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:

`BACKUP_TAPE_IO_SLAVES`	`OBJECT_CACHE_MAX_SIZE_PERCENT`
`OBJECT_CACHE_OPTIMAL_SIZE`	`OLAP_PAGE_POOL_SIZE`
`SORT_AREA_RETAINED_SIZE`	`SORT_AREA_SIZE`
`TRANSACTION_AUDITING`

Displaying Current Parameter Values

To see the current settings for initialization parameters, use the following SQL*Plus command:

SQL> SHOW PARAMETERS

This command displays all parameters in alphabetical order, 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.

Parameters You Should Not Specify in the Parameter 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 Corporation to resolve a problem
Derived parameters, which normally do not need altering because their values are calculated automatically by the Oracle database server

When Parameters Are Set Incorrectly

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.