Oracle 下查看隐含参数
可以通过以下方式查询Oracle的隐含参数：
set linesize 132
col name format a30
col value format a25
select
x.ksppinm "name",
y.ksppstvl "value",
y.ksppstdf "isdefault",
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%_&par%'
order by
translate(x.ksppinm, ' _', ' ')
/测试：
16:22:11 SYS@ test1 >set linesize 132
16:22:27 SYS@ test1 >col name format a30
16:22:27 SYS@ test1 >col value format a25
16:22:27 SYS@ test1 >select
16:22:27 2 x.ksppinm "name",
16:22:27 3 y.ksppstvl "value",
16:22:27 4 y.ksppstdf "isdefault",
16:22:27 5 decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
16:22:27 6 decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
16:22:27 7 from
16:22:27 8 sys.x$ksppi x,
16:22:27 9 sys.x$ksppcv y
16:22:27 10 where
16:22:27 11 x.inst_id = userenv('Instance') and
16:22:27 12 y.inst_id = userenv('Instance') and
16:22:27 13 x.indx = y.indx and
16:22:27 14 x.ksppinm like '%_&par%'
16:22:27 15 order by
16:22:27 16 translate(x.ksppinm, ' _', ' ')
16:22:27 17 /
Enter value for par: optim
old 14: x.ksppinm like '%_&par%'
new 14: x.ksppinm like '%_optim%'
name value isdefault ISMOD ISADJ
------------------------------ ------------------------- --------- ---------- -----
_add_col_optim_enabled TRUE TRUE FALSE FALSE
_aggregation_optimization_sett 0 TRUE FALSE FALSE
ings
_db_file_optimizer_read_count 8 TRUE FALSE FALSE
_db_noarch_disble_optim FALSE TRUE FALSE FALSE
_disable_cell_optimized_backup FALSE TRUE FALSE FALSE
s
_disable_sample_io_optim FALSE TRUE FALSE FALSE
_drop_table_optimization_enabl TRUE TRUE FALSE FALSE
ed
_enable_NUMA_optimization FALSE TRUE FALSE FALSE
_log_max_optimize_threads 128 TRUE FALSE FALSE
object_cache_optimal_size 102400 TRUE FALSE FALSE
_optim_adjust_for_part_skews TRUE TRUE FALSE FALSE
name value isdefault ISMOD ISADJ
------------------------------ ------------------------- --------- ---------- -----
_optim_dict_stats_at_db_cr_upg TRUE TRUE FALSE FALSE
_optim_enhance_nnull_detection TRUE TRUE FALSE FALSE
_optimizer_adaptive_cursor_sha TRUE TRUE FALSE FALSE
ring创建查询隐含参数视图：
CREATE VIEW all_parameters
AS
SELECT i.ksppinm name,
i.ksppdesc description,
CV.ksppstvl VALUE,
CV.ksppstdf isdefault,
DECODE (BITAND (CV.ksppstvf, 7),
1, 'MODIFIED',
4, 'SYSTEM_MOD',
'FALSE')
ismodified,
DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted
FROM sys.x$ksppi i, sys.x$ksppcv CV
WHERE i.inst_id = USERENV ('Instance')
AND CV.inst_id = USERENV ('Instance')
AND i.indx = CV.indx
AND i.ksppinm LIKE '/_%' ESCAPE '/'
ORDER BY REPLACE (i.ksppinm, '_', '')测试：
15:47:07 SYS@ orcl> select name,value from all_parameters;
NAME VALUE ---------------------------------------- -------------------------------------------------- _xsolapi_sql_minus_threshold 1000 _xsolapi_sql_optimize TRUE _xsolapi_sql_prepare_stmt_cache_size 16 _xsolapi_sql_remove_columns TRUE _xsolapi_sql_result_set_cache_size 32 _xsolapi_sql_symmetric_predicate TRUE _xsolapi_sql_top_dimension_hints _xsolapi_sql_top_measure_hints _xsolapi_sql_use_bind_variables TRUE _xsolapi_stringify_order_levels FALSE _xsolapi_support_mtm FALSE ......
15:47:25 SYS@ orcl>select name,value from all_parameters where name like '%resetlog%';
NAME VALUE ---------------------------------------- -------------------------------------------------- _allow_resetlogs_corruption FALSE _no_recovery_through_resetlogs FALSE
