Oracle 下查看隐含参数
可以通过以下方式查询Oracle的隐含参数：
set linesize 132col name format a30col value format a25selectx.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') isadjfromsys.x$ksppi x,sys.x$ksppcv ywherex.inst_id = userenv('Instance') andy.inst_id = userenv('Instance') andx.indx = y.indx andx.ksppinm like '%_&par%'order bytranslate(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 a2516:22:27 SYS@ test1 >select16: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') isadj16:22:27 7 from16:22:27 8 sys.x$ksppi x,16:22:27 9 sys.x$ksppcv y16:22:27 10 where16:22:27 11 x.inst_id = userenv('Instance') and16:22:27 12 y.inst_id = userenv('Instance') and16:22:27 13 x.indx = y.indx and16:22:27 14 x.ksppinm like '%_&par%'16:22:27 15 order by16:22:27 16 translate(x.ksppinm, ' _', ' ')16:22:27 17 /Enter value for par: optimold 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 FALSEings_db_file_optimizer_read_count 8 TRUE FALSE FALSE_db_noarch_disble_optim FALSE TRUE FALSE FALSE_disable_cell_optimized_backup FALSE TRUE FALSE FALSEs_disable_sample_io_optim FALSE TRUE FALSE FALSE_drop_table_optimization_enabl TRUE TRUE FALSE FALSEed_enable_NUMA_optimization FALSE TRUE FALSE FALSE_log_max_optimize_threads 128 TRUE FALSE FALSEobject_cache_optimal_size 102400 TRUE FALSE FALSE_optim_adjust_for_part_skews TRUE TRUE FALSE FALSEname 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 FALSEring 创建查询隐含参数视图：
CREATE VIEW all_parametersAS 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