The Oracle (tm) Users' Co-Operative FAQ

How can I find what the values are for the hidden (underscore) parameters ?

Back to index of questions

In trying to hunt down hidden parameters, we start by looking at the V$PARAMETER table to see where it sources its information from

SQL> select VIEW_DEFINITION 
  2  from v$fixed_view_definition
  3  where view_name = 'V$PARAMETER';

select  NUM , NAME , TYPE , VALUE , ISDEFAULT , ISSES_MODIFIABLE , 
        ISSYS_MODIFIABLE , ISMODIFIED , ISADJUSTED , DESCRIPTION 
from GV$PARAMETER where inst_id = USERENV('Instance')

So lets try that again - this time GV$PARAMETER (the consolidated view of parameters across all instances)

SQL> select VIEW_DEFINITION 
  2  from v$fixed_view_definition
  3  where view_name = 'GV$PARAMETER';

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl,ksppstdf,  
       decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
       decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE'),  
       decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),  
       decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),  
       ksppdesc 
from   x$ksppi x, 
       x$ksppcv y 
where (x.indx = y.indx) 
and  (translate(ksppinm,'_','#') not like '#%' 
or   (translate(ksppinm,'_','#') like '#%'and ksppstdf = 'FALSE'))

From this output, its relatively straight forward to generate a query to list the hidden parameters and their descriptions

select KSPPINM  name,
       KSPPDESC description
from   X$KSPPI
where  substr(KSPPINM,1,1) = '_'

which of course will only work as SYS. I have not included the values because whilst the hidden parameters have an associated entry in X$KSPPCV, this "value" is often ZERO, which is not necessarily the value actually in use by the instance, so interpreting them should take this into consideration. Similarly in OPS environments, you can restrict this for a particular instance in the same way that V$PARAMETER does

Further reading: N/A

Back to top

Back to index of questions