SQL> select username,user,segtype,segfile#,segblk#,extents,segrfno# from v$sort_usage;
SEGFILE#代表的是绝对文件号(AFN)，对应x$kcctf表中的TFAFN(temp file absolute file number)。
SQL> select indx,tfnum,tfafn,tfcsz from x$kcctf;
TFCSZ 是创建临时文件的块数
查看临时文件的绝对文件号
select tf.FILE#, xtf.tfafn, tf.NAME
from v$tempfile tf, x$kcctf xtf
where tf.FILE# = xtf.tfnum;
查看内存中正在使用的临时文件
col username for a12
col name for a90
select tf.FILE#,su.USERNAME,su.SEGTYPE, tf.NAME
from v$tempfile tf, x$kcctf xtf,v$sort_usage su
where tf.FILE# = xtf.tfnum and xtf.tfafn = su.SEGFILE#;
正在使用的临时文件是无法删除的
SQL> select name from v$tempfile;
NAME
------------------------------------------------------------------------------------------
+ASM_DATA/orcl/tempfile/temp.277.925652849
+ASM_DATA/orcl/tempfile/temp.268.925556123
SQL> alter database tempfile '+ASM_DATA/orcl/tempfile/temp.268.925556123' drop;
alter database tempfile '+ASM_DATA/orcl/tempfile/temp.268.925556123' drop
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time
而没有正在使用的临时文件是可以删除的
SQL> alter database tempfile '+ASM_DATA/orcl/tempfile/temp.277.925652849' drop;
Database altered.
-----------------------------------------------------------
小知识补充：
数据库真正的v$基础视图用户无法访问
可以通过v_$视图访问真正的v$基础视图
用户可以访问的v$视图实际上是v_$视图的同义词
-----------------------------------------------------------
select do.object_type from dba_objects do where do.object_name in('V$TEMPFILE','V$TEMPSEG_USAGE')
可以看到我们通常访问的v$tempfile其实是个同义词，这里提到了V$TEMPSEG_USAGE
select * from dba_synonyms where synonym_name='V$TEMPSEG_USAGE';
可以看到 V$TEMPSEG_USAGE 是 V_$SORT_USAGE 的同义词，而 V_$SORT_USAGE则是基础视图V$SORT_USAGE的视图，由此可知V$TEMPSEG_USAGE与V$SORT_USAGE是等效的。
附：v$tempfile 定义查看过程
select * from v$fixed_view_definition where view_name='V$TEMPFILE';
select FILE#,
CREATION_CHANGE#,
CREATION_TIME,
TS#,
RFILE#,
STATUS,
ENABLED,
BYTES,
BLOCKS,
CREATE_BYTES,
BLOCK_SIZE,
NAME
from GV$TEMPFILE
where inst_id = USERENV('Instance')
select * from v$fixed_view_definition where view_name='GV$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 bitand(tf.tfsta, 32) <> 32
and fn.fntyp = 7
and fn.fnnam is not null
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