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