Oracle: view hidden (undocumented) parameters
Read the value and properties of a hidden Oracle parameter using the X$KSPPI, X$KSPPCV and X$KSPPSV tables.
Oracle hides some initialization parameters (those starting with an underscore). They are not shown in V$PARAMETER, but they can be read from the internal X$ tables as SYS. The example below uses _gby_hash_aggregation_enabled.
Changing a hidden parameter without guidance from Oracle Support may seriously damage your database. Use these queries to read values only.
1. V$PARAMETER does not list hidden parameters
SELECT name, value, isdefault, isses_modifiable, issys_modifiable
FROM v$parameter
WHERE name = '_gby_hash_aggregation_enabled';
no rows selected
2. Read the value of a hidden parameter
set lines 200
col Parameter for a40
col "Session Value" for a25
col "Instance Value" for a25
SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_gby%hash%' escape '/';
Parameter Session Value Instance Value
---------------------------------------- ------------------------- -------------------------
_gby_hash_aggregation_enabled TRUE TRUE
3. Detailed information (default, modifiable, description)
This query also tells whether the parameter can be changed at session or system level.
set lines 200
col name for a33
col value for a20
col description for a50
col IS_SESSION_MODIFIABLE for a25
col IS_SYSTEM_MODIFIABLE for a25
col IS_MODIFIED for a25
select par.ksppinm name,
val.ksppstvl value,
par.ksppity type,
val.ksppstdf is_default,
decode(bitand(par.ksppiflg/256,1), 1,'True', 'False') is_session_modifiable,
decode(bitand(par.ksppiflg/65536,3), 1,'Immediate', 2,'Deferred', 3,'Immediate', 'False') is_system_modifiable,
par.ksppdesc description
from x$ksppi par, x$ksppsv val
where par.indx = val.indx
and par.ksppinm = '_gby_hash_aggregation_enabled';
NAME VALUE TYPE IS_DEFAUL IS_SESSION_MODIFIABLE IS_SYSTEM_MODIFIABLE DESCRIPTION
--------------------------------- -------------------- ---------- --------- ------------------------- ------------------------- --------------------------------------------------
_gby_hash_aggregation_enabled TRUE 1 TRUE True Immediate enable group-by and aggregation using hash scheme
Notes
- The
X$tables are only accessible toSYS(or a user granted access to them). - Undocumented parameters can change or disappear between Oracle versions.