A simple interview question

Question: How to check instance parameter values in Oracle?

Answer: show parameter xyz

WRONG!

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

WRONG!

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

VALUE
---------------------------------------------------------------------------------
20

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

VALUE
--------------------------------------------------------------------------------
0

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

VALUE
------------------------------------------------------------------------------------
WEB, HR, OE, MFG, ADMIN

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

VALUE
------------------------------------------------------------------------------------
WEB
HR
OE
MFG
ADMIN

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