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