Oracle Scratchpad

March 8, 2011

Valid Values

Filed under: Oracle — Jonathan Lewis @ 6:52 pm BST Mar 8,2011

How do you find out if there are any restrictions (or interesting, possibly undocumented, values) for the system parameters ? Once upon a time I used to use a value that was (probably) illegal so that I could find out from the resulting error message what the list of valid values was, for example:

SQL> alter system set optimizer_features_enable='fred';
alter system set optimizer_features_enable='fred'
ERROR at line 1:
ORA-00096: invalid value fred for parameter
optimizer_features_enable, must be from among,,,,,,,,,, 10.1.0, 9.2.0, 9.0.1, 9.0.0, 8.1.7, 8.1.6, 8.1.5, 8.1.4,
8.1.3, 8.1.0, 8.0.7, 8.0.6, 8.0.5, 8.0.4, 8.0.3, 8.0.0

This little trick became unnecessary in 10.2 with the arrival of view v$parameter_valid_values. For example (from an instance):

SQL> select ordinal, isdefault, value
  2  from v$parameter_valid_values
  3  where name = 'optimizer_features_enable'
  4  order by ordinal
  5  /

---------- -------- ---------------------------
         1 FALSE    8.0.0
         2 FALSE    8.0.3
         3 FALSE    8.0.4
         4 FALSE    8.0.5
         5 FALSE    8.0.6
         6 FALSE    8.0.7
         7 FALSE    8.1.0
         8 FALSE    8.1.3
         9 FALSE    8.1.4
        10 FALSE    8.1.5
        11 FALSE    8.1.6
        12 FALSE    8.1.7
        13 FALSE    9.0.0
        14 FALSE    9.0.1
        15 FALSE    9.2.0
        16 FALSE    10.1.0
        17 FALSE
        18 FALSE
        19 FALSE
        20 FALSE
        21 FALSE
        22 FALSE
        23 FALSE
        24 FALSE
        25 TRUE
        26 FALSE

26 rows selected.

But a quick test demonstrates that you don’t get to see the values for hidden parameters – which is why I wrote a quick script to query the underyling x$ structure. Since it’s looking at an x$ it will only work if you’re connected to the SYS schema:

rem     Script:         valid_values.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Aug 2007

set pagesize 60

set linesize 90
set trimspool on

column	name_kspvld_values	format a40	heading "Name"
column	value_kspvld_values	format a40	heading "Value"
column	isdefault_kspvld_values	format a4	heading "Def"

break on name_kspvld_values skip 1

spool valid_values

			'TRUE',  '***',
			'FALSE', null,
	)	isdefault_kspvld_values,
order by

spool off

Name                                     Def  Value
---------------------------------------- ---- ----------------------------------------
_always_anti_join                             HASH

_always_semi_join                             HASH

_backup_kgc_scheme                       ***  ZLIB

_cluster_library                              SKGXN


You’ll notice when you run it that it’s only about the parameters that take string values – there’s no information there about default values for numeric parameters or boolean parameters. Unfortunately there are (in my instance) 107 distinct parameters listed in this view but 283 parameters of type 2 in x$ksppi (one of the things underneath v$parameter) so at first sight it looks as if some (quite a lot of) parameters are missing – but that’s because some string parameters (such as control_files, remote_listener, log_archive_dest) are open-ended in their content and can’t be constrained by a simple list of values.


  1. Thanks for sharing it made me recall Tanel’s post about the same issue

    Comment by coskan — March 9, 2011 @ 7:28 am BST Mar 9,2011 | Reply

    • Coskan,

      Thanks for that – I ought to remember to check Tanel’s blog before I write up notes like this.

      There’s a very useful warning at the end of his note about getting illegal values into the SPFILE.

      Comment by Jonathan Lewis — March 9, 2011 @ 9:39 am BST Mar 9,2011 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by