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%';