SQL connect test/test@test
已连接。
SQL create table test (a number);
表已创建。
SQL insert into test values(1);
已创建 1 行。
SQL select segment_name,header_file,header_block from dba_segments where segmen
t_name like 'TEST';
SEGMENT_NAME
HEADER_FILE
HEADER_BLOCK
TEST
1
35387
SQL alter system dump datafile 1 block 35388;
系统已更改。
DUMP出数据头文件查看itl(interested transaction list)
*** 2003-06-09 18:41:19.359
Start dump data blocks tsn: 0 file#: 1 minblk 35388 maxblk 35388
buffer tsn: 0 rdba: 0x00408a3c (1/35388)
scn: 0x0000.00105cd3 seq: 0x04 flg: 0x00 tail: 0x5cd30604
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:
0x00408a3c
Object id on Block? Y
seg/obj: 0x6487
csc: 0x00.105cd2
itc: 1
flg: O
typ: 1 - DATA
fsl: 0
fnx: 0x0 ver: 0x01
Itl
Xid
Uba
Flag
Lck
Scn/Fsc
0x01
xid:
0x0008.000.00000002 uba: 0x00800dc4.0000.05 ----
1
fsc 0x001c.00000000
/*这里可以看到xid= 0x0008.000.00000002(事务id)
uba= 0x00800dc4.0000.05(undo block address)
lck=
1(受影响的行数)
根据Xid的结构得到
0x0008.000.00000002
0x0008 ? Undo Segment Number
000 ? Transaction Table Slot Number
00000002? Wrap
根据uba的结构得到
0x00800dc4.0000.05
0x00800dc4? Address of the last undo block used
0000 ? Sequence
05 ? Last Entry in UNDO record map
*/
data_block_dump
===============
以下省略。。。。。。
根据 0x0008 ? Undo Segment Number，
SQL
select a.segment_name,a.header_file,a.header_block from dba_segments a,dba
_rollback_segs b where a.segment_name=b.segment_name and b.segment_id='8';
SEGMENT_NAME
HEADER_FILE
HEADER_BLOCK
RBS7
2
3522
然后dump rbs头查看trans table
Start dump data blocks tsn: 1 file#: 2 minblk 3522 maxblk 3522
buffer tsn: 1 rdba: 0x00800dc2 (2/3522)
scn: 0x0000.00105cd2 seq: 0x01 flg: 0x00 tail: 0x5cd20e01
frmt: 0x02 chkval: 0x0000 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0
space2: 0
#extents: 8
#blocks: 511
last map
0x00000000
#maps: 0
offset: 4128
Highwater::
0x00800dc4
ext#: 0
blk#: 1
ext size: 63
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk
0x00000000
offset: 0
Unlocked
Map Header:: next
0x00000000
#extents: 8
obj#: 0
flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00800dc3
length: 63
0x00800d42
length: 64
0x00800582
length: 64
0x00800342
length: 64
0x00800482
length: 64
0x008017c2
length: 64
0x00801802
length: 64
0x00800c42
length: 64
TRN CTL:: seq: 0x0000 chd: 0x0001 ctl: 0x0061 inc: 0x00000000 nfb: 0x0000
mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800dc4.0000.01 scn: 0x0000.00000000
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0000.00 ext: 0x0
spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0
spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0
spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0
spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0
spc: 0x0
TRN TBL::
index
state cflags
wrap#
uel
scn
dba
parent-xid
nub
------------------------------------------------------------------------------------------------
0x00
10
0x80
0x0002
0x0000
0x0000.00105cd2
0x00800dc4
0x0000.000.00000000
0x00000001
0x01
9
0x00
0x0001
0x0002
0x0000.00000000
0x00000000
0x0000.000.00000000
0x00000000
以下省略。。。。。。。
根据从xid中得到的000 ? Transaction Table Slot Number
去找到事务表中记载的undo块的地址dba＝0x00800dc4（也可从uba中直接得到）
接下来我们来看一下undo头的地址rdba: 0x00800dc2 (2/3522)
所以我们去dump 3524即undo头+2
*** 2003-06-09 18:42:52.734
Start dump data blocks tsn: 1 file#: 2 minblk 3524 maxblk 3524
buffer tsn: 1 rdba: 0x00800dc4 (2/3524)
scn: 0x0000.00105cd3 seq: 0x04 flg: 0x00 tail: 0x5cd30204
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
********************************************************************************
UNDO BLK:
xid: 0x0008.000.00000002
seq: 0x0
cnt: 0x5
irb: 0x5
icl: 0x0
flg: 0x0000
Rec Offset
Rec Offset
Rec Offset
Rec Offset
Rec Offset
---------------------------------------------------------------------------
0x01 0x1f80
0x02 0x1f18
0x03 0x1eb0
0x04 0x1e48
0x05 0x1de0
*-----------------------------
* Rec #0x1
slt: 0x00
objn: 25735(0x00006487)
objd: 25735
tblspc: 0(0x00000000)
*
Layer:
11 (Row)
opc: 1
rci 0x00
Undo type:
Regular undo
Begin trans
Last buffer split:
No
Temp Object:
No
Tablespace Undo:
No
rdba: 0x00000000
*-----------------------------
uba: 0x00000000.0000.00 ctl max scn: 0x0000.00000000 prv tx scn: 0x0000.00000000
KDO undo record:
KTB Redo
op: 0x04
ver: 0x01
op: L
itl: scn:
0x0004.049.000000d8 uba: 0x00800716.009f.3a
flg: C-U-
lkc:
0
scn: 0x0000.00105ccf
KDO Op code: DRP
xtype: XA
bdba: 0x00408a3c
hdba: 0x00408a3b
itli: 1
ispac: 0
maxfr: 4863
tabn: 0 slot: 1(0x1)
根据KDO Op code: DRP，表明反操作是delete,所以我们可以知道这就是刚才insert后在undo segment里记载的信息
我们知道当发生insert的时候undo segment里仅记载了记录的rowid，下面我们把它找出来
SQL select rowid from test;
ROWID
------------------
AAAGSHAABAAAIo8AAC
Translate the value: AAAGSHAABAAAIo8AAC
Data Object number = AAAGSH
File = AAB
Block = AAAIo8
ROW = AAC
然后根据公式转换
得到data object number=25735
file=1
block=35388
row=2
