Valid Values

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 simple script to query the underlying x$ structure. Since it’s looking at an x$ it will work only if you’re connected to the SYS schema:

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

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
-- where
--      translate(name_kspvld_values,'_','#') not like '#%'
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.