Oracle Database Parameters
Since Oracle 9i there are much more dynamic parameters which can be changed in runtime. So it’s a good thing to use server parameter files (SPFILEs) instead of plain text parameter files (PFILEs). I will use MYDB as instance name in this blog.
If you don’t use a SPFILE just create one using:
SQL> create spfile from pfile;
and bounce the instance:
SQL> shutdown SQL> startup
The original $ORACLE_HOME/dbs/initMYDB.ora still exists but won’t be used as long as a server parameter file exists. The SPFILE is named $ORACLE_HOME/dbs/spfileMYDB.ora.
After using a SPFILE and changing parameters using ALTER SYSTEM in runtime, you can create a PFILE with the actual parameters by typing:
SQL> create pfile from spfile;
You can query these data dictionary views to get informations about Oracle’s parameters:
- V$OBSOLETE_PARAMETER shows information about obsolete parameters
- V$PARAMETER displays the initialization parameters that are used in the current session
- V$PARAMETER2 like V$PARAMETER, but every value of a parameter appears in a row
- V$PARAMETER_VALID_VALUES shows a list of valid values for parameters
- V$SPPARAMETER displays contents of server parameter file
- V$SYSTEM_PARAMETER displays the initialization parameters that are currently in effect for the instance
- V$SYSTEM_PARAMETER2 like V$SYSTEM_PARAMETER, but every value of a parameter appears in a row
V$PARAMETER
The columns *_MODIFIABLE show whether a parameter value is modifiable with ALTER SYSTEM (ISSYS_MODIFIABLE), in the session through ALTER SESSION (ISSES_MODIFIABLE) or if every instance can have a different value (ISINSTANCE_MODIFIABLE).
ISSYS_MODIFIABLE tells us whether we can ALTER SYSTEM that parameter or not:
IMMEDIATE— can be altered regardless of what type of parameter file was used to start the instance and changes take effect immediately,DEFERRED— can be altered regardless of what type of parameter file was used to start the instance and change will take effect in subsequent sessions,FALSE— can only be changed when a SPFILE is used, you need to bounce the instance.
ISMODIFIED shows if you have changed the value after instance startup. Query WHERE ismodified != 'FALSE' to see them: the value MODIFIED means altered by ALTER SESSION, SYSTEM_MOD says altered by ALTER SYSTEM.
To show some parameters I reduced the list below (342 total records!) to most used ones. These parameters are from 11g:
SQL> set linesize 132 SQL> set pages 9000 SQL> select name, isdefault, isses_modifiable, issys_modifiable, isinstance_modifiable, ismodified 2 from v$parameter order by name; NAME ISDEFAULT ISSES ISSYS_MOD ISINS ISMODIFIED ------------------------------ --------- ----- --------- ----- ---------- asm_diskgroups TRUE FALSE IMMEDIATE TRUE FALSE asm_diskstring TRUE FALSE IMMEDIATE TRUE FALSE asm_power_limit TRUE TRUE IMMEDIATE TRUE FALSE audit_file_dest TRUE FALSE DEFERRED TRUE FALSE background_dump_dest TRUE FALSE IMMEDIATE TRUE FALSE buffer_pool_keep TRUE FALSE FALSE FALSE FALSE buffer_pool_recycle TRUE FALSE FALSE FALSE FALSE cluster_database TRUE FALSE FALSE FALSE FALSE compatible TRUE FALSE FALSE FALSE FALSE control_files FALSE FALSE FALSE FALSE FALSE core_dump_dest TRUE FALSE IMMEDIATE TRUE FALSE cursor_sharing TRUE TRUE IMMEDIATE TRUE FALSE cursor_space_for_time TRUE FALSE FALSE FALSE FALSE db_16k_cache_size TRUE FALSE IMMEDIATE TRUE FALSE db_2k_cache_size TRUE FALSE IMMEDIATE TRUE FALSE db_32k_cache_size TRUE FALSE IMMEDIATE TRUE FALSE db_4k_cache_size TRUE FALSE IMMEDIATE TRUE FALSE db_8k_cache_size TRUE FALSE IMMEDIATE TRUE FALSE db_block_buffers TRUE FALSE FALSE FALSE FALSE db_block_checking TRUE TRUE IMMEDIATE TRUE FALSE db_block_checksum TRUE FALSE IMMEDIATE TRUE FALSE db_block_size FALSE FALSE FALSE FALSE FALSE db_cache_advice TRUE FALSE IMMEDIATE TRUE FALSE db_cache_size TRUE FALSE IMMEDIATE TRUE FALSE db_create_file_dest FALSE TRUE IMMEDIATE TRUE FALSE db_create_online_log_dest_1 FALSE TRUE IMMEDIATE TRUE FALSE db_create_online_log_dest_2 FALSE TRUE IMMEDIATE TRUE FALSE db_domain TRUE FALSE FALSE FALSE FALSE db_file_multiblock_read_count TRUE TRUE IMMEDIATE TRUE FALSE db_file_name_convert TRUE TRUE FALSE FALSE FALSE db_flashback_retention_target TRUE FALSE IMMEDIATE FALSE FALSE db_keep_cache_size TRUE FALSE IMMEDIATE TRUE FALSE db_name FALSE FALSE FALSE FALSE FALSE db_recovery_file_dest FALSE FALSE IMMEDIATE FALSE FALSE db_recovery_file_dest_size FALSE FALSE IMMEDIATE FALSE FALSE db_writer_processes TRUE FALSE FALSE FALSE FALSE dbwr_io_slaves TRUE FALSE FALSE FALSE FALSE diagnostic_dest FALSE FALSE IMMEDIATE TRUE FALSE disk_asynch_io TRUE FALSE FALSE FALSE FALSE fast_start_io_target TRUE FALSE IMMEDIATE TRUE FALSE fast_start_mttr_target TRUE FALSE IMMEDIATE TRUE FALSE filesystemio_options TRUE FALSE FALSE FALSE FALSE hash_area_size TRUE TRUE FALSE FALSE FALSE instance_name FALSE FALSE FALSE FALSE FALSE java_pool_size TRUE FALSE IMMEDIATE TRUE FALSE job_queue_processes TRUE FALSE IMMEDIATE TRUE FALSE large_pool_size TRUE FALSE IMMEDIATE TRUE FALSE log_archive_config TRUE FALSE IMMEDIATE TRUE FALSE log_archive_dest TRUE FALSE IMMEDIATE TRUE FALSE log_archive_dest_1 TRUE TRUE IMMEDIATE TRUE FALSE log_archive_dest_2 TRUE TRUE IMMEDIATE TRUE FALSE log_archive_dest_state_1 TRUE TRUE IMMEDIATE TRUE FALSE log_archive_dest_state_2 TRUE TRUE IMMEDIATE TRUE FALSE log_archive_duplex_dest TRUE FALSE IMMEDIATE TRUE FALSE log_archive_format TRUE FALSE FALSE FALSE FALSE log_archive_max_processes TRUE FALSE IMMEDIATE TRUE FALSE log_archive_min_succeed_dest TRUE TRUE IMMEDIATE TRUE FALSE log_archive_start TRUE FALSE FALSE FALSE FALSE log_buffer TRUE FALSE FALSE FALSE FALSE log_checkpoint_interval TRUE FALSE IMMEDIATE TRUE FALSE log_checkpoint_timeout TRUE FALSE IMMEDIATE TRUE FALSE log_file_name_convert TRUE FALSE FALSE FALSE FALSE memory_max_target TRUE FALSE FALSE FALSE FALSE memory_target FALSE FALSE IMMEDIATE TRUE FALSE open_cursors TRUE FALSE IMMEDIATE TRUE FALSE open_links TRUE FALSE FALSE FALSE FALSE open_links_per_instance TRUE FALSE FALSE FALSE FALSE optimizer_dynamic_sampling TRUE TRUE IMMEDIATE TRUE FALSE optimizer_features_enable TRUE TRUE IMMEDIATE TRUE FALSE optimizer_index_caching TRUE TRUE IMMEDIATE TRUE FALSE optimizer_index_cost_adj TRUE TRUE IMMEDIATE TRUE FALSE optimizer_mode TRUE TRUE IMMEDIATE TRUE FALSE os_roles TRUE FALSE FALSE FALSE FALSE pga_aggregate_target TRUE FALSE IMMEDIATE TRUE FALSE processes FALSE FALSE FALSE FALSE FALSE query_rewrite_enabled TRUE TRUE IMMEDIATE TRUE FALSE recyclebin TRUE TRUE DEFERRED TRUE FALSE remote_login_passwordfile FALSE FALSE FALSE FALSE FALSE service_names TRUE FALSE IMMEDIATE TRUE FALSE session_cached_cursors TRUE TRUE FALSE FALSE FALSE session_max_open_files TRUE FALSE FALSE FALSE FALSE sessions TRUE FALSE FALSE FALSE FALSE sga_max_size TRUE FALSE FALSE FALSE FALSE sga_target TRUE FALSE IMMEDIATE TRUE FALSE shared_pool_reserved_size TRUE FALSE FALSE FALSE FALSE shared_pool_size TRUE FALSE IMMEDIATE TRUE FALSE sort_area_retained_size TRUE TRUE DEFERRED TRUE FALSE sort_area_size TRUE TRUE DEFERRED TRUE FALSE spfile TRUE FALSE IMMEDIATE TRUE FALSE statistics_level TRUE TRUE IMMEDIATE TRUE FALSE streams_pool_size TRUE FALSE IMMEDIATE TRUE FALSE timed_os_statistics TRUE TRUE IMMEDIATE TRUE FALSE timed_statistics TRUE TRUE IMMEDIATE TRUE FALSE trace_enabled TRUE FALSE IMMEDIATE TRUE FALSE undo_management FALSE FALSE FALSE FALSE FALSE undo_retention TRUE FALSE IMMEDIATE TRUE FALSE undo_tablespace FALSE FALSE IMMEDIATE TRUE FALSE user_dump_dest TRUE FALSE IMMEDIATE TRUE FALSE utl_file_dir FALSE FALSE FALSE FALSE FALSE [...] 342 rows selected.
