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: 0x6487csc: 0x00.105cd2itc: 1flg: Otyp: 1 - DATA
fsl: 0fnx: 0x0 ver: 0x01
ItlXidUbaFlagLck Scn/Fsc
0x01 xid:0x0008.000.00000002 uba: 0x00800dc4.0000.05 ----1fsc 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，
SQLselect 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
RBS72 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: 0space2: 0#extents: 8#blocks: 511
last map0x00000000#maps: 0offset: 4128
Highwater::0x00800dc4ext#: 0blk#: 1ext size: 63
#blocks in seg. hdr's freelists: 0
#blocks below: 0
mapblk0x00000000offset: 0
Unlocked
Map Header:: next0x00000000#extents: 8obj#: 0flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00800dc3length: 63
0x00800d42length: 64
0x00800582length: 64
0x00800342length: 64
0x00800482length: 64
0x008017c2length: 64
0x00801802length: 64
0x00800c42length: 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: 0x0spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0spc: 0x0
TRN TBL::
indexstate cflagswrap#uel scndba parent-xidnub
------------------------------------------------------------------------------------------------
0x00 100x800x00020x00000x0000.00105cd20x00800dc40x0000.000.000000000x00000001
0x0190x000x00010x00020x0000.000000000x000000000x0000.000.000000000x00000000
以下省略。
。。。。。。
根据从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.00000002seq: 0x0 cnt: 0x5 irb: 0x5 icl: 0x0 flg: 0x0000
Rec OffsetRec OffsetRec OffsetRec OffsetRec Offset
---------------------------------------------------------------------------
0x01 0x1f80 0x02 0x1f18 0x03 0x1eb0 0x04 0x1e48 0x05 0x1de0
*-----------------------------
* Rec #0x1slt: 0x00objn: 25735(0x00006487)objd: 25735tblspc: 0(0x00000000)
* Layer:11 (Row) opc: 1 rci 0x00
Undo type:Regular undoBegin transLast 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: 0x04ver: 0x01
op: Litl: scn:0x0004.049.000000d8 uba: 0x00800716.009f.3a
flg: C-U-lkc:0 scn: 0x0000.00105ccf
KDO Op code: DRPxtype: XAbdba: 0x00408a3chdba: 0x00408a3b
itli: 1ispac: 0maxfr: 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
