oracle 隐藏参数查询

col  PARAMETER for a30
col  KSPPDESC  for a40
col  SESSION_VALUE for a15
col  INSTANCE_VALUE for a15
--- 隐含参数命名比较难记，所以首先通过模糊查询找到需要的参数名称
select ksppinm from x$ksppi where ksppinm like '%&like%';
---将获取到的参数名称输入即可查询出对应的值
SELECT A.KSPPINM PARAMETER, A.KSPPDESC DESCRIPTION, B.KSPPSTVL SESSION_VALUE,
C.KSPPSTVL INSTANCE_VALUE
FROM X$KSPPI A, X$KSPPCV B, X$KSPPSV C
WHERE A.INDX = B.INDX AND A.INDX = C.INDX AND
--SUBSTR(A.KSPPINM, 1, 1) = '_' and 
a.KSPPINM ='&key'
ORDER BY A.KSPPINM ;

将以上内容存储为sql 脚本，在sqlplus 提示符下调用：
SQL> @/home/oracle/ksppi
Enter value for like: small
old   1: select ksppinm from x$ksppi where ksppinm like '%&like%'
new   1: select ksppinm from x$ksppi where ksppinm like '%small%'


KSPPINM
--------------------------------------------------------------------------------
_max_small_io
_small_table_threshold
_kdli_small_cache_limit
_olap_parallel_update_small_threshold
_olap_parallel_update_small_group
_olap_dimension_corehash_small


6 rows selected.


Enter value for key: _small_table_threshold
old   6: a.KSPPINM ='&key'
new   6: a.KSPPINM ='_small_table_threshold'


PARAMETER  DESCRIPTION   SESSION_VALUE INSTANCE_VALUE
------------------------- ---------------------------------------- -------------------- --------------------
_small_table_threshold  lower threshold level of table size for  1681 1681
                                 direct reads

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21998432/viewspace-1404620/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21998432/viewspace-1404620/