tempfile真正文件号

1.发现问题
这里看到文件号为201，但是查询了v$datafile和v$tempfile视图都没有文件号为201

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

USERN SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS   SEGRFNO#
----- --------- ---------- ---------- ---------- ----------
SYS   SORT             201     260745        650          1

SEGFILE#  NUMBER  File number of initial extent 

SEGRFNO#   NUMBER  Relative file number of initial extent 

SQL> SELECT FILE#,RFILE# FROM V$DATAFILE;

     FILE#     RFILE#
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10
        11         11

     FILE#     RFILE#
---------- ----------
        13         13
        14         14

13 rows selected.

SQL> SELECT FILE#,RFILE# FROM V$TEMPFILE;

     FILE#     RFILE#
---------- ----------
         1          1

2.查看v$sort_usage的原始表

SELECT USERNAME,
       USERNAME,
       KTSSOSES,
       KTSSOSNO,
       PREV_SQL_ADDR,
       PREV_HASH_VALUE,
       PREV_SQL_ID,
       KTSSOTSN,
       DECODE(KTSSOCNT, 0, 'PERMANENT', 1, 'TEMPORARY'),
       DECODE(KTSSOSEGT,
              1,
              'SORT',
              2,
              'HASH',
              3,
              'DATA',
              4,
              'INDEX',
              5,
              'LOB_DATA',
              6,
              'LOB_INDEX',
              'UNDEFINED'),
       KTSSOFNO,
       KTSSOBNO,
       KTSSOEXTS,
       KTSSOBLKS,
       KTSSORFNO
  FROM X$KTSSO, V$SESSION
 WHERE KTSSOSES = V$SESSION.SADDR
   AND KTSSOSNO = V$SESSION.SERIAL#
   and inst_id = USERENV('Instance')

这里没有发现有用信息，只是知道X$KTSSO.KTSSOFNO是v$sort_usage.SEGFILE#，通过v$sort_usage视图是查询临时表空用来排序的数据文件使用情况。所以把问题定位在v$tempfile视图中，检查它为什么没有显示文件号为201的文件

3.查看v$tempfile视图

SELECT 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 BITAND(TF.TFSTA, 32) <> 32
   AND FN.FNTYP = 7
   AND FN.FNNAM IS NOT NULL
   and inst_id = USERENV('Instance')

从这里可以看出v$tempfile.file#出自X$KCCTF.TFNUM

4.继续查看X$KCCTF表

SQL> desc X$KCCTF
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 ADDR                                               RAW(8)
 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
 TFMSZ                                              NUMBER
 TFNSZ                                              NUMBER

这里发现一列TFAFN，初步怀疑这个才是真正的绝对文件号。

5.证明X$KCCTF.TFAFN是绝对文件号

SQL> select TFAFN,TFNUM from X$KCCTF;

     TFAFN      TFNUM
---------- ----------
       201          1

如果证明X$KCCTF.TFAFN才是真正的文件号，而TFNUM是临时文件的文件号

6.temp file绝对文件号结论
再进一步可以知道，实际上，为了分离临时文件号和数据文件号，Oracle对临时文件的编号以db_files为起点，所以临时文件的绝对文件号应该等于db_files+file#。

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
db_files                             integer     200