Script: Display valid values for multioption parameters (including hidden parameters)

I wrote a little script pvalid.sql for listing valid values for multioption parameters (the ones which are not string, number or boolean type, but accept a parameter from predetermined list, like optimizer_mode which can have values of ALL_ROWS, FIRST_ROWS, CHOOSE, FIRST_ROWS_1, etc).

The script accepts a (part of) Oracle parameter name as first argument, for example the following output is from Oracle 10.2.0.3 database:

SQL> @pvalid lock

  PAR# PARAMETER                                          VALUE                          DEFAULT
------ -------------------------------------------------- ------------------------------ -------
   374 _db_block_cache_protect                            FALSE                          DEFAULT
       _db_block_cache_protect                            LOW
       _db_block_cache_protect                            MEDIUM
       _db_block_cache_protect                            TRUE

   376 db_block_checksum                                  TRUE                           DEFAULT
       db_block_checksum                                  FALSE
       db_block_checksum                                  FULL
       db_block_checksum                                  OFF
       db_block_checksum                                  TYPICAL

   696 _row_locking                                       ALWAYS
       _row_locking                                       ALWAYS
       _row_locking                                       DEFAULT
       _row_locking                                       DEFAULT
       _row_locking                                       INTENT
       _row_locking                                       INTENT

   756 db_block_checking                                  FALSE                          DEFAULT
       db_block_checking                                  FULL
       db_block_checking                                  LOW
       db_block_checking                                  MEDIUM
       db_block_checking                                  OFF
       db_block_checking                                  TRUE

   851 _plsql_anon_block_code_type                        INTERPRETED                    DEFAULT
       _plsql_anon_block_code_type                        NATIVE


23 rows selected.

SQL>

Or:

SQL> @pvalid optimizer

  PAR# PARAMETER                                          VALUE                          DEFAULT
------ -------------------------------------------------- ------------------------------ -------
   873 _optimizer_trace                                   ALL
       _optimizer_trace                                   ENVIRONMENT
       _optimizer_trace                                   HIGH
       _optimizer_trace                                   HINT
       _optimizer_trace                                   LOGICAL
       _optimizer_trace                                   LOW
       _optimizer_trace                                   MEDIUM
       _optimizer_trace                                   NONE
       _optimizer_trace                                   PHYSICAL

   936 optimizer_features_enable                          10.2.0.3                       DEFAULT
       optimizer_features_enable                          10.1.0
       optimizer_features_enable                          10.1.0.3
       optimizer_features_enable                          10.1.0.4
       optimizer_features_enable                          10.1.0.5
       optimizer_features_enable                          10.2.0.1
       optimizer_features_enable                          10.2.0.2
       optimizer_features_enable                          10.2.0.3.1
       optimizer_features_enable                          8.0.0
       optimizer_features_enable                          8.0.3
       optimizer_features_enable                          8.0.4
       optimizer_features_enable                          8.0.5
       optimizer_features_enable                          8.0.6
       optimizer_features_enable                          8.0.7
       optimizer_features_enable                          8.1.0
       optimizer_features_enable                          8.1.3
       optimizer_features_enable                          8.1.4
       optimizer_features_enable                          8.1.5
       optimizer_features_enable                          8.1.6
       optimizer_features_enable                          8.1.7
       optimizer_features_enable                          9.0.0
       optimizer_features_enable                          9.0.1
       optimizer_features_enable                          9.2.0
       optimizer_features_enable                          9.2.0.8

   952 optimizer_mode                                     ALL_ROWS
       optimizer_mode                                     CHOOSE
       optimizer_mode                                     FIRST_ROWS
       optimizer_mode                                     FIRST_ROWS_1
       optimizer_mode                                     FIRST_ROWS_10
       optimizer_mode                                     FIRST_ROWS_100
       optimizer_mode                                     FIRST_ROWS_1000
       optimizer_mode                                     RULE

   967 _optimizer_extended_cursor_sharing                 NONE
       _optimizer_extended_cursor_sharing                 UDO

   968 _optimizer_cost_model                              CHOOSE
       _optimizer_cost_model                              CPU
       _optimizer_cost_model                              FIXED
       _optimizer_cost_model                              IO

   983 _optimizer_cost_based_transformation               EXHAUSTIVE
       _optimizer_cost_based_transformation               ITERATIVE
       _optimizer_cost_based_transformation               LINEAR
       _optimizer_cost_based_transformation               OFF
       _optimizer_cost_based_transformation               ON

  1259 _optimizer_better_inlist_costing                   ALL
       _optimizer_better_inlist_costing                   NLONLY
       _optimizer_better_inlist_costing                   OFF

  1263 _optimizer_or_expansion                            BREADTH
       _optimizer_or_expansion                            DEPTH

  1279 _optimizer_native_full_outer_join                  CHOOSE
       _optimizer_native_full_outer_join                  FORCE
       _optimizer_native_full_outer_join                  OFF


60 rows selected.

Note that this script is using the X$KSPVLD_VALUES fixed table, as the V$PARAMETER_VALID_VALUES view doesn’t show this info for hidden parameters. Thereforre you normally need to be logged on as SYS for using this script (or create a proxy view for that X$ table).

Also, this script works only from Oracle 10.2 onwards.

In previous versions you can run ALTER SESSION SET command with a bogus parameter to find out the valid values. This is an example from 9.2.0.8:

SQL> alter session set optimizer_mode = something_ridiculous;
ERROR:
ORA-00096: invalid value SOMETHING_RIDICULOUS for parameter optimizer_mode, must be from among first_rows_1000,
first_rows_100, first_rows_10, first_rows_1, first_rows, all_rows, choose, rule

Let’s try another one:

SQL> alter session set optimizer_features_enable = something_ridiculous;
alter session set optimizer_features_enable = something_ridiculous
                  *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Some parameters can’t be modified dynamically neither at session nor system level as this query also shows:

SQL> select isses_modifiable, issys_modifiable from v$parameter where name = 'optimizer_features_enable';

ISSES ISSYS_MOD
----- ---------
FALSE FALSE

But I’m still allowed to run this ALTER command using scope=spfile option:

SQL> alter system set optimizer_features_enable = something_ridiculous scope=spfile;
alter system set optimizer_features_enable = something_ridiculous scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value SOMETHING_RIDICULOUS for parameter optimizer_features_enable, must be from among 9.2.0.1, 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

Note that in some earlier version (can’t rememer which) this check was not done properly and the invalid value was passed to spfile. Thus the next startup failed. So if you need to use this approach, don’t run it in production, at least if you need to resort to ALTER SYSTEM instead of ALTER SESSION (and better not run any untested stuff from internet in your production db anyway ;-)

Anyway, the pvalid.sql should make life a little bit easier :)