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