Hidden parameters in Oracle always start with an underscore.
It is not possible for the DBA to see the hidden parameters with the SQL*Plus command “show parameter” or by querying v$parameter. Unless the hidden parameter is explicitly set in spfile/init.ora file.
How Can I list all Hidden Parameters set in The database?
As they are explicitely set in the init file, you can create a report that shows all the hidden parameters using the v$parameter view.
The following sql statemant lists undocumented parameters but can also be used to list documented parameters, that can be set in the spfile or init.ora file:
col name for A45 set lines 120 col value for A40 set pagesize 100 select name, value from v$parameter where name like '\_%' escape '\';
How can I list all hidden parameters available?
If you want to list all hidden parameters available for your version along with a description:
select ksppinm, ksppdesc from x$ksppi where substr(ksppinm,1,1) = '_';
How can I set the value of a hidden parameter?
You can change a hidden parameter, the same way as you would any other init.ora parameters but you need to put double quotes for the parameter name:
alter system set "_pga_max_size"=5G scope=spfile sid='*';
A Good DBA needs to know what hidden parameters are set in the database and their values. Especially during upgrade, database migrations or performance tuning problems.
Oracle has hundreds of initialization parameters, which are hidden and undocumented. Many savvy Oracle professionals are known to commonly adjust the hidden parameters to improve the overall performance of their systems.
Disclaimer: It is not recommended to change hidden parameter without consent of Oracle Support since Oracle can make your system unsupported. You can be responsible for data corruption, performance degradation because of bad SQL plans or other problem. the undocumented init parameters are only used in emergencies or to fix a bug. Some of these parameters are Operating system specific and used in unusual recovery situations. Hence, these parameters should be manipulated carefully and preferably not without recommendation from an Oracle Database Administrator.
Author: Vincent Fenoll Oracle DBA