Viswamitra's blog

I got this from some another blog. This is just for my reference.

SET linesize 200 col PARAMETER
FOR a30 col "Session Value"
FOR a10 col "Instance Value"
FOR a10 col "Default value"
FOR a5 col SESSMOD
FOR a7 col SYSMOD
FOR a9 col Description
FOR a45

SELECT a.ksppinm "Parameter",
       b.ksppstvl "Session Value",
       c.ksppstvl "Instance Value",
       b.ksppstdf "Default value",
       decode(bitand(a.ksppiflg/256,3),1, 'True', 'False') SESSMOD,
       decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') SYSMOD,
       a.ksppdesc "Description"
FROM sys.x$ksppi a,
                 sys.x$ksppcv b,
                              sys.x$ksppsv c
WHERE a.indx = b.indx
  AND a.indx = c.indx
  AND substr(ksppinm,1,100) LIKE '_enable_shared_pool_%'
ORDER BY a.ksppinm;