二、隐藏参数
Oracle 系统中还有一类参数称之为隐藏参数 (hidden parameters) ，是系统中使用，但 Oracle 官方没有公布的参数，这些参数可能是那些还没有成熟或者是系统开发中使用的参数。这些参数在所有 Oracle 官方提供的文档中都没有介绍，他们的命名有一个共同特征就是都以 '_' 作为参数的首字符，诸如 Oracle 8i 中的 _trace_files_public 和 _lock_sga_areas 等等。 特别声明：用到隐含参数时要谨慎。
下面的查询可以得到当前系统中的所有隐藏参数 ( 以 sys 身份登录 ) ：
SQL> col ksppinm for a30
SQL> col ksppstvl for a5
SQL> col ksppdesc for a25
SQL> select ksppinm,ksppstvl,ksppdesc
2 from x$ksppi x,x$ksppcv y
3 where x.indx=y.indx
4 and translate(ksppinm,'_','#') like '#%';
_ior_serialize_fault 0 inject fault in the ior s
erialize code
_inject_startup_fault 0 inject fault in the start
up code
_latch_recovery_alignment 65534 align latch recovery stru
ctures
_spin_count 2000 Amount to spin waiting fo
r a latch
_latch_miss_stat_sid 0 Sid of process for which
to collect latch stats
_max_sleep_holding_latch 4 max time to sleep while h
olding a latch
_max_exponential_sleep 0 max sleep during exponent
ial backoff
_other_wait_threshold 0 threshold wait percentage
for event wait class Oth
er
_other_wait_event_exclusion exclude event names from
_other_wait_threshold cal
culations
_use_vector_post TRUE use vector post
_latch_class_0 latch class 0
_latch_class_1 latch class 1
_latch_class_2 latch class 2
_latch_class_3 latch class 3
_latch_class_4 latch class 4
_latch_class_5 latch class 5
_latch_class_6 latch class 6
_latch_class_7 latch class 7
_latch_classes latch classes override
_ultrafast_latch_statistics TRUE maintain fast-path statis
tics for ultrafast latche
s
_enable_reliable_latch_waits TRUE Enable reliable latch wai
ts
_wait_breakup_time_csecs 300 Wait breakup time (in cen
tiseconds)
_wait_breakup_threshold_csecs 600 Wait breakup threshold (i
n centiseconds)
_disable_wait_stack FALSE Disable wait stack
_session_idle_bit_latches 0 one latch per session or
a latch per group of sess
ions
_ksu_diag_kill_time 5 number of seconds ksuitm
waits before killing diag
_ksuitm_dont_kill_dumper FALSE delay inst. termination t
o allow processes to dump
_disable_image_check FALSE Disable Oracle executable
image checking
_num_longop_child_latches 2 number of child latches f
or long op array
_longops_enabled TRUE longops stats enabled
_test_ksusigskip 5 test the function ksusigs
kip
_disable_kcbhxor_osd FALSE disable kcbh(c)xor OSD fu
nctionality
_disable_system_state 42949 disable system state dump
67294
_session_wait_history 10 enable session wait histo
ry collection
_session_idle_check_interval 60 Resource Manager session
idle limit check interval
in seconds
_pkt_enable FALSE enable progressive kill t
est
_pkt_start FALSE start progressive kill te
st instrumention
_pkt_pmon_interval 50 PMON process clean-up int
erval (cs)
_dead_process_scan_interval 60 PMON dead process scan in
terval
_collapse_wait_history FALSE collapse wait history
_short_stack_timeout_ms 30000 short stack timeout in ms
_sga_early_trace 0 sga early trace event
_kill_session_dump TRUE Process dump on kill sess
ion immediate
_logout_storm_rate 0 number of processes that
can logout in a second
_logout_storm_timeout 5 timeout in centi-seconds
for time to wait between
retries
_logout_storm_retrycnt 600 maximum retry count for l
ogouts
_ksuitm_addon_trccmd command to execute when d
ead processes don't go aw
ay
_timeout_actions_enabled TRUE enables or disables KSU t
imeout actions
_idle_session_kill_enabled TRUE enables or disables resou
rce manager session idle
limit checks
_disable_vktm FALSE disable vktm process
_disable_highres_ticks FALSE disable high-res tick counter
鉴于隐含参数有1627行，这里只列出一部分，在留言板中将列出全部。这里绝大多数对我们来说是用不到的。再次声明:对于隐含参数 我们要慎用。
三、系统当前参数
下面的脚本以英文字母顺序列出了系统当前使用的所有参数。在列出的参数中，如果参数名称前面有 # 这个符号，则表示该参数没有明确指定，采用了系统中的默认参数。一般在一个新的 Oracle 版本安装完成后，首先运行该脚本，则可以生成该版本数据库的标准 init.ora 文件。
SQL>SELECT DECODE(isdefault, 'TRUE', '# ') || DECODE(isdefault, 'TRUE', RPAD(name,43), RPAD(name,45)) || ' = ' || value
2 FROM v$parameter
3 ORDER BY name;
注意：上面的 SQL 脚本没有列出系统中的隐藏参数。
# O7_DICTIONARY_ACCESSIBILITY = FALSE
# active_instance_count =
# aq_tm_processes = 0
# archive_lag_target = 0
# asm_diskgroups =
# asm_diskstring =
# asm_power_limit = 1
# asm_preferred_read_failure_groups =
audit_file_dest = F:\APP\YANG\ADMIN\ORACL\ADUMP
# audit_sys_operations = FALSE
audit_trail = DB
# background_core_dump = partial
# background_dump_dest = f:\app\yang\diag\rdbms\oracl\ora
cl\trace
# backup_tape_io_slaves = FALSE
# bitmap_merge_area_size = 1048576
# blank_trimming = FALSE
# buffer_pool_keep =
# buffer_pool_recycle =
# circuits =
# client_result_cache_lag = 3000
# client_result_cache_size = 0
# cluster_database = FALSE
# cluster_database_instances = 1
# cluster_interconnects =
# commit_logging =
# commit_point_strength = 1
# commit_wait =
# commit_write =
compatible = 11.1.0.0.0
# control_file_record_keep_time = 7
control_files = F:\APP\YANG\ORADATA\ORACL\CONTRO
L01.CTL, F:\APP\YANG\ORADATA\ORACL\CONTROL02.CTL, F:\APP\YANG\ORADATA\ORACL\CONT
ROL03.CTL
# control_management_pack_access = DIAGNOSTIC+TUNING
# core_dump_dest = f:\app\yang\diag\rdbms\oracl\ora
cl\cdump
# cpu_count = 2
# create_bitmap_area_size = 8388608
# create_stored_outlines =
# cursor_sharing = EXACT
# cursor_space_for_time = FALSE
# db_16k_cache_size = 0
# db_2k_cache_size = 0
# db_32k_cache_size = 0
# db_4k_cache_size = 0
# db_8k_cache_size = 0
# db_block_buffers = 0
# db_block_checking = FALSE
# db_block_checksum = TYPICAL
db_block_size = 8192
# db_cache_advice = ON
# db_cache_size = 0
# 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 =
# db_create_online_log_dest_5 =
db_domain =
# db_file_multiblock_read_count = 128
# db_file_name_convert =
# db_files = 200
# db_flashback_retention_target = 1440
# db_keep_cache_size = 0
# db_lost_write_protect = NONE
db_name = oracl
db_recovery_file_dest = F:\app\yang\flash_recovery_area
db_recovery_file_dest_size = 2147483648
# db_recycle_cache_size = 0
# db_securefile = PERMITTED
# db_ultra_safe = OFF
# db_unique_name = oracl
# db_writer_processes = 1
# dbwr_io_slaves = 0
# ddl_lock_timeout = 0
# dg_broker_config_file1 = F:\APP\YANG\PRODUCT\11.1.0\DB_1\
DATABASE\DR1ORACL.DAT
# dg_broker_config_file2 = F:\APP\YANG\PRODUCT\11.1.0\DB_1\
DATABASE\DR2ORACL.DAT
# dg_broker_start = FALSE
diagnostic_dest = F:\APP\YANG
# disk_asynch_io = TRUE
dispatchers = (PROTOCOL=TCP) (SERVICE=oraclXDB
)
# distributed_lock_timeout = 60
# dml_locks = 748
# drs_start = FALSE
# enable_ddl_logging = FALSE
# event =
# fal_client =
# fal_server =
# fast_start_io_target = 0
# fast_start_mttr_target = 0
# fast_start_parallel_rollback = LOW
# file_mapping = FALSE
# fileio_network_adapters =
# filesystemio_options =
# fixed_date =
# gc_files_to_locks =
# gcs_server_processes = 0
# global_context_pool_size =
# global_names = FALSE
# global_txn_processes = 1
# hash_area_size = 131072
# hi_shared_memory_address = 0
# hs_autoregister = TRUE
# ifile =
# instance_groups =
# instance_name = oracl
# instance_number = 0
# instance_type = RDBMS
# java_jit_enabled = TRUE
# java_max_sessionspace_size = 0
# java_pool_size = 0
# java_soft_sessionspace_limit = 0
# job_queue_processes = 1000
# large_pool_size = 0
# ldap_directory_access = NONE
# ldap_directory_sysauth = no
# license_max_sessions = 0
# license_max_users = 0
# license_sessions_warning = 0
# local_listener =
# lock_name_space =
# lock_sga = FALSE
# log_archive_config =
# log_archive_dest =
log_archive_dest_1 = location=f:\app\yang\archive1
# log_archive_dest_10 =
log_archive_dest_2 = location=f:\app\yang\archive2
# log_archive_dest_3 =
# 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_state_1 = ENABLE
# log_archive_dest_state_10 = enable
# log_archive_dest_state_2 = enable
# log_archive_dest_state_3 = enable
# log_archive_dest_state_4 = enable
# 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_duplex_dest =
log_archive_format = %s_%t_%r.log
# log_archive_local_first = TRUE
log_archive_max_processes = 5
log_archive_min_succeed_dest = 2
log_archive_start = TRUE
# log_archive_trace = 0
# log_buffer = 5653504
# log_checkpoint_interval = 0
# log_checkpoint_timeout = 1800
# log_checkpoints_to_alert = FALSE
# log_file_name_convert =
# max_commit_propagation_delay = 0
# max_dispatchers =
# max_dump_file_size = unlimited
# max_enabled_roles = 150
# max_shared_servers =
# memory_max_target = 859832320
memory_target = 859832320
# nls_calendar =
# nls_comp = BINARY
# nls_currency =
# nls_date_format =
# nls_date_language =
# nls_dual_currency =
# nls_iso_currency =
# nls_language = AMERICAN
# nls_length_semantics = BYTE
# nls_nchar_conv_excp = FALSE
# nls_numeric_characters =
# nls_sort =
# nls_territory = AMERICA
# nls_time_format =
# nls_time_tz_format =
# nls_timestamp_format =
# nls_timestamp_tz_format =
# object_cache_max_size_percent = 10
# object_cache_optimal_size = 102400
# olap_page_pool_size = 0
open_cursors = 300
# open_links = 4
# open_links_per_instance = 4
# optimizer_capture_sql_plan_baselines = FALSE
# optimizer_dynamic_sampling = 2
# optimizer_features_enable = 11.1.0.6
# optimizer_index_caching = 0
# optimizer_index_cost_adj = 100
# optimizer_mode = ALL_ROWS
# optimizer_secure_view_merging = TRUE
# optimizer_use_invisible_indexes = FALSE
# optimizer_use_pending_statistics = FALSE
# optimizer_use_sql_plan_baselines = TRUE
# os_authent_prefix = OPS$
# os_roles = FALSE
# parallel_adaptive_multi_user = TRUE
# parallel_automatic_tuning = FALSE
# parallel_execution_message_size = 2148
# parallel_instance_group =
# parallel_io_cap_enabled = FALSE
# parallel_max_servers = 20
# parallel_min_percent = 0
# parallel_min_servers = 0
# parallel_server = FALSE
# parallel_server_instances = 1
# parallel_threads_per_cpu = 2
# pga_aggregate_target = 0
# plscope_settings = IDENTIFIERS:NONE
# plsql_ccflags =
# plsql_code_type = INTERPRETED
# plsql_debug = FALSE
# plsql_native_library_dir =
# plsql_native_library_subdir_count = 0
# plsql_optimize_level = 2
# plsql_v2_compatibility = FALSE
# plsql_warnings = DISABLE:ALL
# pre_page_sga = FALSE
processes = 150
# query_rewrite_enabled = TRUE
# query_rewrite_integrity = enforced
# rdbms_server_dn =
# read_only_open_delayed = FALSE
# recovery_parallelism = 0
# recyclebin = on
# redo_transport_user =
# remote_dependencies_mode = TIMESTAMP
# remote_listener =
remote_login_passwordfile = EXCLUSIVE
# remote_os_authent = FALSE
# remote_os_roles = FALSE
# replication_dependency_tracking = TRUE
# resource_limit = FALSE
# resource_manager_cpu_allocation = 2
# resource_manager_plan = SCHEDULER[0x2C0E]:DEFAULT_MAINTE
NANCE_PLAN
# result_cache_max_result = 5
# result_cache_max_size = 1245184
# result_cache_mode = MANUAL
# result_cache_remote_expiration = 0
# resumable_timeout = 0
# rollback_segments =
# sec_case_sensitive_logon = TRUE
# sec_max_failed_login_attempts = 10
# sec_protocol_error_further_action = CONTINUE
# sec_protocol_error_trace_action = TRACE
# sec_return_server_release_banner = FALSE
# serial_reuse = disable
# service_names = oracl
# session_cached_cursors = 50
# session_max_open_files = 10
# sessions = 170
# sga_max_size = 536870912
# sga_target = 0
# shadow_core_dump = none
# shared_memory_address = 0
# shared_pool_reserved_size = 6081740
shared_pool_size = 121634816
# shared_server_sessions =
# shared_servers = 1
# skip_unusable_indexes = TRUE
# smtp_out_server =
# sort_area_retained_size = 0
# sort_area_size = 65536
# spfile = F:\APP\YANG\PRODUCT\11.1.0\DB_1\
DATABASE\SPFILEORACL.ORA
# sql92_security = FALSE
# sql_trace = FALSE
# sql_version = NATIVE
# sqltune_category = DEFAULT
# standby_archive_dest = %ORACLE_HOME%\RDBMS
# standby_file_management = MANUAL
# star_transformation_enabled = FALSE
# statistics_level = TYPICAL
# streams_pool_size = 0
# tape_asynch_io = TRUE
# thread = 0
# timed_os_statistics = 0
# timed_statistics = TRUE
# trace_enabled = TRUE
# tracefile_identifier =
# transactions = 187
# transactions_per_rollback_segment = 5
# undo_management = AUTO
undo_retention = 900
undo_tablespace = UNDOTBS1
# use_indirect_data_buffers = FALSE
# user_dump_dest = f:\app\yang\diag\rdbms\oracl\ora
cl\trace
# utl_file_dir =
# workarea_size_policy = AUTO
# xml_db_events = enable
已选择288行。