Database Reference
In-Depth Information
----------------------------------------------------------------------------
select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',
3,'IMMEDIATE','FALSE'), decode(bitand(ksppiflg,4),4,'FALSE',
decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),
decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),
decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), ksppdesc,
ksppstcmnt, ksppihash
from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and
((translate(ksppinm,'_','#') not like '##%') and
((translate(ksppinm,'_','#') not like '#%') or (ksppstdf = 'FALSE')
or (bitand(ksppstvf,5) > 0)))
The well-known X$ tables
X$KSPPI
and
X$PSPPCV
have come to the fore. Looking at the
where-clause, it is very obvious that parameters that start with one or two underscores are
hidden from the prying eyes of DBAs thirsty for knowledge.
Next, we need to make sense out of the cryptic column names. Since the column sequence
of
GV$PARAMETER
is known, we can add the well-understandable column names of
GV$PARAMETER
as column aliases to the view definition, by traversing the select-list of the view from top to
bottom. This yields the following:
select x.inst_id AS inst_id,
x.indx+1 AS num, /* C language arrays start at offset 0,
but SQL stuff usually starts at offset 1*/
ksppinm AS name,
ksppity AS type,
ksppstvl AS value,
ksppstdvl AS display_value,
ksppstdf AS isdefault,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') AS isses_modifiable,
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',
3,'IMMEDIATE','FALSE') AS issys_modifiable,
decode(bitand(ksppiflg,4),4,'FALSE',
decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')) AS isinstance_modifiable,
decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') AS ismodified,
decode(bitand(ksppstvf,2),2,'TRUE','FALSE') AS isadjusted,
decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE') AS isdeprecated,
ksppdesc AS description,
ksppstcmnt AS update_comment,
ksppihash AS hash
