LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon