1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Get Oracle Database Parameters at SQL Prompt

Discussion in 'General' started by sidharthmellam, Feb 13, 2010.

  1. sidharthmellam

    sidharthmellam Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi All,

    1) My Basic Question with big Description

    If I need to know the following parameters of my current database for which am executing the following and getting the outputs at SQL prompt

    show parameter DB_FILE_MULTIBLOCK_READ_COUNT;
    show parameter OPTIMIZER_MODE;
    show parameter OPTIMIZER_INDEX_COST_ADJ;
    show parameter _SORT_ELIMINATION_COST_RATIO;


    My question is , With a Single SQL Command, I should be able to get the values for the all the above sql commands, in an output file.

    in simple words, i need a file which should contain the output for the above sql commnands.

    2) can i get a command to clear the screen content from the SQL prompt

    3)Command to repeat the previous command from the SQL prompt and
    history of the commands executed ?

    Thanks in Advance
    Sidhu
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: How to get the following ?

    1) The query you're searching for is this:

    Code (SQL):
    SELECT rpad(i.ksppinm, 35) || ' = ' || v.ksppstvl parameter
    FROM x$ksppi i,
    x$ksppcv v
    WHERE v.indx = i.indx
    AND v.inst_id = i.inst_id
    AND i.ksppinm IN ('db_file_multiblock_read_count','optimizer_mode','optimizer_index_cost_adj','_sort_elimination_cost_rated')
    ORDER BY i.ksppinm
    /
    2) clear screen

    3) / will execute the command currently in the SQL buffer; there is no 'command history' in SQLPlus although on Windows if DOSKEY is enabled the up and down arrows will scroll through the commands submitted. That may work for some Linux/UNIX shells (bash, ksh, zsh) as well but youll need to verify that on your own system.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: How to get the following ?

    Alternative for the first question , you can try from V$PARAMETER

    Code (SQL):

    SELECT * FROM V$PARAMETER WHERE NAME IN
    ('db_file_multiblock_read_count','optimizer_mode','optimizer_index_cost_adj','_sort_elimination_cost_rated');
     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: How to get the following ?

    Unless the 'hidden' parameter isn't set in the pfile/spfile at which point no data will be returned. Usisng the fixed tables/views returns the values for all valid parameters whether they are the defaults or not.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: How to get the following ?

    Yes.. You are right .