Introduction to X$ Fixed Tables - Secrets of the Oracle Database - page 101

SQL> SELECT
f1.name x_name,
f2.name v_name
FROM x_v_assoc a, v$fixed_table f1, v$fixed_table f2
WHERE a.x_id=f1.object_id
AND a.v_id=f2.object_id
ORDER BY x_name;

In Oracle10 g Release 2, 727 rows are returned by this query. To get a list of X$ fixed tables
underlying V$ views, run the following query:

SQL> SELECT
f1.name v_name,
f2.name x_name
FROM x_v_assoc a, v$fixed_table f1, v$fixed_table f2
WHERE a.v_id=f1.object_id
AND a.x_id=f2.object_id;

Table 9-5 depicts a tiny subset of the associations returned by the latter query.

Table 9-5. Some V$ Views and Their Underlying X$ Fixed Tables
V$ View Underlying X$ table

V$ARCHIVED_LOG X$KCCAL

V$BH X$BH

V$CONTROLFILE X$KCCCF

V$DATAFILE X$KCCFN

V$DB_PIPES X$KGLOB

V$INSTANCE X$KVIT , X$QUIESCE , X$KSUXSINST

V$LATCH X$KSLLD

V$MYSTAT X$KSUSGIF , X$KSUMYSTA

V$PROCESS X$KSUPR

V$RECOVER_FILE X$KCVFHMRR

V$SEGMENT_STATISTICS X$KTSSO , X$KSOLSFTS

V$SESSION X$KSLED , X$KSUSE

V$SQL X$KGLCURSOR_CHILD

V$SQL_BIND_DATA X$KXSBD

V$SQL_PLAN X$KQLFXPL

By spooling the output of the script x_v_assoc.sql , a text document that contains all the
associations between V$ views and X$ tables may be generated.