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.

SET LINES 999
SET PAGES 300
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 starting with “_” character, for instance _gsm_region_list

SET LINES 999
SET PAGES 300
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 get a specific hidden parameter by a mask name, for instance queue_interval

SET LINES 999
SET PAGES 300
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 '/_%' escape '/'
AND    a.KSPPINM LIKE '%&mask_name%'
/
Enter value for mask_name: queue_interval
old  13: AND    a.KSPPINM LIKE '%&mask_name%'
new  13: AND    a.KSPPINM LIKE '%queue_interval%'

PARAMETER                      IS_DEFAULT SESSION    INSTANCE   IS_SE IS_SYSTEM_MODIFIABLE
------------------------------ ---------- ---------- ---------- ----- --------------------
_job_queue_interval            TRUE       5          5          FALSE FALSE

 
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)

 
 

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