Undocumented ( Hidden ) Parameters in Oracle 11g:
Some times ago , one of my junior queried about hidden parameters in Oracle 11g to me and which is the cause because , I am writing this post .I realized that sometimes pretty simple things can be really handy to know. Oracle 11g database has a various nice feature to find out the available Undocumented ( hidden ) parameters present in the spfile.
So when talk about the parameters, there are following 2 types of initialization parameters exist in Oracle11g R2:
1: Documented Initialization Parameter.
2: Undocumented ( Hidden/Underscore ) Initialization Parameter.
We can check documented Initialization Parameter very easily, but the checking of Undocumented Initialization parameter, which is also called hidden Parameter is little bit tricky . These parameters start with a symbol “_” and there is no document available about these parameter.
Every version of Oracle has special undocumented (Hidden) initialization parameters. These undocumented initialization parameters are usually only used in emergencies or with the express consent of Oracle technical support.
Warning:
Here are some points to be noted regarding Undocumented ( hidden ) parameters:
1: Use of undocumented parameters without the knowledge of Oracle Support is a violation of the Support agreement with Oracle. Oracle Worldwide support has every right to refuse to support the databases affected by using these parameters.
2: All these parameters may not be available with all the ports. This means that they are not compatible across the operating systems. A few parameters are Operating System specific.
3: A few parameters can potentially corrupt the databases and some parameters within those few will help us in bringing up the corrupted database with minimal data loss. So these parameters are equally useful inspite of those dangers.
4: Undocumented ( hidden) parameters change from version to version (even in the same versions from patchset to patchset) without any notice. These parameters are not consistent across versions.
5:Since no external documentation is available, it is important to test them before using these parameters in our production system. It is also suggested to have a complete backup before using these parameters.
The X$KSPPI table contains all of the parameters that influence the oracle database in operation.X$<tables> are available only when we connect through the oracle instance as the user ‘SYS’ .
To check all Undocumented ( Hidden) initialization parameter run command given below.
SQL> select count(*) from X$KSPPI;
COUNT(*)
-------------
2399
To check documented parameter run the following command .
SQL> select count(*) from v$parameter;
COUNT(*)
-------------
342 That means Oracle 11g R2 has 2399 parameters, out of which 342 parameters are documented in the V$parameter view. This means that , there are still 2057 parameters which are not listed in either V$views or Oracle Documentation. These are Undocumented ( Hidden ) parameters.
In my next post you will see the different-2 ways to find out these Undocumented (Hidden) parameters loaded in your Oracle 11g database instance (memory).
Thank you for reading… This is Airy…Enjoy 🙂