Hidden or Underscore Parameter in Oracle 19c

What is Hidden Parameter in Oracle?

Hidden parameters in Oracle Database, often referred to as “Underscore parameters,” are settings that do not appear in standard queries like SHOW PARAMETER or V$PARAMETER unless they have been explicitly set.

These parameters usually begin with an underscore (“_”) and are also known as “Undocumented Parameters” because Oracle does not officially document them.

How to Query a hidden Parameter?

col Session_Value for a20
col Instance_Value for a20
col Parameter for a50
col Default_Value for a20
set lin 200 pages 200

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 a hidden parameter in Oracle Database differs from setting a normal parameter. When setting a hidden parameter, you must enclose the parameter name in double quotes. Failure to do so will result in an error. This distinction ensures that hidden parameters are intentionally set and prevents accidental modification or usage.

Syntax –

alter system set "<name of the parameter>"= <values> scope=spfile sid='*';

How to Check all hidden parameter Explicitly Set in the Database?

If your manager has asked you to provide a list of all the hidden parameters set explicitly in the database, you can use the following SQL query:

select name, value from v$parameter where name like '/_%' escape '/' AND isdefault = 'FALSE' ORDER BY name;

You can follow another way as well –

1. create Pfile from spfile;

SQL> create pfile='/home/oracle/initmydb.ora' from spfile;

2. Copy the content of PFILE and keep only parameters which start with “_”

cat /home/oracle/initmydb.ora | grep "*._"

Reference : MOS (Doc ID 315631.1)

Hope it helped !! 🙂