Wednesday, May 12, 2010

How to get list of hidden parameters in ORACLE?

Hidden parameters should be used only and only under Oracle Support control. Otherwise you can be left with your corrupted database. In the near future I will post real world example what it caused using it without Oracle Support's confirmation.

However, I was interested in how many and which hidden parameters available in Oracle. Below is script:

SELECT
a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
a.ksppdesc description
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 '/'

In Oracle 10.2.0.4 on HP UX box, it returned 1236(!) parameters. It is 2057 in 11gR2 in Linux box.

5 comments:

Chinar said...

Hi,Teymur.Hidden parameters is interesting for me.I used some hidden parameters such as _allow_resetlogs_corruption ,_corrupted_rollback_segments and _offline_rollback_segments().In my practice i had need this.But I want know that other examples about using hidden parameters.

Teymur Hajiyev said...

Hi, Chinar.

Glad to see you here.

As you know hidden parameter should be implemented only direction by Oracle Support. It is "hidden" and what id does is also "hidden" :) Hidden for public, but not for Oracle Support Services.

If you need to implement other hidden parameter, the only way is to investigate it on Test Database and see what it does.

I am going to publish real world example what it caused using hidden parameter in production 24x7 database without Oracle Support Services' confirmation and how I solved it without help of Oracle Support :)

See you.

Chinar said...

Hi.Thanks,Of course,i know that,also i do not want impelemt hidden parameter on production database. :-)

Baskar said...

hi,

Just a add on..in 11.1.0.6 the query returned 1631 rows selected.

thanks,
baskar.l

Teymur Hajiyev said...

Hi, Baskar.

Thanks for the info.