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_LIMIT
COMMIT_WRITE
CREATE_STORED_OUTLINES
CURSOR_SHARING
DB_BLOCK_CHECKING
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_
n
DB_FILE_MULTIBLOCK_READ_COUNT
DB_FILE_NAME_CONVERT
FILESYSTEMIO_OPTIONS
GLOBAL_NAMES
HASH_AREA_SIZE
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_DYNAMIC_SAMPLING
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MODE
PARALLEL_INSTANCE_GROUP
PARALLEL_MIN_PERCENT
PLSQL_CODE_TYPE
PLSQL_DEBUG
PLSQL_OPTIMIZE_LEVEL
PLSQL_V2_COMPATIBILITY
PLSQL_WARNINGS
QUERY_REWRITE_ENABLED
QUERY_REWRITE_INTEGRITY
REMOTE_DEPENDENCIES_MODE
RESUMABLE_TIMEOUT
SESSION_CACHED_CURSORS
SKIP_UNUSABLE_INDEXES
SORT_AREA_RETAINED_SIZE
SORT_AREA_SIZE
SQLTUNE_CATEGORY
STAR_TRANSFORMATION_ENABLED
STATISTICS_LEVEL
TIMED_OS_STATISTICS
TIMED_STATISTICS
TRACEFILE_IDENTIFIER
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
ASM_DISKGROUPS
ASM_DISKSTRING
ASM_POWER_LIMIT
BACKGROUND_DUMP_DEST
CIRCUITS
COMMIT_WRITE
CONTROL_FILE_RECORD_KEEP_TIME
CORE_DUMP_DEST
CPU_COUNT
CREATE_STORED_OUTLINES
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_FLASHBACK_RETENTION_TARGET
DB_KEEP_CACHE_SIZE
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_RECYCLE_CACHE_SIZE
DG_BROKER_CONFIG_FILE
n
DG_BROKER_START
DISPATCHERS
FAL_CLIENT
FAL_SERVER
FAST_START_MTTR_TARGET
FAST_START_PARALLEL_ROLLBACK
FILE_MAPPING
FILESYSTEMIO_OPTIONS
FIXED_DATE
GLOBAL_NAMES
HS_AUTOREGISTER
JAVA_POOL_SIZE
JOB_QUEUE_PROCESSES
LARGE_POOL_SIZE
LDAP_DIRECTORY_ACCESS
LICENSE_MAX_SESSIONS
LICENSE_MAX_USERS
LICENSE_SESSIONS_WARNING
LOCAL_LISTENER
LOG_ARCHIVE_CONFIG
LOG_ARCHIVE_DEST
LOG_ARCHIVE_DEST_
n
LOG_ARCHIVE_DEST_STATE_
n
LOG_ARCHIVE_DUPLEX_DEST
LOG_ARCHIVE_LOCAL_FIRST
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
NLS_LENGTH_SEMANTICS
NLS_NCHAR_CONV_EXCP
OPEN_CURSORS
OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MODE
PARALLEL_ADAPTIVE_MULTI_USER
PARALLEL_INSTANCE_GROUP
PARALLEL_MAX_SERVERS
PARALLEL_MIN_SERVERS
PARALLEL_THREADS_PER_CPU
PGA_AGGREGATE_TARGET
PLSQL_CODE_TYPE
PLSQL_DEBUG
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
PLSQL_OPTIMIZE_LEVEL
PLSQL_V2_COMPATIBILITY
PLSQL_WARNINGS
QUERY_REWRITE_ENABLED
QUERY_REWRITE_INTEGRITY
REMOTE_DEPENDENCIES_MODE
REMOTE_LISTENER
RESOURCE_LIMIT
RESOURCE_MANAGER_PLAN
RESUMABLE_TIMEOUT
SERVICE_NAMES
SGA_TARGET
SHARED_POOL_SIZE
SHARED_SERVER_SESSIONS
SHARED_SERVERS
SKIP_UNUSABLE_INDEXES
SQLTUNE_CATEGORY
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT
STAR_TRANSFORMATION_ENABLED
STATISTICS_LEVEL
STREAMS_POOL_SIZE
TIMED_OS_STATISTICS
TIMED_STATISTICS
TRACE_ENABLED
UNDO_RETENTION
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
:
AUDIT_FILE_DEST
BACKUP_TAPE_IO_SLAVES
OBJECT_CACHE_MAX_SIZE_PERCENT
OBJECT_CACHE_OPTIMAL_SIZE
OLAP_PAGE_POOL_SIZE
SORT_AREA_RETAINED_SIZE
SORT_AREA_SIZE
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, 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.