Friday, September 04, 2015

11:01 AM

Listing and Changing Oracle Hidden Parameters in 11gR1/11gR2

Posted by Jay on March 11, 2011

The hidden parameters start with an “_".They can not be viewed from the output of show parameter or querying v$parameter unless and until they are set explicitly in init.ora. To view all the hidden parameters and their default values the following query can be used.

SELECT

a.ksppinm “Parameter",

b.ksppstvl “Session Value",

c.ksppstvl “Instance Value"

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 ‘/’

order by a.ksppinm desc

Change the paramater using spfile or pfile according to the file being used.

It is never recommended to make the changes unless we are 100% sure and always note down the default value before change in case to revert back and please never try these in production systems at first go.

Inserted from <https://amoratech.wordpress.com/2011/03/11/listing-and-changing-oracle-hidden-parameters-in-11gr111gr2/>