QUERYING ORACLE HIDDEN PARAMETERS

QUESTIONS
How to query the hidden initialization parameters?
How to view list of hidden parameters?
How to view hidden parameters in Oracle?
How to find oracle hidden parameters?
List all hidden database parameters.
View hidden initialization parameters.
See all initialization parameters.

REQUEST
You as an Oracle database administrator want to know the value of the specific hidden parameter in the Oracle database.

SOLUTION
The following query returns all hidden parameters existing in the Oracle database.

SET LINES 999
SET PAGES 300
COL "PARAMETER" FOR A50 
COL "IS_DEFAULT" FOR A10
COL "SESSION" FOR A10
COL "INSTANCE" FOR A10
COL "IS_SYSTEM_MODIFIABLE" FOR A20

SELECT a.KSPPINM "PARAMETER", 
       b.KSPPSTDF "IS_DEFAULT",
       b.KSPPSTVL "SESSION", 
       c.KSPPSTVL "INSTANCE",
       DECODE(BITAND(a.KSPPIFLG/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE, 
       DECODE(BITAND(a.KSPPIFLG/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM   X$KSPPI  a,
       X$KSPPCV b,
       X$KSPPSV c
WHERE  a.INDX = b.INDX
AND    a.INDX = c.INDX
AND    a.KSPPINM LIKE '/_%' escape '/'
/

The next query returns the specific hidden parameter, for instance the _gsm_region_list

SET LINES 999
SET PAGES 300
COL "PARAMETER" FOR A30 
COL "IS_DEFAULT" FOR A10
COL "SESSION" FOR A10
COL "INSTANCE" FOR A10
COL "IS_SYSTEM_MODIFIABLE" FOR A20

SELECT a.KSPPINM "PARAMETER", 
       b.KSPPSTDF "IS_DEFAULT",
       b.KSPPSTVL "SESSION", 
       c.KSPPSTVL "INSTANCE",
       DECODE(BITAND(a.KSPPIFLG/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE, 
       DECODE(BITAND(a.KSPPIFLG/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM   X$KSPPI  a,
       X$KSPPCV b,
       X$KSPPSV c
WHERE  a.INDX = b.INDX
AND    a.INDX = c.INDX
AND    a.KSPPINM LIKE '/&hidden_parameter' escape '/'
/

Enter value for hidden_parameter: _gsm_region_list
old  12: AND    a.KSPPINM LIKE '/&hidden_parameter' escape '/'
new  12: AND    a.KSPPINM LIKE '/_gsm_region_list' escape '/'

PARAMETER                      IS_DEFAULT SESSION    INSTANCE   IS_SESSION_MODI IS_SYSTEM_MODIFIABLE
------------------------------ ---------- ---------- ---------- --------------- --------------------
_gsm_region_list               TRUE                             FALSE           IMMEDIATE

Another query that let you return hidden parameter(s) by a mask name, for instance, the queue_interval

SET LINES 999
SET PAGES 300
COL "PARAMETER" FOR A30 
COL "IS_DEFAULT" FOR A10
COL "SESSION" FOR A10
COL "INSTANCE" FOR A10
COL "IS_SYSTEM_MODIFIABLE" FOR A20

SELECT a.KSPPINM "PARAMETER", 
       b.KSPPSTDF "IS_DEFAULT",
       b.KSPPSTVL "SESSION", 
       c.KSPPSTVL "INSTANCE",
       DECODE(BITAND(a.KSPPIFLG/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE, 
       DECODE(BITAND(a.KSPPIFLG/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM   X$KSPPI  a,
       X$KSPPCV b,
       X$KSPPSV c
WHERE  a.INDX = b.INDX
AND    a.INDX = c.INDX
AND    a.KSPPINM LIKE '/_%' escape '/'
AND    a.KSPPINM LIKE '%&mask_name%'
/

Enter value for mask_name: queue_interval
old  13: AND    a.KSPPINM LIKE '%&mask_name%'
new  13: AND    a.KSPPINM LIKE '%queue_interval%'

PARAMETER                      IS_DEFAULT SESSION    INSTANCE   IS_SE IS_SYSTEM_MODIFIABLE
------------------------------ ---------- ---------- ---------- ----- --------------------
_job_queue_interval            TRUE       5          5          FALSE FALSE

 
To change a hidden parameter

-- The system level
ALTER SYSTEM SET "_your_parameter_name"='desired_value' scope=spfile;

-- A session level
ALTER SESSION SET "_your_parameter_name"='desired_value';

REFERENCES
How To Query And Change The Oracle Hidden Parameters In Oracle 10g ,11g and 12c (Doc ID 315631.1)