How to Find out / Check / Set Values for Hidden Underscore _ Parameters in Oracle?


Short Note on Hidden/Undocumented Parameters:
Undocumented parameters need to be tested in test database before implementing in production. Also, better to get support from oracle before going to production. Hidden parameters are for internal purpose of the oracle. You have to be very careful before implementing as they are very dangerous. It starts with ‘_’.

How to set / reset the value for Hidden parameter?
  1. Database is running on init file/pfile
Add the parameter in the init.ora file and bounce the database.
  1. Database is running on spfile
The parameter/value which starts with underscore has to be mentioned within Double quote (“).
alter system set "_kdlu_trace_layer"=1073741824 scope=spfile sid='*';
This requires a database bounce to take the parameter value in to effect.
To reset the parameter value you can use the following command.
alter system reset "_kdlu_trace_layer” scope=spfile sid='*';

How to find out the value for the hidden parameters?
  1. Already value is set for the hidden parameter. You can see the parameter value using show parameter command from sqlplus prompt.  
SQL> show parameter _kdlu_trace_layer

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
_kdlu_trace_layer                    integer     1073741824

  1. If set/not set you can use the following sql query to find out the value.
col ParameterName for a30
col sessval for a30
col instval for a30
SELECT pi.ksppinm ParameterName, pcv.ksppstvl SessVal, psv.ksppstvl InstVal
FROM x$ksppi pi, x$ksppcv pcv, x$ksppsv psv
WHERE pi.indx = pcv.indx AND pi.indx = psv.indx AND pi.ksppinm LIKE '_kdlu_trace_layer'

PARAMETERNAME                  SESSVAL                        INSTVAL
------------------------------ ------------------------------ -----------
_kdlu_trace_layer              2147483647                     1073741824



Post a Comment Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google