查询Oracle数据库中所有隐藏参数及默认值(10g、11g、12c)
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 '/'
- /