Oracle Scratchpad

March 8, 2011

Valid Values

Filed under: Oracle — Jonathan Lewis @ 6:52 pm GMT 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 11.1.0.6.1, 11.1.0.6,
10.2.0.4, 10.2.0.3, 10.2.0.2, 10.2.0.1, 10.1.0.6, 10.1.0.5, 10.1.0.4,
10.1.0.3, 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 11.1.0.6 instance):


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

   ORDINAL ISDEFAUL VALUE
---------- -------- ---------------------------
         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    10.1.0.3
        18 FALSE    10.1.0.4
        19 FALSE    10.1.0.5
        20 FALSE    10.1.0.6
        21 FALSE    10.2.0.1
        22 FALSE    10.2.0.2
        23 FALSE    10.2.0.3
        24 FALSE    10.2.0.4
        25 TRUE     11.1.0.6
        26 FALSE    11.1.0.6.1

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:

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

select 
	name_kspvld_values,
	decode(
		isdefault_kspvld_values,
			'TRUE',  '***',
			'FALSE', null,
			         '?'
	)	isdefault_kspvld_values,
	value_kspvld_values
from
	x$kspvld_values
order by
	name_kspvld_values,
	ordinal_kspvld_values
;

spool off


Name                                     Def  Value
---------------------------------------- ---- ----------------------------------------
_always_anti_join                             HASH
                                              MERGE
                                              NESTED_LOOPS
                                              CHOOSE
                                              OFF

_always_semi_join                             HASH
                                              MERGE
                                              NESTED_LOOPS
                                              CHOOSE
                                              OFF

_backup_kgc_scheme                       ***  ZLIB
                                              BZIP2
                                              LZO

_cluster_library                              SKGXN
                                              CLSS

...

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 11.1.0.6 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.

4 Comments »

  1. Typo: “mm” in “v$parammeter_valid_values”

    Comment by Friendly Internet Spellchecker — March 8, 2011 @ 7:43 pm GMT Mar 8,2011 | Reply

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

    http://blog.tanelpoder.com/2008/08/13/script-display-valid-values-for-multioption-parameters-including-hidden-parameters/

    Comment by coskan — March 9, 2011 @ 7:28 am GMT 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 GMT Mar 9,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Blog at WordPress.com.