v$datafile.file#与v$tempfile.file#区别

在oracle文档中,file#是被定义为绝对文件号(the absolute file number)

查询dba_objects视图,发现v$tempseg_usage视图是一个同义词

SQL> select object_type from dba_objects where object_name='V$TEMPSEG_USAGE';

OBJECT_TYPE
-------------------
SYNONYM

v$tempseg_usage是v_$sort_usage的同义词,也就是和v$sort_usage同源.

select * from dba_synonyms where synonym_name='V$TEMPSEG_USAGE';
SQL> select * from dba_synonyms where synonym_name='V$TEMPSEG_USAGE';

OWNER    SYNONYM_NAME     TABLE_OWNER  TABLE_NAME     DB_LINK
-------- ---------------- ------------ -------------- ----------
PUBLIC   V$TEMPSEG_USAGE  SYS          V_$SORT_USAGE

查看这个视图的构造语句;

SQL> select view_definition from v$fixed_view_definition where view_name='GV$SORT_USAGE';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select x$ktsso.inst_id,
       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#

注意到在oracle文档中segfile#的定义为:
segfile# number file number of initial extent
在视图中,这个字段来自x$ktsso.ktssofno,也就是说这个字段实际上代表的是绝对文件号,
那么这个绝对文件号能否与v$tempfile中的file#字段关联了

来查看一下v$tempfile的来源,

select view_definition from v$fixed_view_definition where view_name='GV$TEMPFILE';
VIEW_DEFINITION
--------------------------------------------------------------------------------
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

再来查看x$kcctf底层表,注意到TFAFN(Temp File Absolute File Number)是存在的

SQL> desc x$kcctf
Name      Type         Nullable Default Comments
--------- ------------ -------- ------- --------
ADDR      RAW(8)       Y
INDX      NUMBER       Y
INST_ID   NUMBER       Y
TFNUM     NUMBER       Y
TFAFN     NUMBER       Y
TFCSZ     NUMBER       Y
TFBSZ     NUMBER       Y
TFSTA     NUMBER       Y
TFCRC_SCN VARCHAR2(16) Y
TFCRC_TIM VARCHAR2(20) Y
TFFNH     NUMBER       Y
TFFNT     NUMBER       Y
TFDUP     NUMBER       Y
TFTSN     NUMBER       Y
TFTSI     NUMBER       Y
TFRFN     NUMBER       Y
TFPFT     NUMBER       Y
TFMSZ     NUMBER       Y
TFNSZ     NUMBER       Y

而v$kcctf.tfafn这个字段在构造v$tempfile时并没有使用,所以不能通过v$sort_usage
和vg$tempfile直接关联绝对文件号.查询一下排序段使用

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

USERNAME                       SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS   SEGRFNO#
------------------------------ --------- ---------- ---------- ---------- ----------
ZW2003                         DATA             201    2097801          1          1
ZW2001                         DATA             201    2104073          1          1
ZW2001                         DATA             201    2096265          1          1

看到这里的segfile#=201,而在v$tempfile是找不到这个信息的;

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

     FILE#     RFILE#        TS# STATUS      BLOCKS
---------- ---------- ---------- ------- ----------
         1          1          3 ONLINE     3840000

可以从x$kcctf中获得这些信息,并可以看到v$tempfile.file#实际上来自x$kcctf.tfnum,
这个字段是临时文件的文件号,而绝对文件号是v$kcctf.tfafn,只有这个字段才可以与
v$sort_usage.segfile#关联;

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

      INDX      TFNUM      TFAFN      TFCSZ
---------- ---------- ---------- ----------
         0          1        201    1048576

为了分离临时文件号和数据文件号,oracle对临时文件的编号是以db_files为起点,所以临时文件
的绝对文件号是等于db_files+file#

db_files参数的值如下:


SQL> show parameter db_files;

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

所以v$tempfile.file#定义为绝对文件号是不确切的.

数据文件的的文件号

SQL> select a.object_name,a.object_type from dba_objects a where a.object_name='V$DATAFILE';

OBJECT_NAME     OBJECT_TYPE
--------------- -------------------
V$DATAFILE      SYNONYM

从这个查询知道v$datafile是同义词来源于v_$datafile

SQL> select * from dba_synonyms a where a.synonym_name='V$DATAFILE';

OWNER     SYNONYM_NAME  TABLE_OWNER  TABLE_NAME   DB_LINK
--------- ------------- ------------ ------------ -------
PUBLIC    V$DATAFILE    SYS          V_$DATAFILE

v_$datafile的构造语句如下:

SQL> select view_definition from v$fixed_view_definition where view_name='GV$DATAFILE';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select /*+ rule */
 fe.inst_id,
 fe.fenum,
 to_number(fe.fecrc_scn),
 to_date(fe.fecrc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 fe.fetsn,
 fe.ferfn,
 decode(fe.fetsn,
        0,
        decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
        decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER')),
 decode(fe.fedor,
        2,
        'READ ONLY',
        decode(bitand(fe.festa, 12),
               0,
               'DISABLED',
               4,
               'READ ONLY',
               12,
               'READ WRITE',
               'UNKNOWN')),
 to_number(fe.fecps),
 to_date(fe.fecpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 to_number(fe.feurs),
 to_date(fe.feurt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 to_number(fe.fests),
 decode(fe.fests,
        NULL,
        to_date(NULL),
        to_date(fe.festt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')),
 to_number(fe.feofs),
 to_number(fe.feonc_scn),
 to_date(fe.feonc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 fh.fhfsz * fe.febsz,
 fh.fhfsz,
 fe.fecsz * fe.febsz,
 fe.febsz,
 fn.fnnam,
 fe.fefdb,
 fn.fnbof,
 decode(fe.fepax, 0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam),
 to_number(fh.fhfirstunrecscn),
 to_date(fh.fhfirstunrectime,
         'MM/DD/RR HH24:MI:SS',
         'NLS_CALENDAR=Gregorian')
  from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh
 where ((fe.fepax != 65535 and fe.fepax != 0 and fe.fepax = fnaux.fnnum) or
       ((fe.fepax = 65535 or fe.fepax = 0) and fe.fenum = fnaux.fnfno and
       fnaux.fntyp = 4 and fnaux.fnnam is not null and
       bitand(fnaux.fnflg, 4) != 4 and fe.fefnh = fnaux.fnnum))
   and fn.fnfno = fe.fenum
   and fn.fnfno = fh.hxfil
   and fe.fefnh = fn.fnnum
   and fe.fedup != 0
   and fn.fntyp = 4
   and fn.fnnam is not null
   and bitand(fn.fnflg, 4) != 4

从上面的构造语句可知v$datafile.file#来源于x$kccfe.fenum字段

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.