QUERYING ORACLE HIDDEN PARAMETERS

 
QUESTIONS

 
REQUEST

 
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

-- The system level
ALTER SYSTEM SET "_your_parameter_name"='desired_value' scope=spfile;

-- A session level
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)

 
 
REFERENCES
 
How To Query And Change The Oracle Hidden Parameters In Oracle 10g ,11g and 12c (Doc ID 315631.1)