How to resolve the text behind v$views?

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