在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字段