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