隐藏参数 (hidden parameters) ，由oracle内部使用，以 ‘_’ 开头。
可以通过以下两种方式查看所有隐藏参数：
方式一：
col name for a30
col DESCRIPTION for a50
col VALUE for a50
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, '’, ‘’);
方式二：
col ksppinm for a50
col ksppstvl for a50
col ksppdesc for a50
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND TRANSLATE (ksppinm, ‘_’, ‘#’) LIKE ‘#%’;
单项查看：
示例：如果想查看_db_block_hash_buckets的参数值
col ksppinm for a50
col ksppstvl for a50
col ksppdesc for a50
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = ‘_db_block_hash_buckets’;
=================================================================================
10g&11g中查询隐藏参数：
SET PAGES 1000 LINE 200
COL parameter FOR a40
COL “Session Value” FOR a20
COL “Instance Value” FOR a20
COL IS_SESSION_MODIFIABLE FOR a20
COL IS_SYSTEM_MODIFIABLE FOR a20
SELECT a.ksppinm “Parameter”,
b.ksppstvl “Session Value”,
c.ksppstvl “Instance Value”,
DECODE (BITAND (a.ksppiflg / 256, 1), 1, ‘TRUE’, ‘FALSE’)
IS_SESSION_MODIFIABLE,
DECODE (BITAND (a.ksppiflg / 65536, 3),
1, ‘IMMEDIATE’,
2, ‘DEFERRED’,
3, ‘IMMEDIATE’,
‘FALSE’)
IS_SYSTEM_MODIFIABLE
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE ‘/_%’ ESCAPE ‘/’
/
12c中查询隐藏参数，并查询是否可以在PDB中修改：
SET PAGES 1000 LINE 200
COL parameter FOR a40
COL “Session Value” FOR a20
COL “Instance Value” FOR a20
COL IS_SESSION_MODIFIABLE FOR a20
COL IS_SYSTEM_MODIFIABLE FOR a20
COL ISPDB_MODIFIABLE FOR a20
SELECT a.ksppinm “Parameter”,
b.ksppstvl “Session Value”,
c.ksppstvl “Instance Value”,
decode(bitand(a.ksppiflg/256,1),1,‘TRUE’,‘FALSE’) IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,‘IMMEDIATE’,2,‘DEFERRED’,3,‘IMMEDIATE’,‘FALSE’) IS_SYSTEM_MODIFIABLE,
decode(bitand(a.ksppiflg/524288,1),1,‘TRUE’,‘FALSE’) ISPDB_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE ‘/_%’ escape ‘/’
/
