如何查询隐含参数的值和描述 （How To query underscore parameter in oracle database）

1.查询所有隐含参数

Doc ID 315631.1 for detail

COLUMN parameter           FORMAT a37
COLUMN description         FORMAT a30 WORD_WRAPPED
COLUMN "Session Value"     FORMAT a10
COLUMN "Instance Value"    FORMAT a10
SET LINES 100
SET PAGES 0
SPOOL undoc.lis

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 
   a.ksppinm LIKE '/_%' escape '/'
/

2.查询指定隐含参数的名，值

COL "INSTANCE" FOR A10
COL "IS_SYSTEM_MODIFIABLE" FOR A20

SELECT a.KSPPINM "PARAMETER", 
       b.KSPPSTDF "IS_DEFAULT",
       b.KSPPSTVL "SESSION", 
       c.KSPPSTVL "INSTANCE",
FROM   X$KSPPI  a,
       X$KSPPCV b,
       X$KSPPSV c
WHERE  a.INDX = b.INDX
AND    a.INDX = c.INDX
AND    a.KSPPINM LIKE '/&hidden_parameter' escape '/'
/

Usage:

SQL> @check_parameter.sql  _bct_public_dba_buffer_dynresize

3. 查询指定前缀的隐含参数的名，值，及其对应的描述

set lines 1000;
col name for a30;
col value for a20;
col description for a20;
select a.KSPPINM name,b.KSPPSTVL value , a.KSPPDESC description from x$ksppi a,x$ksppcv b where a.INDX=b.INDX and a.KSPPINM like '%&1%';

Usage:

sqlplus / as sysdba
SQL> @check_parameter.sql _bct

4.查询隐含参数的值：

select KSPPSTVL from x$ksppcv  where INDX in (select INDX from x$ksppi where KSPPINM='&1');

5.查询隐含参数的名字对应的值：

set lines 1000;
col name for a30;
col value for a20;
col description for a20;
select a.KSPPINM name,b.KSPPSTVL value from x$ksppi a,x$ksppcv b where a.INDX=b.INDX and a.KSPPINM like '%&1%';