Hidden Parameters in Oracle. How to Change Hidden Parameter

The hidden parameters start with an "_".They can not be viewed from the output of show parameter
or querying v$parameter unless and untill they are set explicitly in init.ora.
However if you want to view all the hidden parameters and their default values the following query
could be of help,


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 '/_%' escape '/'
/


In order to see the listing of all hidden parameters query,

select *
from SYS.X$KSPPI
where substr(KSPPINM,1,1) = '_';

Change Hidden Parameters in Oracle
---------------------------------------
It is never recommended to modify these hidden parameters without the assistance of Oracle Support.Changing these parameters may lead to high performance degradation and other problems in the database.
In order to change hidden parameter,
1)If you use pfile then in your initSID.ora you can entry of the hidden parameter and start the database.

2)If you want to use for the current session you can use ALTER SESSION SET ....

3)To set it permanently if you use spfile then use, ALTER SYSTEM SET ...... SCOPE=SPFILE. Since hidden parameter starts with underscore(_) to access it you have to specify within double quotes. If you use SCOPE=SPFILE then in order to take effect you need to restart database. You can use SCOPE=BOTH if parameter can be set in the session also.