#環境:
os:centos 6.6 x64
db version:11.2.0.4.0

#建測試表
[oracle@ct6605 ~]$ ORACLE_SID=ct66
[oracle@ct6605 ~]$ sqlplus / as sysdba
SQL> create table scott.tb_block
as
select 'selectshen' a,123 b from dual
union all select 'shengjie' a,234 b from dual;

#檢視測試表的資料,rowid,object_id,relative_fno,block_number,row_number
SQL> select t.*,
       rowid,
       dbms_rowid.rowid_object(rowid) object_id,
       dbms_rowid.rowid_relative_fno(rowid) relative_fno,
       dbms_rowid.rowid_block_number(rowid) block_number,
       dbms_rowid.rowid_row_number(rowid) row_number
from scott.tb_block t;
/*
A           B   ROWID               OBJECT_ID   RELATIVE_FNO   BLOCK_NUMBER   ROW_NUMBER
selectshen   123   AAAVxaAAEAAABVDAAA   89178       4               5443           0
shengjie   234   AAAVxaAAEAAABVDAAB   89178       4               5443           1
*/

#檢視測試表的資料,rowid,dump值
SQL> select t.*,
       rowid,
       dump(rowid),
       dump(rowid,16)
from scott.tb_block t;
/*
A           B   ROWID               DUMP(ROWID)                               DUMP(ROWID,16)
selectshen   123   AAAVxaAAEAAABVDAAA   Typ=69 Len=10: 0,1,92,90,1,0,21,67,0,0   Typ=69 Len=10: 0,1,5c,5a,1,0,15,43,0,0
shengjie   234   AAAVxaAAEAAABVDAAB   Typ=69 Len=10: 0,1,92,90,1,0,21,67,0,1   Typ=69 Len=10: 0,1,5c,5a,1,0,15,43,0,1
*/

#轉儲資料塊
SQL> alter system dump datafile 4 block 5443;

#檢視資料塊的轉儲內容
[oracle@ct6605 ~]$ cd /u01/app/oracle/diag/rdbms/ct66/ct66/trace
[oracle@ct6605 trace]$ vi ct66_ora_6238.trc
#以下是轉儲出來資料塊的內容
Start dump data blocks tsn: 4 file#:4 minblk 5443 maxblk 5443
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16782659
BH (0x72fe04a0) file#: 4 rdba: 0x01001543 (4/5443) class: 1 ba: 0x72d10000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 89178 objn: 89178 tsn: 4 afn: 4 hint: f
hash: [0x76fe2778,0x8a241350] lru: [0x73bd8328,0x77bed4c0]
dbwrid: 0 obj: 89178 objn: 89178 tsn: 4 afn: 4 hint: f
hash: [0x76fe2778,0x8a241350] lru: [0x73bd8328,0x77bed4c0]
ckptq: [NULL] fileq: [NULL] objq: [0x773daa50,0x86aaa498] objaq: [0x773e1a80,0x86aaa488]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk:
buffer tsn: 4 rdba: 0x01001543 (4/5443)
scn: 0x0000.001ce6b1 seq: 0x02 flg: 0x04 tail: 0xe6b10602
frmt: 0x02 chkval: 0x2079 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F38E710FA00 to 0x00007F38E7111A00
7F38E710FA00 0000A206 01001543 001CE6B1 04020000 [....C...........]
7F38E710FA10 00002079 00000001 00015C5A 001CE6AF [y ......Z\......]
7F38E710FA20 00000000 00320003 01001540 0000FFFF [......2.@.......]
7F38E710FA30 00000000 00000000 00000000 00008000 [................]
7F38E710FA40 001CE6AF 00000000 00000000 00000000 [................]
7F38E710FA50 00000000 00000000 00000000 00000000 [................]
        Repeat 1 times
7F38E710FA70 00000000 00000000 00000000 00020100 [................]
7F38E710FA80 0016FFFF 1F481F5E 00001F48 1F6E0002 [....^.H.H.....n.]
7F38E710FA90 00001F5E 00000000 00000000 00000000 [^...............]
7F38E710FAA0 00000000 00000000 00000000 00000000 [................]
        Repeat 498 times
7F38E71119D0 00000000 00000000 002C0000 68730802 [..........,...sh]
7F38E71119E0 6A676E65 C2036569 002C2303 65730A02 [engjie...#,...se]
7F38E71119F0 7463656C 6E656873 1802C203 E6B10602 [lectshen........]

Block header dump: 0x01001543
Object id on Block? Y
seg/obj: 0x15c5a csc: 0x00.1ce6af itc: 3 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x1001540 ver: 0x01 opc: 0
     inc: 0 exflg: 0

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0xffff.000.00000000 0x00000000.0000.00 C---    0 scn 0x0000.001ce6af
0x02   0x0000.000.00000000 0x00000000.0000.00 ----    0 fsc 0x0000.00000000
0x03   0x0000.000.00000000 0x00000000.0000.00 ----    0 fsc 0x0000.00000000
bdba: 0x01001543
data_block_dump,data header at 0x7f38e710fa7c
===============
tsiz: 0x1f80
hsiz: 0x16
pbl: 0x7f38e710fa7c
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f5e
avsp=0x1f48
tosp=0x1f48
0xe:pti[0]      nrow=2 offs=0
0x12:pri[0]     offs=0x1f6e
0x14:pri[1]     offs=0x1f5e
block_row_dump:
tab 0, row 0, @0x1f6e
tl: 18 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 73 65 6c 65 63 74 73 68 65 6e
col 1: [ 3] c2 02 18
tab 0, row 1, @0x1f5e
tl: 16 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 8] 73 68 65 6e 67 6a 69 65
col 1: [ 3] c2 03 23
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 5443 maxblk 5443

#解釋:
Start dump data blocks tsn: 4 file#:4 minblk 5443 maxblk 5443
dump表空間4,資料檔案號4,資料塊號5443

#資料塊頭
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16782659
BH (0x72fe04a0) file#: 4 rdba: 0x01001543 (4/5443) class: 1 ba: 0x72d10000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25
dbwrid: 0 obj: 89178 objn: 89178 tsn: 4 afn: 4 hint: f
hash: [0x76fe2778,0x8a241350] lru: [0x73bd8328,0x77bed4c0]
dbwrid: 0 obj: 89178 objn: 89178 tsn: 4 afn: 4 hint: f
hash: [0x76fe2778,0x8a241350] lru: [0x73bd8328,0x77bed4c0]
ckptq: [NULL] fileq: [NULL] objq: [0x773daa50,0x86aaa498] objaq: [0x773e1a80,0x86aaa488]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
Block dump from disk:
buffer tsn: 4 rdba: 0x01001543 (4/5443)
scn: 0x0000.001ce6b1 seq: 0x02 flg: 0x04 tail: 0xe6b10602
frmt: 0x02 chkval: 0x2079 type: 0x06=trans data
tsn:4是表空間號
rdba:0x01001543是資料檔案號塊號,它是rowid的第7到第15個64進位制字元AAEAAABVD的16進位制形式,在dump(rowid,16)中的第5位到第8位中也可以看出,可參考:http://blog.itpub.net/28539951/viewspace-1986647/
scn: 0x0000.001ce6b1是資料塊頭的scn,16進位制,前4位佔2個位元組,是scn wrap,後8位佔4個位元組,是scp base.
seq: 0x02是順序號,incremented for every change made to the block at the same SCN
flg: 0x04
   0x01 New block
   0x02 Delayed Logging Change advanced SCN/seq
   0x04 Check value/saved - block XOR‘s to zero
   0x08 Temporary block
tail: 0xe6b10602是用於校驗資料塊的一致性,存放在最據塊的最後4個位元組,tail=SCN Base的低2個位元組+type+seq=e6b1+06+02
frmt: 0x02 是塊格式
   0x01:oracle 7
   0x02:oracle 8+
chkval: 0x2079是塊的檢查值,用於對比檢查資料塊是否為壞塊
type: 0x06=trans data
   0x01 Undo segment header
   0x02 Undo data block
   0x03 Save undo header
   0x04 Save undo data block
   0x05 Data segment header (temp, index, data and so on)
   0x06 KTB managed data block (with ITL)
   0x07 Temp table data block (no ITL)
   0x08 Sort Key
   0x09 Sort Run
   0x10 Segment free list block
   0x11 Data file header


#資料區
Dump of memory from 0x00007F38E710FA00 to 0x00007F38E7111A00
7F38E710FA00 0000A206 01001543 001CE6B1 04020000 [....C...........]
7F38E710FA10 00002079 00000001 00015C5A 001CE6AF [y ......Z\......]
7F38E710FA20 00000000 00320003 01001540 0000FFFF [......2.@.......]
7F38E710FA30 00000000 00000000 00000000 00008000 [................]
7F38E710FA40 001CE6AF 00000000 00000000 00000000 [................]
7F38E710FA50 00000000 00000000 00000000 00000000 [................]
        Repeat 1 times
7F38E710FA70 00000000 00000000 00000000 00020100 [................]
7F38E710FA80 0016FFFF 1F481F5E 00001F48 1F6E0002 [....^.H.H.....n.]
7F38E710FA90 00001F5E 00000000 00000000 00000000 [^...............]
7F38E710FAA0 00000000 00000000 00000000 00000000 [................]
        Repeat 498 times
7F38E71119D0 00000000 00000000 002C0000 68730802 [..........,...sh]
7F38E71119E0 6A676E65 C2036569 002C2303 65730A02 [engjie...#,...se]
7F38E71119F0 7463656C 6E656873 1802C203 E6B10602 [lectshen........]

#事務區
Block header dump: 0x01001543
Object id on Block? Y
seg/obj: 0x15c5a csc: 0x00.1ce6af itc: 3 flg: E typ: 1 - DATA
     brn: 0 bdba: 0x1001540 ver: 0x01 opc: 0
     inc: 0 exflg: 0

Itl           Xid                  Uba         Flag Lck        Scn/Fsc
0x01   0xffff.000.00000000 0x00000000.0000.00 C---    0 scn 0x0000.001ce6af
0x02   0x0000.000.00000000 0x00000000.0000.00 ----    0 fsc 0x0000.00000000
0x03   0x0000.000.00000000 0x00000000.0000.00 ----    0 fsc 0x0000.00000000
bdba: 0x01001543
Object id on Block? Y是表示是否是物件的塊
0x01001543是資料檔案號塊號
seg/obj: 0x15c5a是物件id 89178,它是rowid的前6個64進位制字元AAAVxa的16進位制形式,在dump(rowid,16)中的前4位中也可以看出,可參考:http://blog.itpub.net/28539951/viewspace-1986647/
csc: 0x00.1ce6af是The cleanout SCN that is used during read consistency
itc: 3是塊中itl slot的數量
flg: E
   E是使用ASSM
   O是使用free list
typ: 1 - DATA 1是資料,2是索引
brn: 0
bdba: 0x1001540是Block relative data block address
ver: 0x01
opc: 0
inc: 0
exflg: 0
Itl是塊上相關事務列表interested transaction list
Xid是事務id.Xid=Undo Segment Number XIDUSN+Transaction Table Slot Number XIDSLOT+ Wrap
Uba是該事務對應的回滾段地址.Uba=回滾塊地址(undo檔案號UBAFIL和資料塊號UBABLK)+回滾序列號UBASQN+回滾記錄號UBAREC
Flag是事務標誌位
   C = transaction has been committed and locks cleaned out
   B = this undo record contains the undo for this ITL entry
   U = transaction committed (maybe long ago); SCN is an upper bound
   T = transaction was still active at block cleanout SCN
Lck是這個事務影響的行數
Scn/Fsc是scn或者free space credit

#尾區
data_block_dump,data header at 0x7f38e710fa7c
===============
tsiz: 0x1f80
hsiz: 0x16
pbl: 0x7f38e710fa7c
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f5e
avsp=0x1f48
tosp=0x1f48
0xe:pti[0]      nrow=2 offs=0
0x12:pri[0]     offs=0x1f6e
0x14:pri[1]     offs=0x1f5e
tsiz: 0x1f80是資料區的大小Total Data Area Size
hsiz: 0x16是資料塊頭大小Data Header Size
pbl: 0x7f38e710fa7c是資料塊地址
     76543210
flag=--------
ntab=1是The number of table index entries contained in this block(>1 is a cluster)
nrow=2是The number of row index entries in this block
frre=-1是first free row index entry, -1=you have to add one(沒有建立索引)
fsbo=0x16是空閒空間起始位置free space begin offset
fseo=0x1f5e是空閒空間結束位置free space end offset
avsp=0x1f48是可用空間available space for new entries
tosp=0x1f48是total available space when all txs commit
0xe:pti[0]      nrow=2 offs=0是Table directory (The offset indicates where the row directory starts;in this case immediately, the offset is “0.”)塊中資料記錄數
0x12:pri[0]     offs=0x1f6e是Row index (Offset is where the row header for the row begins.)第1條記錄在偏移量為0x1f6e的地方
0x14:pri[1]     offs=0x1f5e是Row index 第2條記錄在偏移量為0x1f5e的地方

block_row_dump:
tab 0, row 0, @0x1f6e
tl: 18 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [10] 73 65 6c 65 63 74 73 68 65 6e
col 1: [ 3] c2 02 18
tab 0, row 1, @0x1f5e
tl: 16 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 8] 73 68 65 6e 67 6a 69 65
col 1: [ 3] c2 03 23
end_of_block_dump
tab 0, row 0, @0x1f6e是第1條記錄在偏移量
tl: 18是Row Size(number of bytes plus data)
fb: --H-FL--是Flag Byte
   K- Cluster key
    H- head of row piece
    D- Deleted row
    F- first data piece
    L- last data piece
    P- First column cintinues from previous row
   N- Last column cintinues in next piece
lb: 0x0是事物在該資料行上的鎖是否清除,0x0是已清除,其它值未清除並對關itl號
col 0: [10] 73 65 6c 65 63 74 73 68 65 6e是列號:[長度] 和值'selectshen'的dump
col 1: [ 3] c2 02 18是列號:[長度] 和值123的dump,可參考http://blog.itpub.net/28539951/viewspace-1986367/

參考文件:

oracle資料塊轉儲說明

相關文章