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