This is a common problem I have and I never write it down (except now). For example, today I wanted to know what the valid parameters for _serial_direct_read were:
SQL> select * from v$parameter_valid_values where name ='_serial_direct_read'; no rows selected
OK so if Oracle doesn’t tell me then maybe I can work it out? Getting the view_text has worked in the past:
SQL> select view_name, text_vc from dba_views where view_name = '%PARAMETER_VALID_VALUES'
VIEW_NAME TEXT_VC
---------------------------------- ----------------------------------------------------------------------------------------------------
V_$PARAMETER_VALID_VALUES select "NUM","NAME","ORDINAL","VALUE","ISDEFAULT","CON_ID"
from v$parameter_valid_values
GV_$PARAMETER_VALID_VALUES select "INST_ID","NUM","NAME","ORDINAL","VALUE","ISDEFAULT","CON_ID"
from gv$parameter_valid_values
I’m sure I did the step wrong, but I couldn’t find what the lower case thingie was.
SQL> desc "v$parameter_valid_values" ERROR: ORA-04043: object "v$parameter_valid_values" does not exist SQL> sho user USER is "SYS" SQL> desc "gv$parameter_valid_values" ERROR: ORA-04043: object "gv$parameter_valid_values" does not exist SQL> select * from dba_views where view_name = '"gv$parameter_valid_values"'; no rows selected SQL> select * from dba_objects where object_name = '"gv$parameter_valid_values"'; no rows selected
Yes, I’m pretty sure I got something wrong along the way.
Solutions
One possibility is to use dbms_xplan.display_cursor() – easy!
SQL> select * from v$parameter_valid_values where name = '_serial_direct_read';
no rows selected
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID 9hkygnf02nd8y, child number 0
-------------------------------------
select * from v$parameter_valid_values where name =
'_serial_direct_read'
Plan hash value: 1012408093
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | FIXED TABLE FULL| X$KSPVLD_VALUES | 1 | 49 | 0 (0)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("NAME_KSPVLD_VALUES"='_serial_direct_read' AND
TRANSLATE("NAME_KSPVLD_VALUES",'_','#') NOT LIKE '#%' AND
"INST_ID"=USERENV('INSTANCE')))
Now if I translate this I can write a query that shows me what I need. It also demonstrates that – just like v$parameter – underscore parameters aren’t shown in this view.
SQL> @desc X$KSPVLD_VALUES
Name Null? Type
------------------------------- -------- ----------------------------
1 ADDR RAW(8)
2 INDX NUMBER
3 INST_ID NUMBER
4 CON_ID NUMBER
5 PARNO_KSPVLD_VALUES NUMBER
6 NAME_KSPVLD_VALUES VARCHAR2(64)
7 ORDINAL_KSPVLD_VALUES NUMBER
8 VALUE_KSPVLD_VALUES VARCHAR2(255)
9 ISDEFAULT_KSPVLD_VALUES VARCHAR2(64)
SQL> select PARNO_KSPVLD_VALUES, NAME_KSPVLD_VALUES, ORDINAL_KSPVLD_VALUES, VALUE_KSPVLD_VALUES
2 from X$KSPVLD_VALUES where NAME_KSPVLD_VALUES ='_serial_direct_read';
PARNO_KSPVLD_VALUES NAME_KSPVLD_VALUES ORDINAL_KSPVLD_VALUES VALUE_KSPVLD_VALUES
------------------- ------------------------------ --------------------- ------------------------------
2873 _serial_direct_read 1 ALWAYS
2873 _serial_direct_read 2 AUTO
2873 _serial_direct_read 3 NEVER
2873 _serial_direct_read 4 TRUE
2873 _serial_direct_read 5 FALSE
There you go!
Another way is to use the 12c functionality in DBMS_UTILITY.EXPAND_SQL_TEXT. Reusing the example by Tom Kyte:
SQL> var x clob.
SQL> exec dbms_utility.expand_sql_text( -
2 input_sql_text => 'select * from V$PARAMETER_VALID_VALUES', -
3 output_sql_text => :x)
print :x
X
--------------------------------------------------------------------------------------------------------
SELECT "A1"."NUM" "NUM","A1"."NAME" "NAME","A1"."ORDINAL" "ORDINAL","A1"."VALUE" "VALUE",
"A1"."ISDEFAULT" "ISDEFAULT","A1"."CON_ID" "CON_ID" FROM (SELECT "A2"."NUM" "NUM","A2"."NAME"
"NAME","A2"."ORDINAL" "ORDINAL","A2"."VALUE" "VALUE","A2"."ISDEFAULT" "ISDEFAULT","A2"."CON_ID" "CON_ID"
FROM (SELECT "A3"."INST_ID" "INST_ID","A3"."PARNO_KSPVLD_VALUES" "NUM","A3"."NAME_KSPVLD_VALUES"
"NAME","A3"."ORDINAL_KSPVLD_VALUES" "ORDINAL","A3"."VALUE_KSPVLD_VALUES"
"VALUE","A3"."ISDEFAULT_KSPVLD_VALUES" "ISDEFAULT","A3"."CON_ID" "CON_ID" FROM SYS."X$KSPVLD_VALUES" "A3"
WHERE TRANSLATE("A3"."NAME_KSPVLD_VALUES",'_','#') NOT LIKE '#%') "A2" WHERE
"A2"."INST_ID"=USERENV('INSTANCE')) "A1"
This seems to have worked in earlier versions too, one example is on Jonathan Lewis’ blog.
Update: the most obvious solution to this was to use v$fixed_view_definition! The view must have dropped at the cold end of my brain’s LRU list. As others have pointed out (thanks everyone for your comments!), this would be the way to query the object:
SQL> select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where view_name like 'GV$PARAMETER_VALID_VALUES'; VIEW_DEFINITION ------------------------------------------------------------------------------------------------------- SELECT INST_ID, PARNO_KSPVLD_VALUES, NAME_KSPVLD_VALUES, ORDINAL_KSPVLD_VALUES, VALUE_KSPVLD_VALUES, ISDEFAULT_KSPVLD_VALUES, CON_ID FROM X$KSPVLD_VALUES WHERE TRANSLATE(NAME_KSPVLD_VALUES,'_','#') NOT LIKE '#%'
Summary
It’s probably not what Oracle intended but DBMS_UTILITY.EXPAND_SQL_TEXT() worked really well. I came across the DBMS_XPLAN.DISPLAY_CURSOR() output by chance when I ran my diagnostic script at the wrong time but it, too, does the job.
Or, I could have used Tanel Poder’s script I didn’t know about until now:
SQL> @pvalid _serial_direct_read
Display valid values for multioption parameters matching "_serial_direct_read"...
PAR# PARAMETER ORD VALUE DEFAULT
------ -------------------------------------------------- ---------- ------------------------------ -------
2873 _serial_direct_read 1 ALWAYS
_serial_direct_read 2 AUTO
_serial_direct_read 3 NEVER
_serial_direct_read 4 TRUE
_serial_direct_read 5 FALSE
Martins Blog 
Hi Martin,
I think this is a surprisingly common problem – how to see what is inside the dynamic GV$ (V$) Views and *how to remember how to*. Like yourself, I had to write a blog for my own use, to remind me how to do it – https://mwidlake.wordpress.com/2009/08/05/looking-inside-v-performance-views/. I had a couple of comments (both on the blog and direct) from people saying they kept forgetting where to look. I don;t use your methods (nice though they are) but v$fixed_view_objects. I’ve not verified it on 12c yet though.
See – glad I posted!
select VIEW_DEFINITION from V$FIXED_VIEW_DEFINITION where view_name like ‘GV$PARAMETER_VALID_VALUES’;
would have worked too :)
Fun, pieces come back from the good-ol-days when Anjo Kolk gave still internal courses at Oracle. Starting points from those days/lessons which were hidden in my mind would be: v$fixed_table | v$fixed_view_definition
Hi,
The view V$FIXED_VIEW_DEFINITION give s only the first 4000 character for the view definition; for views having definition on more than 4000 characters I have used ‘ dbms_sql2.expand_sql_text’ (introduced on 11.0.2.3)
Thanks,
Cherif
Nice Query, i have enhanced it a little bit: All possible values for each parameter where shown in one line:
SELECT NAME_KSPVLD_VALUES AS parameter,
listagg(VALUE_KSPVLD_VALUES
||'(‘
||ORDINAL_KSPVLD_VALUES
||’)’,’,’) within GROUP (
ORDER BY PARNO_KSPVLD_VALUES) AS Possible_values
FROM X$KSPVLD_VALUES
GROUP BY NAME_KSPVLD_VALUES;
Kind regards
Marco Patzwahl