在這裡編輯新頁面「désert/Oracle/Parameters」

= HOW TO QUERY THE HIDDEN PARAMETERS IN AN ORACLE DATABASE =
{{{
set linesize 2000
COLUMN parameter      FORMAT a37
COLUMN description    FORMAT a75 WORD_WRAPPED
COLUMN session_value  FORMAT a10
COLUMN instance_value FORMAT a10


SELECT a.ksppinm AS parameter,
       a.ksppdesc AS description,
       b.ksppstvl AS session_value,
       c.ksppstvl AS 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 '/'
ORDER BY a.ksppinm;
}}}

{{{
    SELECT a.ksppinm AS parameter,
  	     a.ksppdesc AS description,
       b.ksppstvl AS session_value,
       c.ksppstvl AS 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 '/'
  AND a.ksppinm LIKE '%numa%'
}}}


{{{
SET LINES 999
SET PAGES 300
COL "PARAMETER" FOR A40 
COL "IS_DEFAULT" FOR A10
COL "SESSION" FOR A10
COL "INSTANCE" FOR A10
COL "IS_SESSION_MODIFIABLE" FOR A21
COL "IS_SYSTEM_MODIFIABLE" FOR A20

SELECT a.KSPPINM "PARAMETER", 
       b.KSPPSTDF "IS_DEFAULT",
       b.KSPPSTVL "SESSION", 
       c.KSPPSTVL "INSTANCE",
       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 '/'
AND    a.KSPPINM LIKE NVL('%&hidden_parameter%','')
/
}}}