Hidden parameters sometimes ease the life of DBA but be carefull while using them Oracle usually does not support the usage of hidden parameters without their knowledge and you cant even know the side effects of the parameter.
Here is the query you can use to view the values of hidden parameter. (use it with sys user with sysdba privilages)
/* hidden parameters */
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
‘boolean’, 2,
‘string’, 3,
‘number’, 4,
‘file’, a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like ‘\_%’ escape ‘\’
order by
name
Footnote 27/07/2009: I personally start to use Tanel Poder’s script to search both hidden and normal parameters
Coskan's Approach to Oracle 
[…] you can query the list of undocumented parameters with the script mentioned before “how to view list of hidden parameters“ […]
Pingback by Setting or Resetting /Unsetting Parameters « Coskans Approach to Oracle — March 7, 2007 @ 2:19 pm
HI,
I’ve tested your query and it didn’t worked out of the box. I had to change the view names to those ones;
sys.X_$KSPPI a,
sys.X_$KSPPSV b
I’m running a 10.1.0.5.0 instance.
Otherwise thanks for sharing the info with us.
Regards.
Comment by Jeboss — June 1, 2007 @ 8:03 am
I didn’t try the script on 10.1.0.5 before, I tried it on 10.2.0.1 and 10.1.0.4 it worked both on these version if I have chance to use 10.1.0.5 I will try it thanks for the feedback Jeboss
Comment by coskan — June 1, 2007 @ 8:08 am
Hi!
How does one change the hidden parameters?
Regards
Comment by Michael — July 2, 2007 @ 8:40 am
you can set them by using quotes
for instance;
sys@XE> alter system set “_abort_recovery_on_join”=true;
System altered.
since they are undocumented, you must use them carefully
Comment by coskan — July 2, 2007 @ 9:06 am
Thanks a lot!
Comment by Michael — July 2, 2007 @ 9:30 am
Hi again!
We are upgrading our database from 9.2.0.5 to 10.2.0.2.
We are also using Applications 11i and according to the documentation we should remove a lot of init parameters.
My question is: Should I remove parameters that are not in the pfile such as always_anti_join, enqueue_resources and so on. The Metalink Document 216205.1 also tells us to remove a lot of hidden parameters. How do you remove hidden parameters?
Thanks for your support!
Comment by Michael — July 2, 2007 @ 12:10 pm
there are two ways to do this
1- resetting parameter on the spfile
idle> alter system reset “_abort_recovery_on_join” scope=spfile sid=’*’;
System altered.
scope connot be both it is said it can be but I couldn’t do so scope is for only pfile or spfile
2- if you are using init.ora then when you remove the line of the undocumented parameter it will take its default value. init.ora file has entries only for non-default values if you remove the entry then it will take the default value after the next startup.
in my opinion you must consult to oracle support for this kind of upgrades.
best wishes
Comment by coskan — July 2, 2007 @ 12:38 pm
Only quotes appears to be an issue. This is script working fine on all version of 10g database.
select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
‘boolean’, 2,
‘string’, 3,
‘number’, 4,
‘file’, a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like ‘\_%’ escape ‘\’
order by
name
Comment by Ramnik — October 19, 2007 @ 4:04 pm
How is it possible to know to default value for a modified parameter ?
Comment by poleta33 — November 30, 2007 @ 10:26 am
Hi,
I just created an Oracle 10.2.0.1 datbase on Solaris with a very basic init.ora file. I ran your query to check the current and default values of the hidden parameters (it worked by just changing to single quotes, as an earlier reader noted).
I found that the current value for _like_with_bind_as_equality
is FALSE, although the default value appears as TRUE.
How can this be – since I have not ever changed any hidden
parameter on any database, and no one has access tho this one other than myself ?!
Thank you!
Comment by Chris Emmonds — January 25, 2008 @ 2:05 am
The DEFAULT means if the value is the default or not that column is the indicator only.
When it is TRUE it is the default value which means you did not set it . When it is FALSE it means the parameter modified.
If you add the line below to the where clause it will brings you the parameters which are modified.
and b.ksppstdf=’FALSE’
Comment by coskan — January 28, 2008 @ 12:07 pm
The script does indeed need the table reference “X$KSPPCV” changing to “X$KSPPSV” to make it work on 10.2.0.4 on AIX.
Otherwise great.
Cheers
Jeff
Comment by Jeff Moss — October 8, 2008 @ 4:45 pm
The script works with 11.1.0.6 too. Thanks.
Ittichai
Comment by ittichai — December 14, 2008 @ 6:43 am
[…] In addition to that it allows you peek into the current values of some hidden parameters too. This is a good alternative to having to run a query like below; Sample below is looking for a value of the “_b_tree_bitmap_plans” parameter. I got this code from Coskan’s blog. […]
Pingback by See initialization parameters from memory « Oraexplorer’s Blog — January 14, 2009 @ 3:29 am
[…] In addition to that it allows you peek into the current values of some hidden parameters too. This is a good alternative to having to run a query like below; Sample below is looking for a value of the “_b_tree_bitmap_plans” parameter. I got this code from Coskan’s blog. […]
Pingback by OraExplorer » See initialization parameters from memory — January 21, 2009 @ 4:45 am
Thank you, it is very useful to km=now, but are there a perfect INIT.ORA parameters file setting can be used as an initial trial for high database performance ?
Comment by Montaser Salem — April 8, 2011 @ 7:12 pm
[…] How to view list of hidden parameters « Coskan’s Approach to Oracle Laisser un commentaire J'aimeSoyez le premier à aimer ce post.Laisser un commentaire » […]
Pingback by How to view list of hidden parameters « Coskan’s Approach to Oracle « Oracle jgaicc — April 27, 2011 @ 3:40 pm
simple solution is ” create pfile from memory;” it will show hidden parameters also. very easy to remember and very easy to work with also using vi editior
Comment by rajan — September 17, 2012 @ 10:47 am
I barely ever post commentary, however I read over quite a number of opinions here
at How to view list of hidden parameters | Coskan’s Approach to Oracle and had a couple of basic questions for you if you do not mind. Could it be just me or do some of the above comments seem to be as if they are just plain provided by really unintelligent persons? As well, in case you are blogging on other additional web pages, I would like to stay in touch with you. Would you post a list of all of the social network sites like your linkedin account, Facebook site or twitter feed?
Comment by new business ideas — August 15, 2013 @ 3:46 pm
Attractive section of content. I just stumbled upon your web site and in accession capital to assert that I get
actually enjoyed account your blog posts. Anyway I will be subscribing to your
augment and even I achievement you access consistently fast.
Comment by Karissa Patry — March 3, 2014 @ 8:25 am
It’s genuinely very difficult in this busy life to listen
news oon Television, therefore I just use internet for that purpose, and
otain the newest information.
Comment by Constance — March 8, 2014 @ 1:53 am
Thanks for the auspicious writeup. It in fact was once a
amusement account it. Look complicated to far delivered agreeable from you!
By the way, how could we keep in touch?
Comment by k53 learners — March 17, 2014 @ 8:22 am
Hello, yup this piece of writing is in fact pleasant
and I have learned lot of things from it concerning blogging.
thanks.
Comment by http://technologyexpertblog.wordpress.com — April 5, 2014 @ 6:32 am
Hello, just wanted to tell you, I liked this blog
post. It was helpful. Keep on posting!
Comment by chinchilla dust bath for sale — April 10, 2014 @ 2:57 am
Terrific post however , I was wondering if you
could write a litte more on this topic? I’d be very thankful if you could elaborate a little
bit more. Thanjk you!
Comment by chinchilla treats how often — April 21, 2014 @ 6:57 am
Hello! I know this is somewhat off topic but I was wondering which blog platform are you using for this
website? I’m getting fed up of WordPress because I’ve had problems
with hackers and I’m looking at alternatives for another platform.
I would be fantastic if you could point me in the direction of a good platform.
Comment by html5 wordpress — April 23, 2014 @ 3:09 am
Appreciate it for helping out, great information. ecdkfgeagcee
Comment by Johnk869 — May 1, 2014 @ 9:36 pm
There is no need to pledge any sort of collateral to the loan provider lets
them fill up the loan application form and submit personal loans it on the time to waste money paying for a penalty.
Comment by smslån utan uc — May 11, 2014 @ 8:44 pm
Prior to applying for these loans, it is must to be blessed
with something valuable object in case they are not expected to pay the loan
monthly as signed in the contract. You can make use of quick personal loans it.
If you work in a top notch corporate and have
a huge amount of money will be in time. For non-collateral holders
and tenants. These finances are short term loans.
Comment by Jeana — May 11, 2014 @ 8:44 pm
Even if you are personal unsecured loans working for a company which belongs to the
A class according to the bank, even if you have poor credit status.
By applying online through banks and other financial magazines and books.
This is the only way you are going through now and they have discovered the way out?
Comment by låna pengar med betalningsanmärkningar — May 11, 2014 @ 8:55 pm
In fact, refined vegetable oils, like soy oil,
are used in most of the snack foods, cookies, crackers,
and sweets in the American diet as well as in fast food
items. MSM (Methylsulfonylmethane) is not a medicine, a drug
or a food additive. Hopefully you already eat a
lot of fruit and vegetables in your diet, if not then you should start to add them now.
Comment by causes of inflammation — May 16, 2014 @ 8:22 pm
So verpönt das Daddeln auch sein mag, so ist durch psychologische Tests erwiesen,
dass das Daddeln von Games, ein dem Alter angemessenes Spiel vorausgesetzt, förderlich für
das Reaktionsvermögen des Gamers sein kann.
Beim Spielen lernt der Spielende ohne Stress in höherer Frequenz zu Reagieren und seine Prioritäten zu ändern. Viele Videospiele vermitteln darüber hinaus noch Grundkenntnisse über das Haushalten mit begrenzten Rohstoffen und fördern die kognitiven Fähigkeiten des
Spielenden. Sogar die verpönten Multiplayer Spiele
können doch häufig den falsch vermittelten Effekt der
Vereinsamung} invertieren. Der Gamer mag wohl auch einigen wenigen Freaks begegnen, allerdings findet der Spielende sehr oft genau in seinem Lieblingsspiel Gleichgesinnte.
Alles in allem: das Gaming ist super! Ungeachtet des
Games und ob PC,Konsole,Handy,etc. , Daddeln ist Kultur.
Comment by Online spiele kostenlose — August 3, 2014 @ 9:57 pm
Heya i’m for the first time here. I found this board and I find It really useful & it helped me out much.
I hope to give something back and help others like you aided me.
Comment by printer parts — September 3, 2014 @ 3:26 am
I think the admin of this site is in fact working hard
in support of his site, for the reason that here every material is quality
based information.
Comment by spider man unlimited hack tool — September 17, 2014 @ 6:13 am
In fact when someone doesn’t be aware of after that its up to
other visitors that they will help, sso here it occurs.
Comment by Bettie — October 17, 2014 @ 6:41 am
script works with 12.1.0.2 as well…with a little formatting added.
col name format a50;
col value format a12;
col type format a8;
col description format a70;
col deflt format a6;
select a.ksppinm name, b.ksppstvl value, b.ksppstdf deflt, decode(a.ksppity, 1,
‘boolean’, 2, ‘string’, 3, ‘number’, 4, ‘file’, a.ksppity) type, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like ‘\_%’ escape ‘\’
order by name
/
Comment by Mark McCrory — March 1, 2016 @ 9:45 am
Hi Mark,
If we would like to know whether hidden parameters are static or dynamic, how we will come to know.
Appreciate your inputs
Comment by Rakesh — June 15, 2016 @ 7:55 am
I am regular visitor, how are you everybody? Thiis piece of writing
posted at this weeb page is really nice.
Comment by taxi vtc nice — May 16, 2016 @ 12:41 pm
As the admin of this site is working, no hesitation very soon it will be
well-known, due to its feature contents.
Comment by oto Ekspertiz fiyatı — May 18, 2017 @ 8:04 am
The very core of your writing while appearing reasonable originally, did not really settle well with me personally after some time. Someplace within the sentences you actually were able to make me a believer but just for a very short while. I still have got a problem with your jumps in logic and one might do nicely to help fill in all those gaps. If you actually can accomplish that, I could certainly be amazed.
Comment by Aluminium Disc — June 11, 2018 @ 5:25 am
you’ve got an incredible blog here! would you prefer to make some invite posts on my blog?
Comment by Brennschneidmaschine Hersteller — September 3, 2018 @ 9:24 am
earth Energy
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by earth Energy — May 3, 2020 @ 4:15 am
relationship expert
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by relationship expert — July 8, 2020 @ 11:29 am
energy Transition
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by energy Transition — September 23, 2020 @ 9:36 am
[…] You can query the list of undocumented parameters with the script mentioned before “how to view list of hidden parameters” […]
Pingback by Setting , Resetting/Unsetting Database Parameters – LeoSunny — October 30, 2020 @ 8:28 am
Reading Life
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by Reading Life — December 16, 2020 @ 8:52 am
scr888 test id
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by scr888 test id — May 17, 2021 @ 9:43 pm
leather tote bag
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by leather tote bag — January 23, 2022 @ 4:47 pm
6 inch digital caliper
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by 6 inch digital caliper — January 24, 2022 @ 3:27 am
6 mitutoyo digital caliper
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by 6 mitutoyo digital caliper — January 24, 2022 @ 6:47 am
picasso Tiles
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by picasso Tiles — January 24, 2022 @ 10:28 pm
vasagle scrivania per computer mobile
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by vasagle scrivania per computer mobile — January 24, 2022 @ 11:35 pm
generac pressure washer wand
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by generac pressure washer wand — January 25, 2022 @ 1:15 am
husqvarna chainsaw chain
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by husqvarna chainsaw chain — January 25, 2022 @ 6:03 pm
recensioni videogiochi
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by recensioni videogiochi — January 25, 2022 @ 8:50 pm
4 digital caliper
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by 4 digital caliper — January 26, 2022 @ 5:33 pm
glass tobacco pipes near me
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by glass tobacco pipes near me — January 27, 2022 @ 1:18 pm
Suggested Webpage
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by Suggested Webpage — January 27, 2022 @ 6:33 pm
homebase wallpaper table
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by homebase wallpaper table — January 27, 2022 @ 7:52 pm
best asian romantic comedy movies
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by best asian romantic comedy movies — January 29, 2022 @ 3:18 pm
coach Graduate
How to view list of hidden parameters | Coskan’s Approach to Oracle
Trackback by coach Graduate — September 11, 2022 @ 8:12 am
[…] Parameter There’s no hidden parameter. I tried searching for anything like %null% or %sort%, none of them appear […]
Pingback by Oracle order NULL LAST by default - Row Coding — November 25, 2023 @ 7:55 pm