在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,

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欄位

v$datafile.file#與v$tempfile.file#區別

相關文章