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,
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

About these ads

32 Comments »

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

    Otherwise thanks for sharing the info with us.

    Regards.

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

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

  4. Hi!

    How does one change the hidden parameters?

    Regards

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

  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,
    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

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

  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 10.2.0.4 on AIX.

    Otherwise great.

    Cheers
    Jeff

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

  14. The script works with 11.1.0.6 too. Thanks.

    Ittichai

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

    Comment by http://technologyexpertblog.wordpress.com — 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers

%d bloggers like this: