oracle 11g查隐含参数,oracle 查看隐含参数

隐藏参数 (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.xksppii,sys.xksppi i, sys.xksppii,sys.xksppcv 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 xksppix,xksppi x, xksppix,xksppcv 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 xksppix,xksppi x, xksppix,xksppcv 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 xksppia,xksppi a, xksppia,xksppcv 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 xksppia,xksppi a,

xksppia,xksppcv b,

x$ksppsv c

WHERE a.indx = b.indx

AND a.indx = c.indx

AND a.ksppinm LIKE ‘/_%’ escape ‘/’

/