Print Friendly, PDF & Email
A query to view all the hidden parameter and their default values.
QUERY

The following query could be of help to view all the hidden parameter and their default values.

SET LINES 999
SET PAGES 300
COL "PARAMETER" FOR A40 
COL "IS_DEFAULT" FOR A10
COL "SESSION" FOR A10
COL "INSTANCE" FOR A10
COL "IS_SESSION_MODIFIABLE" FOR A21
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 NVL('%&hidden_parameter%','')
/
EXAMPLES

To view a specific parameter by its name, for instance, the _hcs_disable_vector_transform

Enter value for hidden_parameter: _hcs_disable_vector_transform

old  13: AND    a.KSPPINM LIKE NVL('%&hidden_parameter%','')
new  13: AND    a.KSPPINM LIKE NVL('%_hcs_disable_vector_transform%','')

PARAMETER                      IS_DEFAULT SESSION    INSTANCE   IS_SE IS_SYSTEM_MODIFIABLE
------------------------------ ---------- ---------- ---------- ----- --------------------
_hcs_disable_vector_transform  TRUE       FALSE      FALSE      TRUE  IMMEDIATE

To list all the hidden parameters just hit Enter

SQL> /
Enter value for hidden_parameter:

PARAMETER                      IS_DEFAULT SESSION    INSTANCE   IS_SE IS_SYSTEM_MODIFIABLE
------------------------------ ---------- ---------- ---------- ----- --------------------
_autotask_min_window           TRUE       15         15         FALSE IMMEDIATE

...

_hcs_enable_mem_trck           TRUE       FALSE      FALSE      TRUE  IMMEDIATE
_hcs_disable_view_merge        TRUE       FALSE      FALSE      TRUE  IMMEDIATE
_reg_cache_status              TRUE       FALSE      FALSE      FALSE FALSE

4956 rows selected.

To view all hidden parameters having the specific word in a name, for instance, the script.

SQL> /
Enter value for hidden_parameter: script

old  13: AND    a.KSPPINM LIKE NVL('%&hidden_parameter%','')
new  13: AND    a.KSPPINM LIKE NVL('%script%','')

PARAMETER                      IS_DEFAULT SESSION    INSTANCE   IS_SE IS_SYSTEM_MODIFIABLE
------------------------------ ---------- ---------- ---------- ----- --------------------
_cdc_subscription_owner        TRUE                             TRUE  FALSE
_oracle_script                 TRUE       FALSE      FALSE      TRUE  FALSE
_pdb_first_script              TRUE       FALSE      FALSE      TRUE  FALSE
_federation_script             TRUE       FALSE      FALSE      TRUE  FALSE
_application_script            TRUE       FALSE      FALSE      TRUE  FALSE
_gwm_autoons_ha_subscription   TRUE                             FALSE IMMEDIATE
_gwm_autoons_rlb_subscription  TRUE                             FALSE IMMEDIATE

7 rows selected.
REFERENCES

How To Query And Change The Oracle Hidden Parameters In Oracle 10g and Later (Doc ID 315631.1)

Written At
07 NOV 202119:40
OEL Release
7.9 x64
Database Release
19.12.0.0.0