Top 60 Oracle Blogs

Recent comments

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