v$parameter 里的 issys…

v$parameter 里的 issys_modifiable 状态信息：

IMMEDIATE   修改后立即生效
DEFERRED    修改后，必须重启才能生效
FALSE           不能更改

scope=both和scope=spfile

Oracle 里面有个叫做spfile的东西，就是动态参数文件，里面设置了Oracle 的各种参数。所谓的动态，就是说你可以在不关闭数据库的情况下，更改数据库参数，记录在spfile里面。更改参数的时候，有4种scope选项。scope就是范围

++ scope=spfile 仅仅更改spfile里面的记载，不更改内存，也就是不立即生效，而是等下次数据库启动生效。有一些参数只允许用这种方法更改

++ scope=memory 仅仅更改内存，不改spfile。也就是下次启动就失效了

++ scope=both 内存和spfile都更改

++ 不指定scope参数，等同于scope=both.

BOTH:改变实例及SPFILE，使用BOTH选项实际上等同于不带参数的ALTER SYSTEM语句。

不可修改的系统参数

SQL> select name, value from v$parameter where issys_modifiable='FALSE';

NAME VALUE

---------------------------------------- ----------------------------------------

tracefile_identifier

lock_name_space

processes 150

sessions 170

instance_groups

event

sga_max_size 285212672

pre_page_sga FALSE

shared_memory_address 0

hi_shared_memory_address 0

use_indirect_data_buffers FALSE

NAME VALUE

---------------------------------------- ----------------------------------------

lock_sga FALSE

shared_pool_reserved_size 3774873

java_soft_sessionspace_limit 0

java_max_sessionspace_size 0

spfile /opt/oracle/product/dbs/spfiletest.ora

instance_type RDBMS

nls_language AMERICAN

nls_territory AMERICA

nls_sort

nls_date_language

nls_date_format

NAME VALUE

---------------------------------------- ----------------------------------------

nls_currency

nls_numeric_characters

nls_iso_currency

nls_calendar

nls_time_format

nls_timestamp_format

nls_time_tz_format

nls_timestamp_tz_format

nls_dual_currency

nls_comp

fileio_network_adapters

NAME VALUE

---------------------------------------- ----------------------------------------

filesystemio_options none

disk_asynch_io TRUE

tape_asynch_io TRUE

dbwr_io_slaves 0

cluster_interconnects

gcs_server_processes 0

active_instance_count

control_files /opt/oracle/oradata/test/control01.ctl,

/opt/oracle/oradata/test/control02.ctl,

/opt/oracle/oradata/test/control03.ctl

NAME VALUE

---------------------------------------- ----------------------------------------

db_file_name_convert

log_file_name_convert

db_block_buffers 0

db_block_size 8192

db_writer_processes 1

buffer_pool_keep

buffer_pool_recycle

max_commit_propagation_delay 0

compatible 10.2.0.1.0

remote_archive_enable true

log_archive_start FALSE

NAME VALUE

---------------------------------------- ----------------------------------------

log_archive_format %t_%s_%r.dbf

log_buffer 2927616

db_files 200

read_only_open_delayed FALSE

cluster_database FALSE

parallel_server FALSE

parallel_server_instances 1

cluster_database_instances 1

gc_files_to_locks

recovery_parallelism 0

logmnr_max_persistent_sessions 1

NAME VALUE

---------------------------------------- ----------------------------------------

dml_locks 748

replication_dependency_tracking TRUE

instance_number 0

transactions 187

transactions_per_rollback_segment 5

rollback_segments

undo_management AUTO

serial_reuse disable

os_roles FALSE

rdbms_server_dn

max_enabled_roles 150

NAME VALUE

---------------------------------------- ----------------------------------------

remote_os_authent FALSE

remote_os_roles FALSE

O7_DICTIONARY_ACCESSIBILITY FALSE

remote_login_passwordfile EXCLUSIVE

audit_sys_operations FALSE

global_context_pool_size

db_domain

distributed_lock_timeout 60

commit_point_strength 1

instance_name test

cursor_space_for_time FALSE

NAME VALUE

---------------------------------------- ----------------------------------------

session_cached_cursors 20

utl_file_dir

parallel_min_percent 0

create_bitmap_area_size 8388608

bitmap_merge_area_size 1048576

parallel_execution_message_size 2148

hash_area_size 131072

shadow_core_dump partial

background_core_dump partial

audit_syslog_level

session_max_open_files 10

NAME VALUE

---------------------------------------- ----------------------------------------

open_links 4

open_links_per_instance 4

audit_trail NONE

db_name test

db_unique_name test

ifile

os_authent_prefix ops$

sql92_security FALSE

blank_trimming FALSE

parallel_automatic_tuning FALSE

sql_version NATIVE

已选择107行。

可修改，并立即生效的系统参数

SQL> select name, value from v$parameter where issys_modifiable='IMMEDIATE';

NAME VALUE

---------------------------------------- ----------------------------------------

timed_statistics TRUE

timed_os_statistics 0

resource_limit FALSE

license_max_sessions 0

license_sessions_warning 0

cpu_count 1

shared_pool_size 0

large_pool_size 0

java_pool_size 0

streams_pool_size 0

trace_enabled TRUE

NAME VALUE

---------------------------------------- ----------------------------------------

nls_length_semantics BYTE

nls_nchar_conv_excp FALSE

resource_manager_plan

file_mapping FALSE

sga_target 285212672

control_file_record_keep_time 7

db_block_checksum TRUE

db_cache_size 0

db_2k_cache_size 0

db_4k_cache_size 0

db_8k_cache_size 0

NAME VALUE

---------------------------------------- ----------------------------------------

db_16k_cache_size 0

db_32k_cache_size 0

db_keep_cache_size 0

db_recycle_cache_size 0

db_cache_advice ON

log_archive_config

log_archive_dest

log_archive_duplex_dest

log_archive_dest_1

log_archive_dest_2

log_archive_dest_3

NAME VALUE

---------------------------------------- ----------------------------------------

log_archive_dest_4

log_archive_dest_5

log_archive_dest_6

log_archive_dest_7

log_archive_dest_8

log_archive_dest_9

log_archive_dest_10

log_archive_dest_state_1 enable

log_archive_dest_state_2 enable

log_archive_dest_state_3 enable

log_archive_dest_state_4 enable

NAME VALUE

---------------------------------------- ----------------------------------------

log_archive_dest_state_5 enable

log_archive_dest_state_6 enable

log_archive_dest_state_7 enable

log_archive_dest_state_8 enable

log_archive_dest_state_9 enable

log_archive_dest_state_10 enable

log_archive_max_processes 2

log_archive_min_succeed_dest 1

standby_archive_dest ?/dbs/arch

log_archive_trace 0

log_archive_local_first TRUE

NAME VALUE

---------------------------------------- ----------------------------------------

fal_client

fal_server

log_checkpoint_interval 0

log_checkpoint_timeout 1800

archive_lag_target 0

db_file_multiblock_read_count 16

db_create_file_dest

db_create_online_log_dest_1

db_create_online_log_dest_2

db_create_online_log_dest_3

db_create_online_log_dest_4

NAME VALUE

---------------------------------------- ----------------------------------------

db_create_online_log_dest_5

db_recovery_file_dest /opt/oracle/flash_recovery_area

db_recovery_file_dest_size 2147483648

standby_file_management MANUAL

thread 0

fast_start_io_target 0

fast_start_mttr_target 0

log_checkpoints_to_alert FALSE

db_flashback_retention_target 1440

ddl_wait_for_locks FALSE

undo_tablespace UNDOTBS1

NAME VALUE

---------------------------------------- ----------------------------------------

undo_retention 900

fast_start_parallel_rollback LOW

resumable_timeout 0

db_block_checking FALSE

recyclebin on

create_stored_outlines

ldap_directory_access NONE

license_max_users 0

global_names FALSE

service_names test

dispatchers (PROTOCOL=TCP) (SERVICE=testXDB)

NAME VALUE

---------------------------------------- ----------------------------------------

shared_servers 1

max_shared_servers

max_dispatchers

circuits

shared_server_sessions

local_listener

remote_listener

remote_dependencies_mode TIMESTAMP

smtp_out_server

plsql_v2_compatibility FALSE

plsql_compiler_flags INTERPRETED, NON_DEBUG

NAME VALUE

---------------------------------------- ----------------------------------------

plsql_native_library_dir

plsql_native_library_subdir_count 0

plsql_warnings DISABLE:ALL

plsql_code_type INTERPRETED

plsql_debug FALSE

plsql_optimize_level 2

plsql_ccflags

job_queue_processes 10

cursor_sharing EXACT

parallel_min_servers 0

parallel_max_servers 20

NAME VALUE

---------------------------------------- ----------------------------------------

parallel_instance_group

background_dump_dest /opt/oracle/admin/test/bdump

user_dump_dest /opt/oracle/admin/test/udump

max_dump_file_size UNLIMITED

core_dump_dest /opt/oracle/admin/test/cdump

commit_write

optimizer_features_enable 10.2.0.1

fixed_date

open_cursors 300

sql_trace FALSE

optimizer_mode ALL_ROWS

NAME VALUE

---------------------------------------- ----------------------------------------

star_transformation_enabled FALSE

parallel_adaptive_multi_user TRUE

parallel_threads_per_cpu 2

optimizer_index_cost_adj 100

optimizer_index_caching 0

query_rewrite_enabled TRUE

query_rewrite_integrity enforced

pga_aggregate_target 94371840

workarea_size_policy AUTO

optimizer_dynamic_sampling 2

statistics_level TYPICAL

NAME VALUE

---------------------------------------- ----------------------------------------

skip_unusable_indexes TRUE

optimizer_secure_view_merging TRUE

aq_tm_processes 0

hs_autoregister TRUE

dg_broker_start FALSE

drs_start FALSE

dg_broker_config_file1 /opt/oracle/product/dbs/dr1test.dat

dg_broker_config_file2 /opt/oracle/product/dbs/dr2test.dat

asm_diskstring

asm_diskgroups

asm_power_limit 1

NAME VALUE

---------------------------------------- ----------------------------------------

sqltune_category DEFAULT

已选择144行。

修改后，必须重启才能生效的系统参数

SQL> select name, value from v$parameter where issys_modifiable='DEFERRED';

NAME VALUE

---------------------------------------- ----------------------------------------

backup_tape_io_slaves FALSE

audit_file_dest /opt/oracle/admin/test/adump

object_cache_optimal_size 102400

object_cache_max_size_percent 10

sort_area_size 65536

sort_area_retained_size 0

olap_page_pool_size 0

已选择7行。

SQL> alter system set background_dump_dest='/opt/oracle/admin/test/edump';

系统已更改。

SQL> select value,issys_modifiable from v$parameter where name='background_dump_dest';

VALUE ISSYS_MODIFIABLE

---------------------------------------- ---------------------------

/opt/oracle/admin/test/edump IMMEDIATE

SQL> select value, isdefault from v$parameter where name='background_dump_dest';

VALUE ISDEFAULT

---------------------------------------- ---------------------------

/opt/oracle/admin/test/edump FALSE

说明参数background_dump_dest已经不是系统创建时的默认值。