v$tempfile与v$sort_usage之关系解析

    在前面的V$TEMPSEG_USAGE与排序中我谈到V$TEMPSEG_USAGE和V$SORT_USAGE同源,其中的SEGFILE#代表的是绝对文件号(AFN).

那么对于临时表空间的临时文件来说,这个字段可以和什么字段进行关联呢?

我们再来看一下V$TEMPFILE的来源,V$TEMPFILE由如下语句创建:

SELECT tf.inst_id, tf.tfnum, TO_NUMBER (tf.tfcrc_scn),
       TO_DATE (tf.tfcrc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
       tf.tftsn, tf.tfrfn,
       DECODE (BITAND (tf.tfsta, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),
       DECODE (BITAND (tf.tfsta, 12),
               0, 'DISABLED',
               4, 'READ ONLY',
               12, 'READ WRITE',
               'UNKNOWN'
              ),
       fh.fhtmpfsz * tf.tfbsz, fh.fhtmpfsz, tf.tfcsz * tf.tfbsz, tf.tfbsz,
       fn.fnnam
  FROM x$kcctf tf, x$kccfn fn, x$kcvfhtmp fh
 WHERE fn.fnfno = tf.tfnum
   AND fn.fnfno = fh.htmpxfil
   AND tf.tffnh = fn.fnnum
   AND tf.tfdup != 0
   AND fn.fntyp = 7
   AND fn.fnnam IS NOT NULL

考察x$kcctf底层表,我们注意到TFAFN(temp file absolute file number)在这里存在:

SQL> desc x$kcctf
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ADDR                                   RAW(4)
 INDX                                   NUMBER
 INST_ID                                NUMBER
 TFNUM                                  NUMBER
 TFAFN                                  NUMBER
 TFCSZ                                  NUMBER
 TFBSZ                                  NUMBER
 TFSTA                                  NUMBER
 TFCRC_SCN                              VARCHAR2(16)
 TFCRC_TIM                              VARCHAR2(20)
 TFFNH                                  NUMBER
 TFFNT                                  NUMBER
 TFDUP                                  NUMBER
 TFTSN                                  NUMBER
 TFTSI                                  NUMBER
 TFRFN                                  NUMBER
 TFPFT                                  NUMBER

而这个字段在构建v$tempfile时并未出现,所以我们不能通过v$sort_usage和v$tempfile直接关联绝对文件号.

通过LOB对象与临时段一文中方法我们可以简单构建一个排序段使用，然后来研究一下: 

SQL> select username,segtype,segfile#,segblk#,extents,segrfno#
  2  from v$sort_usage;

USERNAME SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS   SEGRFNO#
-------- --------- ---------- ---------- ---------- ----------
SYS      LOB_DATA           9      18953          1          1

我们看到这里的SEGFILE#=9，而在v$tempfile是找不到这个信息的:

SQL> select file#,rfile#,ts#,status,blocks
  2  from v$tempfile;

     FILE#     RFILE#        TS# STATUS      BLOCKS
---------- ---------- ---------- ------- ----------
         1          1          2 ONLINE       38400

我们可以从x$kcctf中获得这些信息,我们可以看到v$tempfile.file#实际上来自x$kcctf.tfnum，实际上是临时文件的顺序号，而绝对文件号是x$kcctf.tfafn，这个才可以和v$sort_usage.segfile#关联： 

SQL> select indx,tfnum,tfafn,tfcsz       
  2  from x$kcctf;

      INDX      TFNUM      TFAFN      TFCSZ
---------- ---------- ---------- ----------
         0          1          9      38400
         1          2         10      12800

临时表空间的绝对文件号可以通过如下查询获得:

 

SQL> select tm.file# Fnum ,tf.tfafn AFN,tm.name FName
  2  from v$tempfile tm,x$kcctf tf
  3  where tm.file# = tf.tfnum;

      FNUM        AFN FNAME
---------- ---------- --------------------------------------------
         1          9 /opt/oracle/oradata/conner/temp1.dbf
         4         12 /opt/oracle/oradata/conner/temp2.dbf

至于其他就不再赘述。

原文地址：

【责编:Peng】

--------------------next---------------------

SQL> desc x$kcctf
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ADDR                                   RAW(4)
 INDX                                   NUMBER
 INST_ID                                NUMBER
 TFNUM                                  NUMBER
 TFAFN                                  NUMBER
 TFCSZ                                  NUMBER
 TFBSZ                                  NUMBER
 TFSTA                                  NUMBER
 TFCRC_SCN                              VARCHAR2(16)
 TFCRC_TIM                              VARCHAR2(20)
 TFFNH                                  NUMBER
 TFFNT                                  NUMBER
 TFDUP                                  NUMBER
 TFTSN                                  NUMBER
 TFTSI                                  NUMBER
 TFRFN                                  NUMBER
 TFPFT                                  NUMBER

--------------------next---------------------

SQL> select indx,tfnum,tfafn,tfcsz       
  2  from x$kcctf;

      INDX      TFNUM      TFAFN      TFCSZ
---------- ---------- ---------- ----------
         0          1          9      38400
         1          2         10      12800

--------------------next---------------------

SQL> select tm.file# Fnum ,tf.tfafn AFN,tm.name FName
  2  from v$tempfile tm,x$kcctf tf
  3  where tm.file# = tf.tfnum;

      FNUM        AFN FNAME
---------- ---------- --------------------------------------------
         1          9 /opt/oracle/oradata/conner/temp1.dbf
         4         12 /opt/oracle/oradata/conner/temp2.dbf

--------------------next---------------------