script--by Steve Adams--hidden_parameters.sql

-- Script: hidden_parameters.sql
-- Purpose: to list the hidden parameters[@more@]

-------------------------------------------------------------------------------
--
-- Script: hidden_parameters.sql
-- Purpose: to list the hidden parameters
-- For: 8.0 up
--
-- Copyright: (c) 1999 Ixora Pty Ltd
-- Author: Steve Adams
--
-------------------------------------------------------------------------------
@reset_sqlplus

set linesize 128
column name format a42

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
/

@reset_sqlplus

-------------------------------------------------------------------------------
--
-- Script: all_parameters.sql
-- Purpose: to show all parameter values (including hidden ones)
-- For: 8.1
--
-- Copyright: (c) 1999 Ixora Pty Ltd
-- Author: Steve Adams
--
-------------------------------------------------------------------------------
@reset_sqlplus

set linesize 132

column name format a42
column value format a64

select
x.ksppinm name,
y.kspftctxvl value,
y.kspftctxdf isdefault,
decode(bitand(y.kspftctxvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.kspftctxvf,2),2,'TRUE','FALSE') isadj
from
sys.x_$ksppi x,
sys.x_$ksppcv2 y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx+1 = y.kspftctxpn
order by
translate(x.ksppinm, ' _', ' ')
/

@reset_sqlplus