Document Type | Technical Information
Category | Monitoring/Inspection
Document Number | TMOTI023
Overview
This guide explains how information related to transaction processing is recorded and handled inside data blocks, and how to verify the internal data block information using the dump function.
NoteThe test was conducted in the following environment.
Method
Table Creation and Data Insertion
CREATE TABLE TIBERO.T2 (c1 number, c2 char(2000)); insert into tibero.t2 values(111,'A'); insert into tibero.t2 values(222,'B'); insert into tibero.t2 values(333,'C'); insert into tibero.t2 values(444,'D'); insert into tibero.t2 values(555,'E'); insert into tibero.t2 values(666,'F');
Data Query
select C1, SUBSTR(C2,1,1) from tibero.t2; C1 C2 ----------- 111 A 222 B 333 C 444 D 555 E 666 F
Querying File and Block Numbers
select C1,
rowid,
dbms_rowid.rowid_block_number(rowid) as blkno,
dbms_rowid.rowid_to_relative_fno(rowid) as fno2
from tibero.t2;
C1 ROWID BLKNO FNO2
---------- ------------------ ---------- ----------
111 AAAA5LAACAAADocAAA 14876 2
222 AAAA5LAACAAADocAAB 14876 2
333 AAAA5LAACAAADocAAC 14876 2
444 AAAA5LAACAAADodAAA 14877 2
555 AAAA5LAACAAADodAAB 14877 2
666 AAAA5LAACAAADodAAC 14877 2
Executing Data Block Dump
alter system dump datafile 2 block 14876; alter system dump datafile 2 block 14877;
Explanation of Block Dump Items and Analysis Results
ITL (Interested Transaction List)
- In the example, the fl (flag) value of itl 0 is registered as 'A'.
- 'A' means that the transaction is in an active state that has not been committed.
Row Piece (rp)
- Currently, there are **6 row pieces (rp)** spanning a total of 2 blocks.
- There are 3 rp in the first block and 3 rp in the second block.
xid value: 0007.33.0134
- This is interpreted as three columns (USN, SLOT, SQN) in the v$transaction view.
Column | Description |
|---|---|
USN=7 | Rollback information is stored in undo segment number 7 |
SLOT=33 | Related information stored in transaction slot number 33 within the undo segment's header block |
SQN=134 | Indicates that the transaction slot has been reused 134 times (incremented by +1 each reuse) |
uea value
- This represents the address of the undo block.
- Accessing the UNDO block via this address shows that undo data for the data block is stored here.
Dump Results
- The dump performed before the transaction was committed shows that the fl value of the itl information for all 6 rows is set to 'A'.
- This means that all rows are in the middle of a transaction (active).
[t72499@localhost tracedump]$ cat tb_dump_20650_19_425.trc
Block Dump Results
**Dump start at 2017-12-18 18:21:09
DUMP of BLOCK file #2 block #14876
**Dump start at 2017-12-18 18:21:09
data block Dump[dba=02_00014876(8403484),tsn=0000.000a61d7,type=13,seqno =3]
--------------------------------------------------------------
sgmt_id=3660 cleanout_tsn=0000.00000000 btxcnt=2
l1dba=02_00014871(8403479), offset_in_l1=5
btx xid undo fl tsn/credit
00 0007.33.0134 01_00028547.00011.00077 A 0000.00000000
01 0000.00.0000 00_00000000.00000.00000 I 0000.00000000
--------------------------------------------------------------
Data block dump:
dlhdr_size=14 freespace=2060 freepos=2158 symtab_offset=0 rowcnt=3
Row piece dump:
rp 0 6178: [2010] flag=--H-FL-- itlidx=0 colcnt=2
col 0: [4]
0000: 03 C2 81 8B ....
col 1: [2003]
0000: FE 07 D0 41 20 20 20 20 20 20 20 20 20 20 20 20 ...A
0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~~~~omitted~~~~
07D0: 20 20 20
rp 1 4168: [2010] flag=--H-FL-- itlidx=0 colcnt=2
col 0: [4]
0000: 03 C2 82 96 ....
col 1: [2003]
0000: FE 07 D0 42 20 20 20 20 20 20 20 20 20 20 20 20 ...B
0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~~~~omitted~~~~
07D0: 20 20 20
rp 2 2158: [2010] flag=--H-FL-- itlidx=0 colcnt=2
col 0: [4]
0000: 03 C2 83 A1 ....
col 1: [2003]
0000: FE 07 D0 43 20 20 20 20 20 20 20 20 20 20 20 20 ...C
0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~~~~omitted~~~~
07D0: 20 20 20
End of data block dump**Dump start at 2017-12-18 18:21:31
DUMP of BLOCK file #2 block #14877
**Dump start at 2017-12-18 18:21:31
data block Dump[dba=02_00014877(8403485),tsn=0000.000a61d7,type=13,seqno =3]
--------------------------------------------------------------
sgmt_id=3660 cleanout_tsn=0000.00000000 btxcnt=2
l1dba=02_00014871(8403479), offset_in_l1=6
btx xid undo fl tsn/credit
00 0007.33.0134 01_00028547.00011.00080 A 0000.00000000
01 0000.00.0000 00_00000000.00000.00000 I 0000.00000000
--------------------------------------------------------------
Data block dump:
dlhdr_size=14 freespace=2060 freepos=2158 symtab_offset=0 rowcnt=3
Row piece dump:
rp 0 6178: [2010] flag=--H-FL-- itlidx=0 colcnt=2
col 0: [4]
0000: 03 C2 84 AC ....
col 1: [2003]
0000: FE 07 D0 44 20 20 20 20 20 20 20 20 20 20 20 20 ...D
0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~~~~omitted~~~~
07D0: 20 20 20
rp 1 4168: [2010] flag=--H-FL-- itlidx=0 colcnt=2
col 0: [4]
0000: 03 C2 85 B7 ....
col 1: [2003]
0000: FE 07 D0 45 20 20 20 20 20 20 20 20 20 20 20 20 ...E
0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~~~~omitted~~~~
07D0: 20 20 20
rp 2 2158: [2010] flag=--H-FL-- itlidx=0 colcnt=2
col 0: [4]
0000: 03 C2 86 C2 ....
col 1: [2003]
0000: FE 07 D0 46 20 20 20 20 20 20 20 20 20 20 20 20 ...F
0010: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
0020: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
~~~~~omitted~~~~
[t72499@localhost tracedump]$