While most initialization parameters are static—that is, they take their values from the INIT.ORA file when the instance is started—some may be dynamically modified while the instance is up and the database is open. Table 12.2 and Table 12.3 list the tables that may be modified dynamically for Oracle7 and Oracle8, respectively. These tables consist of three columns:
The name of the parameter, which must be specified in the INIT.ORA file.
Indicates whether a parameter may be modified using an ALTER SESSION command.
Indicates whether a parameter may be modified using an ALTER SYSTEM command. IMMEDIATE indicates that the change takes place immediately, while DEFERRED indicates that the change is deferred until the next session.
Parameter Name |
Session Modifiable |
System Modifiable |
B_TREE_BITMAP_PLANS |
Yes |
No |
DB_FILE_MULTIBLOCK_READ_COUNT |
Yes |
Immediate |
HASH_AREA_SIZE |
Yes |
No |
HASH_ JOIN_ENABLED |
Yes |
No |
HASH_MULTIBLOCK_IO_COUNT |
Yes |
Immediate |
LOG_CHECKPOINT_INTERVAL |
No |
Immediate |
LOG_CHECKPOINT_TIMEOUT |
No |
Immediate |
LOG_SMALL_ENTRY_MAX_SIZE |
No |
Immediate |
MAX_DUMP_FILE_SIZE |
Yes |
No |
OPTIMIZER_PERCENT_PARALLEL |
Yes |
No |
OPTIMIZER_SEARCH_LIMIT |
Yes |
No |
PARALLEL_MIN_PERCENT |
Yes |
No |
PARTITION_VIEW_ENABLED |
Yes |
No |
SORT_AREA_RETAINED_SIZE |
Yes |
Deferred |
SORT_AREA_SIZE |
Yes |
Deferred |
SORT_DIRECT_WRITES |
Yes |
Deferred |
SORT_READ_FAC |
Yes |
Deferred |
SORT_WRITE_BUFFERS |
Yes |
Deferred |
SORT_WRITE_BUFFER_SIZE |
Yes |
Deferred |
TEXT_ENABLE |
Yes |
No |
TIMED_STATISTICS |
Yes |
Immediate |
USER_DUMP_DEST |
No |
Immediate |
Parameter Name |
Session Modifiable |
System Modifiable |
ALLOW_PARTIAL_SN_RESULTS |
Yes |
Deferred |
AQ_TM_PROCESSES |
No |
Immediate |
BACKGROUND_DUMP_DEST |
No |
Immediate |
BACKUP_DISK_IO_SLAVES |
No |
Deferred |
BACKUP_TAPE_IO_SLAVES |
No |
Deferred |
B_TREE_BITMAP_PLANS |
Yes |
No |
CLOSE_CACHED_OPEN_CURSORS |
Yes |
No |
COMPLEX_VIEW_MERGING |
Yes |
No |
CONTROL_FILE_RECORD_KEEP_TIME |
No |
Immediate |
CORE_DUMP_DEST |
No |
Immediate |
DB_BLOCK_CHECKPOINT_BATCH |
No |
Immediate |
DB_BLOCK_CHECKSUM |
No |
Immediate |
DB_BLOCK_MAX_DIRTY_TARGET |
No |
Immediate |
DB_FILE_DIRECT_IO_COUNT |
No |
Deferred |
DB_FILE_MULTIBLOCK_READ_COUNT |
Yes |
Immediate |
FIXED_DATE |
No |
Immediate |
FAST_FULL_SCAN_ENABLED |
No |
Immediate |
FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY |
No |
Immediate |
GC_DEFER_TIME |
No |
Immediate |
GLOBAL_NAMES |
Yes |
Immediate |
HASH_AREA_SIZE |
Yes |
No |
HASH_ JOIN_ENABLED |
Yes |
No |
HASH_MULTIBLOCK_IO_COUNT |
Yes |
Immediate |
JOB_QUEUE_PROCESSES |
No |
Immediate |
LICENSE_MAX_SESSIONS |
No |
Immediate |
LICENSE_MAX_USERS |
No |
Immediate |
LICENSE_SESSIONS_WARNING |
No |
Immediate |
LOG_ARCHIVE_DUPLEX_DEST |
No |
Immediate |
LOG_ARCHIVE_MIN_SUCCEED_DEST |
No |
Immediate |
LOG_CHECKPOINT_INTERVAL |
No |
Immediate |
LOG_CHECKPOINT_TIMEOUT |
No |
Immediate |
LOG_SMALL_ENTRY_MAX_SIZE |
No |
Immediate |
MAX_DUMP_FILE_SIZE |
Yes |
Immediate |
MTS_DISPATCHERS |
No |
Immediate |
MTS_SERVERS |
No |
Immediate |
NLS_CALENDAR |
Yes |
No |
NLS_CURRENCY |
Yes |
No |
NLS_DATE_FORMAT |
Yes |
No |
NLS_DATE_LANGUAGE |
Yes |
No |
NLS_ISO_CURRENCY |
Yes |
No |
NLS_LANGUAGE |
Yes |
No |
NLS_NUMERIC_CHARACTERS |
Yes |
No |
NLS_SORT |
Yes |
No |
NLS_TERRITORY |
Yes |
No |
OBJECT_CACHE_MAX_SIZE_PERCENT |
Yes |
Deferred |
OBJECT_CACHE_OPTIMAL_SIZE |
Yes |
Deferred |
OPS_ADMIN_GROUP |
Yes |
Immediate |
OPTIMIZER_INDEX_CACHING |
Yes |
No |
OPTIMIZER_INDEX_COST_ADJ |
Yes |
No |
OPTIMIZER_MAX_PERMUTATIONS |
Yes |
No |
OPTIMIZER_MODE |
Yes |
No |
OPTIMIZER_PERCENT_PARALLEL |
Yes |
No |
OPTIMIZER_SEARCH_LIMIT |
Yes |
No |
PARALLEL_ADAPTIVE_MULTI_USER |
Yes |
No |
PARALLEL_BROADCAST_ENABLED |
Yes |
No |
PARALLEL_INSTANCE_GROUP |
Yes |
Immediate |
PARALLEL_MIN_PERCENT |
Yes |
No |
PARALLEL_TRANSACTION_RESOURCE_TIMEOUT |
No |
Immediate |
PARTITION_VIEW_ENABLED |
Yes |
No |
PLSQL_V2_COMPATIBILITY |
Yes |
Immediate |
PUSH_ JOIN_PREDICATE |
Yes |
No |
REMOTE_DEPENDENCIES_MODE |
Yes |
Immediate |
RESOURCE_LIMIT |
No |
Immediate |
SESSION_CACHED_CURSORS |
Yes |
No |
SORT_AREA_RETAINED_SIZE |
Yes |
Deferred |
SORT_AREA_SIZE |
Yes |
Deferred |
SORT_DIRECT_WRITES |
Yes |
Deferred |
SORT_READ_FAC |
Yes |
Deferred |
SORT_WRITE_BUFFERS |
Yes |
Deferred |
SORT_WRITE_BUFFER_SIZE |
Yes |
Deferred |
SPIN_COUNT |
No |
Immediate |
STAR_TRANSFORMATION_ENABLED |
Yes |
No |
TEXT_ENABLE |
Yes |
Immediate |
TIMED_OS_STATISTICS |
No |
Immediate |
TIMED_STATISTICS |
Yes |
Immediate |
TRANSACTION_AUDITING |
No |
Deferred |
USER_DUMP_DEST |
No |