Oracle Database Administration: The Essential Refe

Dynamically Modifiable Parameters

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:

Parameter Name

The name of the parameter, which must be specified in the INIT.ORA file.

Session Modifiable

Indicates whether a parameter may be modified using an ALTER SESSION command.

System Modifiable

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.

Table 12-2. Dynamically Modifiable Initialization Parameters in Oracle7

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