Coskan’s Approach to Oracle

February 6, 2007

How to view list of hidden parameters

Filed under: How To — coskan @ 1:22 pm

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,
(a.ksppity, 1,
‘boolean’, 2,
‘string’, 3,
‘number’, 4,
‘file’, a.ksppity) type,
a.ksppdesc description
sys.x$ksppi a,
sys.x$ksppcv b
a.indx = b.indx
a.ksppinm like ‘\_%’ escape ‘\’
order by

Footnote 27/07/2009: I personally start to use Tanel Poder’s script to search both hidden and normal parameters


  1. […] 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

  2. 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 instance.

    Otherwise thanks for sharing the info with us.


    Comment by Jeboss — June 1, 2007 @ 8:03 am

  3. I didn’t try the script on before, I tried it on and it worked both on these version if I have chance to use I will try it thanks for the feedback Jeboss

    Comment by coskan — June 1, 2007 @ 8:08 am

  4. Hi!

    How does one change the hidden parameters?


    Comment by Michael — July 2, 2007 @ 8:40 am

  5. 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

  6. Thanks a lot!

    Comment by Michael — July 2, 2007 @ 9:30 am

  7. Hi again!

    We are upgrading our database from to
    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

  8. 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

  9. 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,
    (a.ksppity, 1,
    ‘boolean’, 2,
    ‘string’, 3,
    ‘number’, 4,
    ‘file’, a.ksppity) type,
    a.ksppdesc description
    sys.x$ksppi a,
    sys.x$ksppcv b
    a.indx = b.indx
    a.ksppinm like ‘\_%’ escape ‘\’
    order by

    Comment by Ramnik — October 19, 2007 @ 4:04 pm

  10. How is it possible to know to default value for a modified parameter ?

    Comment by poleta33 — November 30, 2007 @ 10:26 am

  11. Hi,

    I just created an Oracle 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

  12. 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

  13. The script does indeed need the table reference “X$KSPPCV” changing to “X$KSPPSV” to make it work on on AIX.

    Otherwise great.


    Comment by Jeff Moss — October 8, 2008 @ 4:45 pm

  14. The script works with too. Thanks.


    Comment by ittichai — December 14, 2008 @ 6:43 am

  15. […] 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

  16. […] 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

  17. 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

  18. […] 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

  24. Hello, yup this piece of writing is in fact pleasant
    and I have learned lot of things from it concerning blogging.

    Comment by — April 5, 2014 @ 6:32 am

  25. 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

  26. 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

  27. 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

  28. Appreciate it for helping out, great information. ecdkfgeagcee

    Comment by Johnk869 — May 1, 2014 @ 9:36 pm

  29. 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

  30. 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

  31. 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

  32. 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

  33. 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

  34. 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

  35. 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

  36. 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

  37. script works with 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

  38. 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

  39. 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

  40. 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

  41. 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

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: