Oracle hidden parameters

The following SQL can be used to find the values of all Oracle hidden parameters in your database:

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 = ‘TRUE’))*/
order by 3;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: