Oracle parameter possible value acquisition method
- 2020-12-22 17:49:21
- OfStack
Sometimes it is not clear that all allowable values for 1 parameter can be obtained by the following two methods
1. Enter the wrong value directly and let error prompt
All the allowed values of _db_block_cache_protect are TRUE, MEDIUM, LOW, FALSE
The downside is that 9i and previous versions do not support this kind of checking
2. Through scripts
cat a.sql
Execute with sys user
SQL > @a block
All possible values for the parameters associated with block are fetched.
1. Enter the wrong value directly and let error prompt
SQL> alter system set "_db_block_cache_protect"=what scope=spfile;
alter system set "_db_block_cache_protect"=what scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value WHAT for parameter _db_block_cache_protect, must be
from among TRUE, MEDIUM, LOW, FALSE
All the allowed values of _db_block_cache_protect are TRUE, MEDIUM, LOW, FALSE
The downside is that 9i and previous versions do not support this kind of checking
2. Through scripts
cat a.sql
COL pvalid_default HEAD DEFAULT FOR A7
COL pvalid_value HEAD VALUE FOR A30
COL pvalid_name HEAD PARAMETER FOR A50
COL pvalid_par# HEAD PAR# FOR 99999
BREAK ON pvalid_par# skip 1
PROMPT Display valid values for multioption parameters matching "&1"...
SELECT
-- INST_ID,
PARNO_KSPVLD_VALUES pvalid_par#,
NAME_KSPVLD_VALUES pvalid_name,
ORDINAL_KSPVLD_VALUES ORD,
VALUE_KSPVLD_VALUES pvalid_value,
DECODE(ISDEFAULT_KSPVLD_VALUES, 'FALSE', '', 'DEFAULT' ) pvalid_default
FROM
X$KSPVLD_VALUES
WHERE
LOWER(NAME_KSPVLD_VALUES) LIKE LOWER('%&1%')
ORDER BY
pvalid_par#,
pvalid_default,
ord,
pvalid_Value
Execute with sys user
SQL > @a block
All possible values for the parameters associated with block are fetched.