internals

Fixed Table Definitions, A New Learning….

Posted

Whenever I used to find out the info of any fixed table(x$) I used to do this by a workaround. I used to set the trace, run the query and see the table’s name. For example, if we are looking for the V$log’s fixed table name so I would do something like that ,

SQL> select * from  V$log;

Execution Plan

———————————————————-

Plan hash value: 2536105608

——————————————————————————–

————

| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)

| Time     |

——————————————————————————–

————

|   0 | SELECT STATEMENT         |                 |     1 |   185 |     0   (0)

| 00:00:01 |

|   1 |  NESTED LOOPS            |                 |     1 |   185 |     0   (0)

| 00:00:01 |

|*  2 |   FIXED TABLE FULL       | X$KCCLE         |     1 |   136 |     0   (0)

| 00:00:01 |

|*  3 |   FIXED TABLE FIXED INDEX| X$KCCRT (ind:1) |     1 |    49 |     0   (0)

| 00:00:01 |

——————————————————————————–

————

(output trimmed)
So I got that x$kccle and x$kccrt are driving it. Well not a bad way IMO.
Till today….
When I found this note,
So there is actually a view which tells us the description of the fixed tables, cool! So doing the same from here,
SQL> select view_definition
2  from v$fixed_view_definition where view_name=’V$LOG’;
VIEW_DEFINITION
——————————————————————————–
select   GROUP# , THREAD# , SEQUENCE# , BYTES , MEMBERS , ARCHIVED , STATUS , FI
RST_CHANGE# , FIRST_TIME from GV$LOG where inst_id = USERENV(’Instance’)
SQL> select view_definition
2  from v$fixed_view_definition where view_name=’GV$LOG’;
VIEW_DEFINITION
——————————————————————————–
select le.inst_id, le.lenum, le.lethr, le.leseq, le.lesiz*le.lebsz, ledup, decod
e(bitand(le.leflg,1),0,’NO’,’YES’), decode(bitand(le.leflg,24), 8, ‘CURRENT’,
16,’CLEARING’,                            24,’CLEARING_
CURRENT’,        decode(sign(leseq),0,’UNUSED’,        decode(sign((to_number(rt
.rtckp_scn)-to_number(le.lenxs))*        bitand(rt.rtsta,2)),-1,’ACTIVE’,’INACTI
VE’))), to_number(le.lelos), to_date(le.lelot,’MM/DD/RR HH24:MI:SS’,’NLS_CALENDA
R=Gregorian’) from x$kccle le, x$kccrt rt where le.ledup!=0 and le.lethr=rt.rtnu
m and  le.inst_id = rt.inst_id
SQL>
Voila! We got the fixed table’s names and much more info about how they are being used. Cool! Its always better to take a straight way rather than a workaround. Learned some thing new today :-) .