让sqlplus 里的show parameter 直接显示oracle隐含参数
Oracle 启动用到了初始化参数，通常我们都可以在sqlplus里用show parameter 来获得。而Oracle为了debug 或者解决一些特殊的问题，还提供了一些以“_”开头的隐含参数。 网络上有一些script可以让我们轻松检查这些隐含参数. 但是，有没有想过,我们可以直接在sqlplus 用show parameter 来获取这些隐含参数呢？答案是[b]可以[/b]的。
下面，我将演示一下如何用sqlplus 里的 show parameter来直接查看Oracle隐含参数.
1。首先，我们先要知道，sqlplus怎样通过show parameter获得参数的。通过sql_trace的方式可以发现。 [indent]PHP code:[color=#000000]
[color=#0000bb]
sqlplus serol[/color][color=#007700]/[/color][color=#0000bb]luo
alter session set sql_trace[/color][color=#007700]=[/color][color=#0000bb]true[/color][color=#007700];
[/color][color=#0000bb]show parameter optimizer
NAME TYPE VALUE
[/color][color=#007700]------------------------------------ -------------------------------- ------------------------------
[/color][color=#0000bb]optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 1
optimizer_max_permutations integer 2000
optimizer_mode string RULE
[/color][color=#007700]exit
...
[/color][/indent]到udump底下找到trace文件，里面的最主要的语句是： [indent]PHP code:[color=#000000]
[color=#0000bb]
SELECT NAME name_col_plus_show_param[/color][color=#007700],[/color][color=#0000bb]DECODE[/color][color=#007700]([/color][color=#0000bb]TYPE[/color][color=#007700],[/color][color=#0000bb]1[/color][color=#007700],[/color][color=#dd0000]'boolean'[/color][color=#007700],[/color][color=#0000bb]2[/color][color=#007700],[/color][color=#dd0000]'string'[/color][color=#007700],[/color][color=#0000bb]3[/color][color=#007700],[/color][color=#dd0000]'integer'[/color][color=#007700],[/color][color=#0000bb]4[/color][color=#007700],[/color][color=#dd0000]'file'[/color][color=#007700],[/color][color=#0000bb]6[/color][color=#007700],[/color][color=#dd0000]'big
integer'[/color][color=#007700],[/color][color=#dd0000]'unknown'[/color][color=#007700]) [/color][color=#0000bb]TYPE[/color][color=#007700],[/color][color=#0000bb]VALUE value_col_plus_show_param
FROM V$PARAMETER WHERE UPPER[/color][color=#007700]([/color][color=#0000bb]NAME[/color][color=#007700]) [/color][color=#0000bb]LIKE UPP
ER[/color][color=#007700]([/color][color=#dd0000]'%optimizer%'[/color][color=#007700]) [/color][color=#0000bb]ORDER BY name_col_plus_show_param[/color][color=#007700],
[/color][color=#0000bb]ROWNUM
[/color][color=#007700]...
[/color][/indent]可以看到，sqlplus的show parameter实际上就是查询V$PARAMETER .
2. 看看 v$parameter的定义。 [indent]PHP code:[color=#000000]
[color=#0000bb]
SELECT [/color][color=#007700]* [/color][color=#0000bb]FROM v$fixed_view_definition
WHERE view_name [/color][color=#007700]= [/color][color=#dd0000]'V$PARAMETER'[/color][color=#007700];
[/color][color=#0000bb]select NUM [/color][color=#007700], [/color][color=#0000bb]NAME [/color][color=#007700], [/color][color=#0000bb]TYPE [/color][color=#007700], [/color][color=#0000bb]VALUE [/color][color=#007700], [/color][color=#0000bb]ISDEFAULT [/color][color=#007700], [/color][color=#0000bb]ISSES_MODIFIABLE [/color][color=#007700], [/color][color=#0000bb]ISSYS_MODIFIABLE [/color][color=#007700], [/color][color=#0000bb]ISMODIFIED [/color][color=#007700],
[/color][color=#0000bb]ISADJUSTED [/color][color=#007700], [/color][color=#0000bb]DESCRIPTION[/color][color=#007700], [/color][color=#0000bb]UPDATE_COMMENT from GV$PARAMETER where inst_id [/color][color=#007700]= [/color][color=#0000bb]USERENV[/color][color=#007700]([/color][color=#dd0000]'Instance'[/color][color=#007700])
[/color][color=#0000bb]SELECT [/color][color=#007700]* [/color][color=#0000bb]FROM v$fixed_view_definition
WHERE view_name [/color][color=#007700]= [/color][color=#dd0000]'GV$PARAMETER'[/color][color=#007700];
[/color][color=#0000bb]select x[/color][color=#007700].[/color][color=#0000bb]inst_id[/color][color=#007700],[/color][color=#0000bb]x[/color][color=#007700].[/color][color=#0000bb]indx[/color][color=#007700]+[/color][color=#0000bb]1[/color][color=#007700],[/color][color=#0000bb]ksppinm[/color][color=#007700],[/color][color=#0000bb]ksppity[/color][color=#007700],[/color][color=#0000bb]ksppstvl[/color][color=#007700],[/color][color=#0000bb]ksppstdf[/color][color=#007700],
[/color][color=#0000bb]decode[/color][color=#007700]([/color][color=#0000bb]bitand[/color][color=#007700]([/color][color=#0000bb]ksppiflg[/color][color=#007700]/[/color][color=#0000bb]256[/color][color=#007700],[/color][color=#0000bb]1[/color][color=#007700]),[/color][color=#0000bb]1[/color][color=#007700],[/color][color=#dd0000]'TRUE'[/color][color=#007700],[/color][color=#dd0000]'FALSE'[/color][color=#007700]),
[/color][color=#0000bb]decode[/color][color=#007700]([/color][color=#0000bb]bitand[/color][color=#007700]([/color][color=#0000bb]ksppiflg[/color][color=#007700]/[/color][color=#0000bb]65536[/color][color=#007700],[/color][color=#0000bb]3[/color][color=#007700]),[/color][color=#0000bb]1[/color][color=#007700],[/color][color=#dd0000]'IMMEDIATE'[/color][color=#007700],[/color][color=#0000bb]2[/color][color=#007700],
[/color][color=#dd0000]'DEFERRED'[/color][color=#007700],
[/color][color=#0000bb]3[/color][color=#007700],[/color][color=#dd0000]'IMMEDIATE'[/color][color=#007700],[/color][color=#dd0000]'FALSE'[/color][color=#007700]), [/color][color=#0000bb]decode[/color][color=#007700]([/color][color=#0000bb]bitand
[/color][color=#007700]([/color][color=#0000bb]ksppstvf[/color][color=#007700],[/color][color=#0000bb]7[/color][color=#007700]),[/color][color=#0000bb]1[/color][color=#007700],[/color][color=#dd0000]'MODIFIED'[/color][color=#007700],[/color][color=#0000bb]4[/color][color=#007700],[/color][color=#dd0000]'SYSTEM_MOD'[/color][color=#007700],[/color][color=#dd0000]'FALSE'[/color][color=#007700]),
[/color][color=#0000bb]decode[/color][color=#007700]([/color][color=#0000bb]bitand[/color][color=#007700]([/color][color=#0000bb]ksppstvf[/color][color=#007700],[/color][color=#0000bb]2[/color][color=#007700]),[/color][color=#0000bb]2[/color][color=#007700],[/color][color=#dd0000]'TRUE'[/color][color=#007700],[/color][color=#dd0000]'FALSE'[/color][color=#007700]), [/color][color=#0000bb]ksppdesc[/color][color=#007700], [/color][color=#0000bb]ksppstcmnt
from x$ksppi x[/color][color=#007700], [/color][color=#0000bb]x$ksppcv y
where [/color][color=#007700]([/color][color=#0000bb]x[/color][color=#007700].[/color][color=#0000bb]indx [/color][color=#007700]= [/color][color=#0000bb]y[/color][color=#007700].[/color][color=#0000bb]indx[/color][color=#007700]) and ( ([/color][color=#0000bb]translate[/color][color=#007700]([/color][color=#0000bb]ksppinm[/color][color=#007700],[/color][color=#dd0000]'_'[/color][color=#007700],[/color][color=#dd0000]'#'[/color][color=#007700]) [/color][color=#0000bb]not like [/color][color=#dd0000]'#%'[/color][color=#007700])
or ([/color][color=#0000bb]ksppstdf [/color][color=#007700]= [/color][color=#dd0000]'FALSE'[/color][color=#007700]) )
...
[/color][/indent]看到上面最关键的 (translate(ksppinm,'_','#') not like '#%')的条件了吗？，就是它把开头为"_" 的隐含参数给过滤掉了。当然，ksppstdf = 'FALSE' 的条件是为了保证如果已经手动改过隐含参数，show parameter还会显示改隐含参数
3。我们可以伪造一个v$parameter, 去掉过滤"_"的条件，就可以大功告成了。
不过，毕竟是改动系统的内部设置，大家一定要小心为上。
一定要以一个仅用于数据库管理，不会参加任何程序活动的管理性数据库用户来伪造这个v$parameter，以免发生意外。而且，最好是在测试环境中玩，而不要跑到生产环境里面。下面假设这个用户名为：rollingpig.
以sys 登陆 [indent]PHP code:[color=#000000]
[color=#0000bb]
connect [/color][color=#007700]/ as [/color][color=#0000bb]sysdba
create view my_v$parameter_with_hidden [/color][color=#007700]([/color][color=#0000bb]NUM[/color][color=#007700],[/color][color=#0000bb]NAME [/color][color=#007700], [/color][color=#0000bb]TYPE
[/color][color=#007700], [/color][color=#0000bb]Value [/color][color=#007700], [/color][color=#0000bb]ISDEFAULT [/color][color=#007700], [/color][color=#0000bb]ISSES_MODIFIABLE [/color][color=#007700], [/color][color=#0000bb]ISSYS_MODIFIABLE [/color][color=#007700],[/color][color=#0000bb]ISMODIFIED [/color][color=#007700], [/color][color=#0000bb]ISADJUSTED[/color][color=#007700],
[/color][color=#0000bb]DESCRIPTION[/color][color=#007700], [/color][color=#0000bb]UPDATE_COMMENT[/color][color=#007700])
as
[/color][color=#0000bb]select x[/color][color=#007700].[/color][color=#0000bb]indx[/color][color=#007700]+[/color][color=#0000bb]1[/color][color=#007700],[/color][color=#0000bb]ksppinm[/color][color=#007700],[/color][color=#0000bb]ksppity[/color][color=#007700],[/color][color=#0000bb]ksppstvl[/color][color=#007700],[/color][color=#0000bb]ksppstdf[/color][color=#007700],
[/color][color=#0000bb]decode[/color][color=#007700]([/color][color=#0000bb]bitand[/color][color=#007700]([/color][color=#0000bb]ksppiflg[/color][color=#007700]/[/color][color=#0000bb]256[/color][color=#007700],[/color][color=#0000bb]1[/color][color=#007700]),[/color][color=#0000bb]1[/color][color=#007700],[/color][color=#dd0000]'TRUE'[/color][color=#007700],[/color][color=#dd0000]'FALSE'[/color][color=#007700]),
[/color][color=#0000bb]decode[/color][color=#007700]([/color][color=#0000bb]bitand[/color][color=#007700]([/color][color=#0000bb]ksppiflg[/color][color=#007700]/[/color][color=#0000bb]65536[/color][color=#007700],[/color][color=#0000bb]3[/color][color=#007700]),[/color][color=#0000bb]1[/color][color=#007700],[/color][color=#dd0000]'IMMEDIATE'[/color][color=#007700],[/color][color=#0000bb]2[/color][color=#007700],[/color][color=#dd0000]'DEFERRED'[/color][color=#007700],
[/color][color=#0000bb]3[/color][color=#007700],[/color][color=#dd0000]'IMMEDIATE'[/color][color=#007700],[/color][color=#dd0000]'FALSE'[/color][color=#007700]), [/color][color=#0000bb]decode[/color][color=#007700]([/color][color=#0000bb]bitand[/color][color=#007700]([/color][color=#0000bb]ksppstvf[/color][color=#007700],[/color][color=#0000bb]7[/color][color=#007700]),[/color][color=#0000bb]1[/color][color=#007700],[/color][color=#dd0000]'MODIFIED'[/color][color=#007700],[/color][color=#0000bb]4[/color][color=#007700],[/color][color=#dd0000]'SYSTEM_MOD'[/color][color=#007700],[/color][color=#dd0000]'FALSE'[/color][color=#007700]),
[/color][color=#0000bb]decode[/color][color=#007700]([/color][color=#0000bb]bitand[/color][color=#007700]([/color][color=#0000bb]ksppstvf[/color][color=#007700],[/color][color=#0000bb]2[/color][color=#007700]),[/color][color=#0000bb]2[/color][color=#007700],[/color][color=#dd0000]'TRUE'[/color][color=#007700],[/color][color=#dd0000]'FALSE'[/color][color=#007700]),
[/color][color=#0000bb]ksppdesc[/color][color=#007700], [/color][color=#0000bb]ksppstcmnt
from x$ksppi x[/color][color=#007700], [/color][color=#0000bb]x$ksppcv y
where [/color][color=#007700]([/color][color=#0000bb]x[/color][color=#007700].[/color][color=#0000bb]indx [/color][color=#007700]= [/color][color=#0000bb]y[/color][color=#007700].[/color][color=#0000bb]indx[/color][color=#007700]);
[/color][color=#0000bb]grant select on my_v$parameter_with_hidden to rollingpig[/color][color=#007700];
[/color][color=#0000bb]以 rollingpig 用户连接[/color][color=#007700],[/color][color=#0000bb]创建同义词：
create synonym v$parameter [/color][color=#007700]for [/color][color=#0000bb]sys[/color][color=#007700].[/color][color=#0000bb]my_v$parameter_with_hidden [/color][color=#007700];
...
[/color][/indent]4。大功告成，测试一下吧
以rollingpig 用户登陆： [indent]PHP code:[color=#000000]
[color=#0000bb]
show parameter optimizer
SQL[/color][color=#007700]> [/color][color=#0000bb]show parameter optimizer
NAME TYPE VALUE
[/color][color=#007700]------------------------------------ -------------------------------- ------------------------------
[/color][color=#0000bb]_optimizer_adjust_for_nulls boolean TRUE
_optimizer_choose_permutation integer 0
_optimizer_cost_model string CHOOSE
_optimizer_degree integer 0
_optimizer_dyn_smp_blks integer 32
_optimizer_join_sel_sanity_check boolean FALSE
_optimizer_mode_force boolean TRUE
_optimizer_new_join_card_computation boolean TRUE
_optimizer_percent_parallel integer 101
_optimizer_search_limit integer 5
_optimizer_skip_scan_enabled boolean TRUE
_optimizer_sortmerge_join_enabled boolean TRUE
_optimizer_system_stats_usage integer 0
_optimizer_undo_changes boolean FALSE
_optimizer_undo_cost_change string 9.2.0.1
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 1
optimizer_max_permutations integer 2000
optimizer_mode string RULE
[/color][color=#007700]...
[/color][/indent]以"_"开头的隐含参数一个给跑出来了，optimizer开头的还真不少 :/lengh
还有一些明显是测试用的hidden parameter [indent]PHP code:[color=#000000]
[color=#0000bb]
SQL[/color][color=#007700]> [/color][color=#0000bb]show parameter test
NAME TYPE VALUE
[/color][color=#007700]------------------------------------ -------------------------------- ------------------------------
[/color][color=#0000bb]_ipc_test_failover integer 0
_ipc_test_mult_nets integer 0
_test_ksusigskip integer 5
_test_param_1 integer 25
_test_param_2 string
_test_param_3 string
_test_param_4 string
_test_param_5 integer 25
_test_param_6 big integer 0
[/color][color=#007700]...
[/color][/color][/indent][/color][/color][/color][/color][/color]最后重申一点：
[b]是在测试环境中玩，不要跑到生产环境里面中玩哦……[/b]
页:
[1]