data block转储文件初识
来源：互联网 发布：通联数据股份公司待遇 编辑：程序博客网 时间：2024/05/16 16:58
使用转储技术，看看事务对数据块的影响：
先找到我们要试验的表记录：
SQL> select rowid,a.* from t1 a where rownum <= 10;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAASNnAAEAAAAMrAAA 10 ACCOUNTING NEW YORK ---我们准备修改这条记录
AAASNnAAEAAAAMrAAB 20 RESEARCH DALLAS
AAASNnAAEAAAAMrAAC 30 SALES CHICAGO
AAASNnAAEAAAAMrAAD 40 OPERATIONS BOSTON
AAASNnAAEAAAAMvAAA 10 ACCOUNTING NEW YORK
AAASNnAAEAAAAMvAAB 20 RESEARCH DALLAS
AAASNnAAEAAAAMvAAC 30 SALES CHICAGO
AAASNnAAEAAAAMvAAD 40 OPERATIONS BOSTON
AAASNnAAEAAAAMvAAE 10 ACCOUNTING NEW YORK
AAASNnAAEAAAAMvAAF 20 RESEARCH DALLAS
已选择10行。
SQL> select dbms_rowid.rowid_object(rowid) object_id, ----段对象的id
2 dbms_rowid.rowid_relative_fno(rowid) file_id, ---文件编号
3 dbms_rowid.rowid_block_number(rowid) block_id, ---块号
4 dbms_rowid.rowid_row_number(rowid) row_num ---行号
5 from t1 where rownum<=10;
OBJECT_ID FILE_ID BLOCK_ID ROW_NUM
---------- ---------- ---------- ----------
74599 4 811 0
74599 4 811 1
74599 4 811 2
74599 4 811 3
74599 4 815 0
74599 4 815 1
74599 4 815 2
74599 4 815 3
74599 4 815 4
74599 4 815 5
已选择10行。
SQL>
SQL> select * from t1 where rowid='AAASNnAAEAAAAMrAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL>
我们来把dname的值改为'AAAA'，然后研究下dumpfile的内容：
先找到我们的tracefile：
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
-----------------------------------------------------------------------
c:\app\administrator\diag\rdbms\denver\denver\trace\denver_ora_4852.trc
SQL>
在事务开始之前，我们转储一次data block：
SQL> alter system dump datafile 4 block 811;
系统已更改。
SQL>
看看tracefile的内容：
*** 2015-10-09 13:37:47.755
*** SESSION ID:(11.38) 2015-10-09 13:37:47.755
*** CLIENT ID:() 2015-10-09 13:37:47.755
*** SERVICE NAME:(SYS$USERS) 2015-10-09 13:37:47.755
*** MODULE NAME:(SQL*Plus) 2015-10-09 13:37:47.755
*** ACTION NAME:() 2015-10-09 13:37:47.755
Start dump data blocks tsn: 4 file#:4 minblk 811 maxblk 811
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16778027
BH (0x2BFCA3A8) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B858000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 94,28
dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
hash: [0x3D7951CC,0x3D7951CC] lru: [0x2BFCA528,0x2BFCA380]
ckptq: [NULL] fileq: [NULL] objq: [0x3915B2E8,0x2BFCA398]
st: XCURRENT md: NULL tch: 3
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x0100032b (4/811)
scn: 0x0000.0010bef5 seq: 0x02 flg: 0x04 tail: 0xbef50602
frmt: 0x02 chkval: 0x1d08 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0EDB2200 to 0x0EDB4200
EDB2200 0000A206 0100032B 0010BEF5 04020000 [....+...........]
EDB2210 00001D08 00000001 00012367 0010BEF3 [........g#......]
EDB2220 00000000 00320003 01000328 0000FFFF [......2.(.......]
EDB2230 00000000 00000000 00000000 00008000 [................]
EDB2240 0010BEF3 00000000 00000000 00000000 [................]
EDB2250 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
EDB2270 00000000 00000000 00000000 00040100 [................]
EDB2280 001AFFFF 1F0A1F24 00001F0A 1F660004 [....$.........f.]
EDB2290 1F3C1F50 00001F24 00000000 00000000 [P.<.$...........]
EDB22A0 00000000 00000000 00000000 00000000 [................]
Repeat 495 times
EDB41A0 0203002C 4F0A29C1 41524550 4E4F4954 [,....).OPERATION]
EDB41B0 4F420653 4E4F5453 0203002C 53051FC1 [S.BOSTON,......S]
EDB41C0 53454C41 49484307 4F474143 0203002C [ALES.CHICAGO,...]
EDB41D0 520815C1 41455345 06484352 4C4C4144 [...RESEARCH.DALL]
EDB41E0 002C5341 0BC10203 4343410A 544E554F [AS,......ACCOUNT]
EDB41F0 08474E49 2057454E 4B524F59 BEF50602 [ING.NEW YORK....]
Block header dump: 0x0100032b
Object id on Block? Y
seg/obj: 0x12367 csc: 0x00.10bef3 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000328 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.0010bef3
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0100032b
data_block_dump,data header at 0xedb227c
===============
tsiz: 0x1f80
hsiz: 0x1a
pbl: 0x0edb227c
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f24
avsp=0x1f0a
tosp=0x1f0a
0xe:pti[0] nrow=4offs=0
0x12:pri[0] offs=0x1f66
0x14:pri[1] offs=0x1f50
0x16:pri[2] offs=0x1f3c
0x18:pri[3] offs=0x1f24
block_row_dump:
tab 0, row 0, @0x1f66
tl: 26 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 41 43 43 4f 55 4e 54 49 4e 47
col 2: [ 8] 4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1f50
tl: 22 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 15
col 1: [ 8] 52 45 53 45 41 52 43 48
col 2: [ 6] 44 41 4c 4c 41 53
tab 0, row 2, @0x1f3c
tl: 20 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 1f
col 1: [ 5] 53 41 4c 45 53
col 2: [ 7] 43 48 49 43 41 47 4f
tab 0, row 3, @0x1f24
tl: 24 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 29
col 1: [10] 4f 50 45 52 41 54 49 4f 4e 53
col 2: [ 6] 42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 811 maxblk 811
红色部分表示该数据块上有3个itl事务槽（事务槽的个数会随着数据块上的事务增涨而增加），并且从Lck字段看出，该数据块上还没有事务发生。
下面我们来更新一条记录，并将脏数据写出到数据文件：
SQL> update t1 set dname='AAAA' where rowid='AAASNnAAEAAAAMrAAA';
已更新 1 行。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select * from t1 where rowid='AAASNnAAEAAAAMrAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 AAAA NEW YORK
SQL>
再来转储一下数据块：
*** 2015-10-09 13:44:15.192
Start dump data blocks tsn: 4 file#:4 minblk 811 maxblk 811
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16778027
BH (0x2BFC9EB0) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B84C000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
hash: [0x2BFCA424,0x3D7951CC] lru: [0x30FD346C,0x2BFC9E88]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFCA3A8) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B858000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
hash: [0x3D7951CC,0x2BFC9F2C] lru: [0x2C7F2478,0x2FFE4ED0]
lru-flags: moved_to_tail on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
*** 2015-10-09 13:44:15.239
Block dump from disk:
buffer tsn: 4 rdba: 0x0100032b (4/811)
scn: 0x0000.0015b38d seq: 0x01 flg: 0x04 tail: 0xb38d0601
frmt: 0x02 chkval: 0x377b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0EDB2200 to 0x0EDB4200
EDB2200 0000A206 0100032B 0015B38D 04010000 [....+...........]
EDB2210 0000377B 00000001 00012367 0010BEF3 [{7......g#......]
EDB2220 00000000 00320003 01000328 0000FFFF [......2.(.......]
EDB2230 00000000 00000000 00000000 00008000 [................]
EDB2240 0010BEF3 00060003 000003A2 00C0043F [............?...]
EDB2250 001800D3 00060001 00000000 00000000 [................]
EDB2260 00000000 00000000 00000000 00000000 [................]
EDB2270 00000000 00000000 00000000 00040100 [................]
EDB2280 001AFFFF 1F0A1EE2 00001F10 1EE20004 [................]
EDB2290 1F3C1F50 00001F24 00000000 00000000 [P.<.$...........]
EDB22A0 00000000 00000000 00000000 00000000 [................]
Repeat 490 times
EDB4150 00000000 00000000 00000000 022C0000 [..............,.]
EDB4160 0BC10203 41414104 454E0841 4F592057 [.....AAAA.NEW YO]
EDB4170 002C4B52 0BC10203 4343410A 544E554F [RK,......ACCOUNT]
EDB4180 08474E49 2057454E 4B524F59 0203022C [ING.NEW YORK,...]
EDB4190 41040BC1 08414141 2057454E 4B524F59 [...AAAA.NEW YORK]
EDB41A0 0203002C 4F0A29C1 41524550 4E4F4954 [,....).OPERATION]
EDB41B0 4F420653 4E4F5453 0203002C 53051FC1 [S.BOSTON,......S]
EDB41C0 53454C41 49484307 4F474143 0203002C [ALES.CHICAGO,...]
EDB41D0 520815C1 41455345 06484352 4C4C4144 [...RESEARCH.DALL]
EDB41E0 002C5341 0BC10203 4343410A 544E554F [AS,......ACCOUNT]
EDB41F0 08474E49 2057454E 4B524F59 B38D0601 [ING.NEW YORK....]
Block header dump: 0x0100032b
Object id on Block? Y
seg/obj: 0x12367 csc: 0x00.10bef3 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000328 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.0010bef3
0x02 0x0003.006.000003a2 0x00c0043f.00d3.18 ---- 1 fsc 0x0006.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0100032b
data_block_dump,data header at 0xedb227c
===============
tsiz: 0x1f80
hsiz: 0x1a
pbl: 0x0edb227c
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1ee2
avsp=0x1f0a
tosp=0x1f10
0xe:pti[0] nrow=4offs=0
0x12:pri[0] offs=0x1ee2
0x14:pri[1] offs=0x1f50
0x16:pri[2] offs=0x1f3c
0x18:pri[3] offs=0x1f24
block_row_dump:
tab 0, row 0, @0x1ee2
tl: 20 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 0b
col 1: [ 4] 41 41 41 41
col 2: [ 8] 4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1f50
tl: 22 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 15
col 1: [ 8] 52 45 53 45 41 52 43 48
col 2: [ 6] 44 41 4c 4c 41 53
tab 0, row 2, @0x1f3c
tl: 20 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 1f
col 1: [ 5] 53 41 4c 45 53
col 2: [ 7] 43 48 49 43 41 47 4f
tab 0, row 3, @0x1f24
tl: 24 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 29
col 1: [10] 4f 50 45 52 41 54 49 4f 4e 53
col 2: [ 6] 42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 811 maxblk 811
可见itl=0x02的事务槽上有一个事务（从Lck=1得出）。从row里面也可以看出col 1已经改为41 41 41 41，也就是'AAAA'。
从事务槽上的Uba也可以得出，该事务的undo地址为0x00c0043f.00d3.18。
下面我们结束我们的事务：
SQL> rollback;
回退已完成。
SQL>
再dump一次我们的数据块：
*** 2015-10-09 14:06:16.274
Start dump data blocks tsn: 4 file#:4 minblk 811 maxblk 811
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16778027
BH (0x2C7DD044) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2C32E000
set: 12 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
hash: [0x2BFC9F2C,0x3D7951CC] lru: [0x2AFD2CF8,0x2C7DD864]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFC9EB0) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B84C000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
hash: [0x2BFCA424,0x2C7DD0C0] lru: [0x30FD346C,0x2BFC9E88]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
BH (0x2BFCA3A8) file#: 4 rdba: 0x0100032b (4/811) class: 1 ba: 0x2B858000
set: 10 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 0 obj: 74599 objn: 74599 tsn: 4 afn: 4 hint: f
hash: [0x3D7951CC,0x2BFC9F2C] lru: [0x2C7F2478,0x2FFE4ED0]
lru-flags: moved_to_tail on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL]
st: FREE md: NULL tch: 0 lfb: 33
flags:
cr pin refcnt: 0 sh pin refcnt: 0
Block dump from disk:
buffer tsn: 4 rdba: 0x0100032b (4/811)
scn: 0x0000.0015b3f8 seq: 0x01 flg: 0x04 tail: 0xb3f80601
frmt: 0x02 chkval: 0x1d4f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0EDB2200 to 0x0EDB4200
EDB2200 0000A206 0100032B 0015B3F8 04010000 [....+...........]
EDB2210 00001D4F 00000001 00012367 0010BEF3 [O.......g#......]
EDB2220 00000000 00320003 01000328 0000FFFF [......2.(.......]
EDB2230 00000000 00000000 00000000 00008000 [................]
EDB2240 0010BEF3 00000000 00000000 00000000 [................]
EDB2250 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
EDB2270 00000000 00000000 00000000 00040100 [................]
EDB2280 001AFFFF 1F0A1EC8 00001F0A 1EC80004 [................]
EDB2290 1F3C1F50 00001F24 00000000 00000000 [P.<.$...........]
EDB22A0 00000000 00000000 00000000 00000000 [................]
Repeat 489 times
EDB4140 00000000 0203002C 410A0BC1 554F4343 [....,......ACCOU]
EDB4150 4E49544E 454E0847 4F592057 022C4B52 [NTING.NEW YORK,.]
EDB4160 0BC10203 41414104 454E0841 4F592057 [.....AAAA.NEW YO]
EDB4170 002C4B52 0BC10203 4343410A 544E554F [RK,......ACCOUNT]
EDB4180 08474E49 2057454E 4B524F59 0203022C [ING.NEW YORK,...]
EDB4190 41040BC1 08414141 2057454E 4B524F59 [...AAAA.NEW YORK]
EDB41A0 0203002C 4F0A29C1 41524550 4E4F4954 [,....).OPERATION]
EDB41B0 4F420653 4E4F5453 0203002C 53051FC1 [S.BOSTON,......S]
EDB41C0 53454C41 49484307 4F474143 0203002C [ALES.CHICAGO,...]
EDB41D0 520815C1 41455345 06484352 4C4C4144 [...RESEARCH.DALL]
EDB41E0 002C5341 0BC10203 4343410A 544E554F [AS,......ACCOUNT]
EDB41F0 08474E49 2057454E 4B524F59 B3F80601 [ING.NEW YORK....]
Block header dump: 0x0100032b
Object id on Block? Y
seg/obj: 0x12367 csc: 0x00.10bef3 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000328 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.0010bef3
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0100032b
data_block_dump,data header at 0xedb227c
===============
tsiz: 0x1f80
hsiz: 0x1a
pbl: 0x0edb227c
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1ec8
avsp=0x1f0a
tosp=0x1f0a
0xe:pti[0] nrow=4offs=0
0x12:pri[0] offs=0x1ec8
0x14:pri[1] offs=0x1f50
0x16:pri[2] offs=0x1f3c
0x18:pri[3] offs=0x1f24
block_row_dump:
tab 0, row 0, @0x1ec8
tl: 26 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 0b
col 1: [10] 41 43 43 4f 55 4e 54 49 4e 47
col 2: [ 8] 4e 45 57 20 59 4f 52 4b
tab 0, row 1, @0x1f50
tl: 22 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 15
col 1: [ 8] 52 45 53 45 41 52 43 48
col 2: [ 6] 44 41 4c 4c 41 53
tab 0, row 2, @0x1f3c
tl: 20 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 1f
col 1: [ 5] 53 41 4c 45 53
col 2: [ 7] 43 48 49 43 41 47 4f
tab 0, row 3, @0x1f24
tl: 24 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 29
col 1: [10] 4f 50 45 52 41 54 49 4f 4e 53
col 2: [ 6] 42 4f 53 54 4f 4e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 811 maxblk 811
可见itl事务槽上已经没有事务存在的标记。
- data block转储文件初识
- 初识block-转自CocoaChina
- 初识block
- 初识block
- 初识block
- 初识block
- 初识block
- 初识block
- 初识block
- 初识block
- 初识block
- 初识block
- 初识Block
- Block系列1：初识block
- Oracle的data block内容研读(转)
- Oracle的data block内容研读(转)
- 如何阅读oracle数据块data block dump文件
- 初识GCD和block
- android 学习之基础篇一
- C/C++---printf/cout 从右至左压栈顺序实例详解
- Select网络模型
- 使用flume问题总结3——一个使用flume拦截器和选择器的简单实例
- 雅虎34条性能法则
- data block转储文件初识
- Krisch Compass Mask(各个方向的边缘）
- 图像处理&识别：应用
- linux下登录mysql
- 重构想法1
- 萧条时期的SEO要如何着手?
- 2.Python标准库_ 时间与日期 (time, datetime包)
- iOS实现手机的连续震动和停止震动的代码(拷如工程就能用)
- iOS 编译失败，但是没有错误提示(ios build failed ,but without errors)
