Changing Hidden parameters may lead to serious corruption issues, high performance degradation and other problems in the database.
Oracle Doc
I was upgrading my database from 11g to 19c, and pre-upgrade sql asked me to remove all the hidden parameters before starting actual upgrade of the database. You can refer following document which I used to upgrade the database. How to Manually Upgrade Database (Non-CDB) from 11g to 19c
It’s always better to check with Oracle Support before setting a hidden Parameter
Table of Contents
What is Hidden Parameter in Oracle
Hidden means which is not visible with naked eyes. Similarly in Oracle Database, parameters which are not visible through show parameter or v$parameter until Hidden parameter is set explicitly.
Hidden parameter start with an “_”. Hence it is also called Underscore parameters in Oracle. Since Hidden Parameters are also called Undocumented Parameters. And hence these parameters shouldn’t be used without Oracle Support assistance.
How to Query a hidden Parameter
– SQLs are taken from Oracle Doc – How To Query And Change The Oracle Hidden Parameters In Oracle 10g and Later 315631.1
SELECT a.ksppinm "Parameter", b.KSPPSTDF "Default Value", b.ksppstvl "Session Value", c.ksppstvl "Instance Value", 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 '/'
for finding ISPDB_MODIFIABLE : SELECT a.ksppinm "Parameter", decode(bitand(ksppiflg/524288,1),1,'TRUE','FALSE') ISPDB_MODIFIABLE FROM x$ksppi a WHERE a.ksppinm LIKE '/_clusterwide_global_transactions' escape '/'
How to Set a Hidden Parameter
Setting hidden parameter is different from normal parameter. In hidden parameters, you need to start the name of parameter in double quotes while setting it. Else you will get an error.
Syntax - alter system set "<name of the parameter>"= <values>;
For example – To enable Adaptive Cursor Sharing, there is a underscore parameter to disable/enable the same
alter system set "_optimizer_adaptive_cursor_sharing"=TRUE scope= both;
How to Check all hidden parameter Explicitly Set in the Database
Yesterday, my manager asked me to provide all the hidden parameters set in the database explicitly. I have used following query to list it –
select name, value from v$parameter where name like '/_%' escape '/'
You can follow another way as well –
1 Login to the database and create Pfile from spfile;
SQL> create pfile='/tmp/backup_oracledbworld_09012022.ora' from spfile;
2 Copy the content of PFILE and keep only parameters which start with “_”
cat /tmp/backup_oracledbworld_09012022.ora | grep "*._"