|
ROLLBACK SEGMENT
|
Hello everyone. In this issue, I will analyze the relationship of transaction
table, datablock (UNDO BLOCK), and ITL (Interested Transaction List) that exists
in datablock such as tables.
What is ITL?
After you update data in a table, for example, you issue SELECT statement to get that datablock
from other sessions. If you issue SELECT statement before commit, you need to get data before
update (i.e. UNDO BLOCK contained in rollback segment) in order to implement read consistency.
ITL, therefore, contains data to identify transaction table and UNDO BLOCK location.
When ROLLBACK ; command is issued, you can also identify UNDO BLOCK location by using
ITL and get data that is not yet updated.
Ref.46 is an illustration of relationship.
|
-----------------------Abstract of datablock dump for table (dump1)-------------------------
Start dump data blocks tsn: 3 file#: 4 minblk 61826 maxblk 61826
buffer tsn: 3 rdba: 0x0100f182 (4/61826)
scn: 0x0000.00ed6bc1 seq: 0x0c flg: 0x00 tail: 0x6bc1060c
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x0100f182
Object id on Block? Y
seg/obj: 0x1548 csc: 0x00.ed6b7a itc: 1 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0009.000.0000005b uba: 0x00800298.010d.0c ---- 12 fsc 0x000c.00000000
--------------------------------------------------------------------------------------------
|
Xid=0x0009.000.0000005b (shown in hexadecimal) in the dump1 above
indicates rollback segment number, slot number, and sequence number (from left) which exactly
matches state=10 in dump2 below.
index=slot number
wrap#=sequence number
Rollback segment number is in fact a rollback segment specified by set transaction use rollback segment
rb_find50;, and therefore, matches the SEGMENT_ID resulted from SQL statement below.
|
|
SQL> select segment_id from DBA_ROLLBACK SEGS where segment_name = 'RB_FIND50' ;
SEGMENT_ID
----------
9 <--matches Xid=0x0009
|
Uba=0x00800298 in dump1 matches TRN TBL dba=0x00800298 in dump2.
scn:0x0000.00ed6bc1 in dump1 matches TRN TBL scn=0x0000.00ed6bc1 in dump2
It is clear that the location of rollback segment can be identified by ITL of
datablock in a table, which is source data to implement read consistency and
ROLLBACK.
|
|
-------------------Abstract of transaction table [TRN TBL] block dump (dump2)------------------
Start dump data blocks tsn: 1 file#: 2 minblk 662 maxblk 662
buffer tsn: 1 rdba: 0x00800296 (2/662)
scn: 0x0000.00ed6bc1 seq: 0x01 flg: 0x00 tail: 0x6bc10e01
frmt: 0x02 chkval: 0x0000 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS
.......................................................................................
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub
--------------------------------------------------------------------------------------------------
0x00 10 0xc0 0x005b 0x0000 0x0000.00ed6bc1 0x00800298 0x0000.000.00000000 0x00000001
0x01 9 0x00 0x005a 0x0002 0x0000.00ed6b83 0x00000000 0x0000.000.00000000 0x00000000
..................................................................................................
0x13 9 0x00 0x005a 0x0014 0x0000.00ed6b83 0x00000000 0x0000.000.00000000 0x00000000
0x14 9 0x00 0x005a 0xffff 0x0000.00ed6b83 0x00000000 0x0000.000.00000000 0x00000000
--------------------------------------------------------------------------------------------------
|
xid: 0x0009.000.0000005b in both dump1 and 2 matches
xid: 0x0009.000.0000005b in dump3 below.
scn in both dump1 and 2 matches scn: 0x0000.00ed6bc in dump3.
col 1: [ 5] 53 4d 49 54 48 in dump3 is exactly the value of columns that are not yet updated.
|
|
------------------Abstract of UNDO BLOCK block dump (dump3)---------------------------
Start dump data blocks tsn: 1 file#: 2 minblk 664 maxblk 664
buffer tsn: 1 rdba: 0x00800298 (2/664)
scn: 0x0000.00ed6bc1 seq: 0x0d flg: 0x00 tail: 0x6bc1020d
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
********************************************************************************
UNDO BLK:
xid: 0x0009.000.0000005b seq: 0x10d cnt: 0xc irb: 0xc icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x077c 0x02 0x0720 0x03 0x06c8 0x04 0x066c 0x05 0x0610
0x06 0x05b4 0x07 0x0558 0x08 0x0500 0x09 0x04a4 0x0a 0x0448
0x0b 0x03f0 0x0c 0x0394
*-----------------------------
* Rec #0x1 slt: 0x00 objn: 5448(0x00001548) objd: 5448 tblspc: 3(0x00000003)
* 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.00ed6b83 prv tx scn: 0x0000.00ed6b83
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP xtype: XA bdba: 0x0100f182 hdba: 0x0100f181
itli: 1 ispac: 0 maxfr: 1177
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 8 nnew: 1 size: 1
col 1: [ 5] 53 4d 49 54 48
-------------------------------------------------------------------------------------
|
I mainly focus on details of dumps this time so that you can
clearly understand the mechanism of transaction.
That's it for today. See you next week.
|
|
Ora! Ora! Oracle
