How to check Hidden parameters in Database

The hidden parameters in oracle database start with “_”. It is not possible to see the hidden parameters by command “show parameter” or querying v$parameter unless the hidden parameter set explicitly set in init.ora file.

Following query can be use to see hidden parameters:

SELECT x.ksppinm "Parameter",

Y.ksppstvl "Session Value",

Z.ksppstvl "Instance Value"

FROM x$ksppi X,

x$ksppcv Y,

x$ksppsv Z

WHERE x.indx = Y.indx

AND x.indx = z.indx

AND x.ksppinm LIKE '/_%' escape '/'

order by x.ksppinm;

Warning: Note that this is not recommended to change hidden parameter without consent of Oracle Support since Oracle can make your system unsupported and you will be responsible for data corruption, high performance degradation or some other problem.