Hidden Parameters

To list hidden parameters and their meanings, you can use following sql:

SELECT   x.ksppinm NAME,
DECODE (BITAND (ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') sesmod,
DECODE (BITAND (ksppiflg / 65536, 3),
1, 'IMMEDIATE',
2, 'DEFERRED',
3, 'IMMEDIATE',
'FALSE'
) sysmod,
ksppdesc description
FROM SYS.x_$ksppi x
WHERE x.inst_id = USERENV ('INSTANCE')
AND TRANSLATE (ksppinm, '_', '#') LIKE '#%'
ORDER BY 1;

To list current values of hidden parameters:

SELECT   a.ksppinm "Parameter", 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 SUBSTR (ksppinm, 1, 1) = '_'
ORDER BY a.ksppinm;