[ORACLE] transaction and consistency (trn tbl and ITL basics), trnitl

[ORACLE] transaction and consistency (trn tbl and ITL basics), trnitl
4 transactions and consistency 4.1 undo segment header transaction table and transaction control table (trn tbl and TRN ctl)

-- Undo header contains the transaction table trn tbl used to mark a simple list of recent transactions,

Trn tbl ::

Index state cflags wrap # uel scn dba parent-xid nub stmt_num cmt

Bytes ------------------------------------------------------------------------------------------------

0x00 9 0x00 0x0146 0x002e 0x0000. 000c4261 0x008000e8 0x0000. 000.00000000 0x00000001 0x00000000 1422514868

0x01 9 0x00 0x0146 0x0007 0x0000. 000c43f4 0x008000e8 0x0000. 000.00000000 0x00000001 0x00000000 1422515883

0x02 9 0x00 0x0146 0x002c 0x0000. 000c43d3 0x008000e8 0x0000. 000.00000000 0x00000001 0x00000000 1422515883

0x03 9 0x00 0x0146 0x0008 0x0000. 000c440a 0x008000e8 0x0000. 000.00000000 0x00000001 0x00000000 1422515883

 

-- The undo header contains the trn ctl in the transaction control area to describe the status of the transaction table in detail.

Trn ctl: seq: 0x00cc chd: 0x0009 ctl: 0x002f inc: 0x00000000 nfb: 0x0002

Mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)

Uba: 0x008000b9. 00cc. 06 scn: 0x0000. 000c3c00

Version: 0x01

Free block pool ::

Uba: 0x008000b9. 00cc. 08 ext: 0x7 spc: 0x1c7e

Uba: 0x008000e6. 00cb. 13 ext: 0x6 spc: 0x1024

Uba: 0x00000000. 00c9. 2f ext: 0x4 spc: 0xc7c

Uba: 0x00000000. 0000.00 ext: 0x0 spc: 0x0

Uba: 0x00000000. 0000.00 ext: 0x0 spc: 0x0

 

-- Commit command

The Commit command also produces a Change vector, because the state and scn of the two fields of the undo transaction table are modified;

 

-- Transaction ID: xid

The transaction ID contains the undo segment number (). The index value of the transaction table (starting from ox00) and warp # constitute the transaction ID.

4.2 view transaction xid

-- View the xid transaction number

SQL> update scott. stu set stu_id = 4 where stu_id = 5;

 

1 row updated

SQL> select xidusn, xidslot, xidsqn, ubablk, ubafil, ubarec from v $ transaction;

XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC

------------------------------------------------------------

2 43 313 31 2 52

-- Xidusn: undo segment number

-- Xidslot: slot number

-- Xidsqn: sequence number (wrap #)

-- Ubablk: The block number of the undo file in which the undo record is located. Uba block number

-- UBAFILE: The undo file number of the undo record.

// Data file 2, block 31

 

SQL> select trunc (id1/65536) usn, mod (id1, 65536) slot, id2 wrap, lmode from v $ lock where type = 'tx ';

 

USN SLOT WRAP LMODE

----------------------------------------

2 43 313 6

// The lmod mode is 6 exclusive. Slot 43 to hexadecimal, 0x2b:

 

-- Obtain the slot ox2b information of the transaction table in the header of the undo Field 2.

Index state cflags wrap # uel scn dba parent-xid nub stmt_num cmt

0x2b 10 0x80 0x0139 0x0000 0x0000. 000c44bc 0x0080001f 0x0000. 000.00000000 0x00000001 0x00000000 0

// The state is 10 and the activity status is

// Cflags 0x80 indicates an active transaction, ox00 indicates no transaction, and 0x10 indicates a dead transaction.

// When uel is ox0000, the next available transaction slot is recorded as information.

// Dba, number of the last undo blocks when the transaction is written to undo

// Nub is 1, and the current transaction uses an undo data block.

// Block 31

4.3 undo records of transactions with the transaction ID XID

-- Information of the undo record corresponding to the dump transaction.

Alter system dump undo Block' _ SYSSMU2 $ 'xid 2 43 313

-- View the dump information of the undo record

Undo Segment: _ SYSSMU2 $ (2)

Xid:0x0002. 02b. 00000139

Undo blk:Extent: 0 Block: 5Dba (file #, block #):2,0x0000001f (10 in 31)

KDO undo record:

KTB Redo

Op: 0x03 ver: 0x01

Op: Z

KDO Op code: URP row dependencies Disabled

Xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x020.cc hdba: 0x020.cb

Itli: 2 ispac: 0 maxfr: 4858

Tabn: 0 slot: 0 (0x0) flag: 0x2c lock: 0 ckix: 161

Ncol: 4Nnew: 1Size: 0

Vector content:

Col 0: [2] c1 06

4.4 you can use the x $ ktuxe table to obtain the transaction xid information (segment number, slot number) and the transaction address (data file number, and block number) at the same time)

-- X $ ktuxe usage

SQL> select indx, ktuxesqn wrap #, ktuxerdbf dba_file, ktuxerdbb dba_block from x $ ktuxe

2 where ktuxesta = 'active ';

 

Indx wrap # DBA_FILE DBA_BLOCK

----------------------------------------

189 313 2 31

 

4.5 concurrent action test

1. view the address of the test table head.

(1) Create table t1 (id number, n1 number)

(2)

Select HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS, INITIAL_EXTENT, EXTENTS, NEXT_EXTENT

From dba_segments where segment_type = 'table' and owner = 'Scott 'and segment_name = 't1 ';

 

HEADER_FILE HEADER_BLOCK bytes blocks INITIAL_EXTENT EXTENTS NEXT_EXTENT

------------------------------------------------------------------------------

4 251 65536 8 65536 1

2. Three processes update t1 3 Records.

3. table t1 dump file:

SQL> alter system dump datafile 4 block min 251 block max 258;

4. Read the table dump file:

(1) t1 header (segment_header ):

Buffer tsn: 4Rdba: 0x00000fb (4/251)

Scn: 0x0000. 000c6195 seq: 0x02 flg: 0x04 tail: 0x61952302

 

(4) buffer tsn: 4 rdba: 0x0366fe (4/254)

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0001. 00b. 00000147 0x0080000d. 00ce. 1b ---- 1 fsc 0x0000.00000000

0x02 0x0005. 025.0000013b 0x00800a05. 010f. 14 ---- 1 fsc 0x0000.00000000

0x03 0x0009. 01e. 00000150 0x0080015f. 00a8. 33 ---- 1 fsc 0x0000.00000000

Block_row_dump:

Tab 0, row 0, @ 0x1f76

Tl: 10 fb: -- H-FL -- lb: 0x2 cc: 2

Col 0: [2] c1 02

Col 1: [3] c2 02 02

Tab 0, row 1, @ 0x1f6c

Tl: 10 fb: -- H-FL -- lb: 0x1 cc: 2

Col 0: [2] c1 03

Col 1: [3] c2 03 02

Tab 0, row 2, @ 0x1f62

Tl: 10 fb: -- H-FL -- lb: 0x3 cc: 2

Col 0: [2] c1 04

Col 1: [3] c2 04 02

End_of_block_dump

 

5. Read the undo header record:

View undo TRN tab transactions

SQL> select xidusn, xidslot, xidsqn, ubablk, ubafil, ubarec from v $ transaction;

 

XIDUSN XIDSLOT XIDSQN UBABLKUBAFIL UBAREC

------------------------------------------------------------

1 11 327 13 2 27

9 30 336 351 2 51

5 37 315 2565 2 20

// The undo records of the three transactions. In the three undo fields, the slot in the header records the transaction list information. Some data blocks in data file 2 Store undo records.

SQL> select trunc (id1/65536) usn, mod (id1, 65536) slot, id2 wrap, lmode from v $ lock where type = 'tx ';

 

USN SLOT WRAP LMODE

----------------------------------------

1 11 327 6

9 30, 336, 6

5 37 315 6

Obtain the undo header information of the corresponding transaction for the undo segment through xid

Alter system dump undo header '_ SYSSMU9 $ ';

Alter system dump undo header '_ SYSSMU1 $ ';

Alter system dump undo header '_ SYSSMU5 $ ';

 

Undo Segment: _ SYSSMU9 $ (9)

Index state cflags wrap # uel scnDbaParent-xid nub stmt_num cmt

Bytes ------------------------------------------------------------------------------------------------

0x1e 10 0x80 0x0150 0x0002 0x0000. 000c6202 0x0080015f 0x0000. 000.00000000 0x00000001 0x00000000 0

 

Undo Segment: _ SYSSMU1 $ (1)

Index state cflags wrap # uel scn dba parent-xid nub stmt_num cmt

Bytes ------------------------------------------------------------------------------------------------

0x0b 10 0x80 0x0147 0x0000 0x0000. 000c61c3 0x0080000d 0x0000. 000.00000000 0x00000001 0x00000000 0

 

Undo Segment: _ SYSSMU5 $ (5)

IndexState cflagsWrap # uelScnDba parent-xid nub stmt_num cmt

Bytes ------------------------------------------------------------------------------------------------

0x25 10 0x80 0x013b 0x0002 0x0000. 000c61b8 0x00800a05 0x0000. 000.00000000 0x00000001 0x00000000 0

4.5.1 data block address

1. DBA

DBA (Data Block Address) generally refers to the absolute Data Block Address.

For example, the Undo data block address:

 Dba (file #, block #):2,0x0000001f (10 in 31)

 

For example, the XID in the ITL table corresponds to the undo data block address.

V $ transaction tableUbablk= 351

 

In the TRN table, the corresponding transaction ID corresponding to dba (0x0080015f) is converted to 10 hexadecimal: 351;

2. rdba

Rdba (Tablespace relative database block address) is the relative data block address,

For exampleRdba: 0x00000fb (4/251) fb is converted to 10 in 251

If the data file is greater than or equal to 1024, that is, the power of 2 is 10, it can only be expressed by absolute dba internally. Generally, it is represented by rdba, which limits a data file.