临时文件相关的v$tempfile v$sort_usage与V$tempseg_usage

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