A simple interview question

Question: How to check instance parameter values in Oracle?

Answer: show parameter xyz


Answer: select value from v$parameter where name = ‘xyz’


These commands show the session level parameter values, which are separate from instance level parameters:

SQL> show parameter session_cached_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
session_cached_cursors               integer     20

SQL> select value from v$parameter where name = 'session_cached_cursors';


V$SYSTEM_PARAMETER is the view which shows instance level parameters (and these are what all new sessions inherit)

SQL> select value from v$system_parameter where name = 'session_cached_cursors';


In this case the difference was due a logon trigger whcih did set session_cached_cursors based on v$session program name.

Note that there is another not widely known gotcha with parameters – multivalue parameters:

There are few parameters which can accept and hold multiple values at a time. For example, utl_file_dir and service_names.

From the following it seems that in my instance there’s a single parameter with multiple comma separated values:

SQL> select value from v$system_parameter where name = 'service_names';


Actually these values are treated as separate parameter values (of the same parameter name) in V$PARAMETER2 and V$SYSTEM_PARAMETER2:

SQL> select value from v$system_parameter2 where name = 'service_names';


So, in addition to well known V$PARAMETER, there’s V$PARAMETER2, V$SYSTEM_PARAMETER and V$SYSTEM_PARAMETER2 – and only V$SYSTEM_PARAMETER(2) show you the instance parameters.

And there’s more – V$SPPARAMETER, V$OBSOLETE_PARAMETER and V$PARAMETER_VALID_VALUES (introduced in 10.2) which all can be handy for diagnosing problems and for grilling people at interviews (*evil grin*).

Unlike all my other marvellous posts, this one doesn’t actually have a point – but hey it’s Friday evening in Singapore (or Saturday in Australia) already, so I wish you a happy, parameter-free weekend ;-)

NB! If you want to move to the "New World" - offload your data and workloads to Hadoop, without having to re-write your existing applications - check out Gluent. We are making history! ;-)

This entry was posted in Oracle and tagged . Bookmark the permalink.

2 Responses to A simple interview question

  1. TedC says:

    This is exactly the reason why so many developers and others find Oracle so annoying. Why have multiple layers to get to an answer and why obfuscate something so simple. “Hey Oracle” call it what it is…. Excellent post.

  2. Amir Hameed says:

    Hi Tanel,
    I have noticed that if I log into a running Oracle instance and run “show parameter db_cache_size”, Oracle only displays the non-doubles undercore value. But if I stopped the instance, start it again and by staying in the same session, I issue the same command, Oracle will show the double underscore values:

    SQL> conn / as sysdba
    SQL> show parameter db_cache_size

    ———————————— ———– ——————————
    db_cache_size big integer 1G

    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    SQL> startup
    ORACLE instance started.

    Total System Global Area 1.0737E+10 bytes
    Fixed Size 2046784 bytes
    Variable Size 7314867392 bytes
    Database Buffers 3405774848 bytes
    Redo Buffers 14729216 bytes
    Database mounted.
    Database opened.
    SQL> show parameter db_cache_size

    ———————————— ———– ——————————
    __db_cache_size big integer 3248M
    db_cache_size big integer 1G

    Somehow, the session that brings up an Oracle instance pulls these values from regareless of whether SPFILE is used or not (I have tested both scenarios).

Leave a Reply

Your email address will not be published. Required fields are marked *