Print Friendly, PDF & Email

 
QUESTIONS

How to query the hidden initialization parameters?
How to view list of hidden parameters?
How to view hidden parameters in Oracle?
How to find oracle hidden parameters?
List all hidden database parameters.
View hidden initialization parameters.
See all initialization parameters.

 
REQUEST

A user wants to know a value of specific hidden parameter in Oracle database.

 
SOLUTION

The following query retrieves all hidden parameters that are in Oracle database.

COL "PARAMETER" FOR A50 
COL "IS_DEFAULT" FOR A10
COL "SESSION" FOR A10
COL "INSTANCE" FOR A10
COL "IS_SYSTEM_MODIFIABLE" FOR A20

SELECT a.KSPPINM "PARAMETER", 
       b.KSPPSTDF "IS_DEFAULT",
       b.KSPPSTVL "SESSION", 
       c.KSPPSTVL "INSTANCE",
       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 '/'
/

To get a specific hidden parameter


COL "PARAMETER" FOR A30 
COL "IS_DEFAULT" FOR A10
COL "SESSION" FOR A10
COL "INSTANCE" FOR A10
COL "IS_SYSTEM_MODIFIABLE" FOR A20

SELECT a.KSPPINM "PARAMETER", 
       b.KSPPSTDF "IS_DEFAULT",
       b.KSPPSTVL "SESSION", 
       c.KSPPSTVL "INSTANCE",
       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 '/&hidden_parameter' escape '/'
/

Enter value for hidden_parameter: _gsm_region_list
old  12: AND    a.KSPPINM LIKE '/&hidden_parameter' escape '/'
new  12: AND    a.KSPPINM LIKE '/_gsm_region_list' escape '/'

PARAMETER                      IS_DEFAULT SESSION    INSTANCE   IS_SESSION_MODI IS_SYSTEM_MODIFIABLE
------------------------------ ---------- ---------- ---------- --------------- --------------------
_gsm_region_list               TRUE                             FALSE           IMMEDIATE

To change a hidden parameter

ALTER SYSTEM SET "_your_parameter_name"='desired_value' scope=spfile;
ALTER SESSION SET "_your_parameter_name"='desired_value';

For more details refer to How To Query And Change The Oracle Hidden Parameters In Oracle 10g ,11g and 12c (Doc ID 315631.1)

 
 

Version  : 18:20 18.12.2017
Platform : Red Hat 5.11
Database : Oracle database 12.2.0.1.0