How To View Hidden Parameters In Oracle?

Oracle initialization parameters which starts with an underscore (_) are called as hidden parameters. These are undocumented parameters.
We cannot find them in V$PARAMETER or by using SHOW PARAMETER command.
Manipulating these parameters will alter Oracle’s internal mechanisms. It is advised NOT to modify these parameters without the consent of Oracle Support.
 
 
Query to view the hidden parameters in Oracle  :
 
set linesize 200
col “Parameter” for a30
col “Session Value” for a10
col “Instance Value” for a10
col “Default value” for a5
col SESSMOD for a7
col SYSMOD for a9
col “Description” for a45
 
select a.ksppinm  “Parameter”, b.ksppstvl “Session Value”, c.ksppstvl “Instance Value” ,b.ksppstdf “Default value” ,decode(bitand(a.ksppiflg/256,3),1, ‘True’, ‘False’) SESSMOD,decode(bitand(a.ksppiflg/65536,3),1,’IMMEDIATE’,2,’DEFERRED’,3,’IMMEDIATE’,’FALSE’) SYSMOD,a.ksppdesc “Description” from sys.x$ksppi a, sys.x$ksppcv b , sys.x$ksppsv c where a.indx = b.indx and a.indx = c.indx and substr(ksppinm,1,1)=’_’  order by a.ksppinm;
 
Note:
In the above query, SESSMOD refers to session modifiable and SYSMOD refers to system modifiable.
 
If SYSMOD is Immediate , then the parameter can be changed with ALTER SYSTEM without restarting the instance.
 
If SYSMOD is DEFERRED , then the parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used in the instance startup.
 
If SYSMOD is FALSE , then the parameter cannot be changed with ALTER SYSTEM unless the database runs on a spfile.

Article by : Anju