[20210604]如何看到递归事务.txt

来源：这里教程网时间：2026-03-03 16:44:28 作者：

[20210604]如何看到递归事务.txt --//上午测试索引分裂以及itl ktbitflg的标识，第一次注意到B标识，关于索引分裂的递归事务的。 --//链接：http://blog.itpub.net/267265/viewspace-2775396/=>[20210604]索引分裂与 itl ktbitflg.txt --//因为递归事务的特殊性，查询v$tranaction无法看到递归事务。有什么方法能看到这个递归事务呢? 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试: --//session 1: drop table t1 purge; create table t1 ( id number ,name varchar2(100)) ; create index i_t1_id on t1(id); --//alter session set events '10224 trace name context forever,level 1'; insert into t1 select rownum,lpad('a',100,rownum) from dual connect by level<=540; commit; --//注:插入540正好索引根节点满了,继续插入索引会发生分裂. SCOTT@book> @ scn GET_SYSTEM_CHANGE_NUMBER SYSDATE ------------------------ ------------------- 13286182066 2021-06-04 09:49:34 SCOTT@book> insert into t1 values (400,lpad('b',100,'b')); 1 row created. SCOTT@book> @ scn GET_SYSTEM_CHANGE_NUMBER SYSDATE ------------------------ ------------------- 13286182097 2021-06-04 09:50:00 --//我开始以为使用 as of scn 模式可以看到，发现不行，它仅仅访问底层的 X$KTCXB ,不支持这种功能，放弃!! 3.换一个思路，设置断点呢? --//多次尝试，发现设置在ktucmt表示ktucmt - kernel transaction undo commit transaction ,可以查询链接： --// orafun.info ,发现一个小小的缺点不能根据描述反查。比如我查询 --//重复上面建表过程略： --//session 1: SCOTT@book> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ------------ ------------ ------------------------ --------- ------ ------- ------------ -------------------------------------------------- 295 5 44886 DEDICATED 44887 21 3 alter system kill session '295,5' immediate; --//session 2: $ gdb -p 44887 ... (gdb) b ktucmt Breakpoint 1 at 0xf1c6aa --//session 1: SCOTT@book> insert into t1 values (100,lpad('b',100,'b')); --//挂起。 --//session 3: SYS@book> select * from v$transaction ; no rows selected --//session 2: --//不断的ni 20 (gdb) ni 20 0x000000000186466e in opitsk () (gdb) ni 20 0x00000000018646b5 in opitsk () (gdb) ni 20 Breakpoint 1, 0x0000000000f1c6aa in ktucmt () --//再次出现ktucmt. --//session 3: SYS@book> select * from v$transaction 2 @ prxx ============================== ADDR : 0000000085AB9D20 XIDUSN : 9 XIDSLOT : 10 XIDSQN : 4145 UBAFIL : 3 UBABLK : 5905 UBASQN : 1938 UBAREC : 1 STATUS : ACTIVE START_TIME : 06/04/21 10:04:02 START_SCNB : 401281729 START_SCNW : 3 START_UEXT : 33 START_UBAFIL : 3 START_UBABLK : 5903 START_UBASQN : 1938 START_UBAREC : 28 SES_ADDR : 0000000087584188 FLAG : 67116579 SPACE : NO RECURSIVE : YES NOUNDO : NO PTX : NO NAME : PRV_XIDUSN : 10 PRV_XIDSLT : 27 PRV_XIDSQN : 36881 PTX_XIDUSN : 0 PTX_XIDSLT : 0 PTX_XIDSQN : 0 DSCN-B : 0 DSCN-W : 0 USED_UBLK : 2 USED_UREC : 2 LOG_IO : 23 PHY_IO : 0 CR_GET : 4 CR_CHANGE : 0 START_DATE : 2021-06-04 10:04:02 DSCN_BASE : 0 DSCN_WRAP : 0 START_SCN : 13286183617 DEPENDENT_SCN : 0 XID : 09000A0031100000 PRV_XID : 0A001B0011900000 PTX_XID : 0000000000000000 ============================== ADDR : 0000000085369CA0 XIDUSN : 9 XIDSLOT : 17 XIDSQN : 4142 UBAFIL : 0 UBABLK : 0 UBASQN : 0 UBAREC : 0 STATUS : ACTIVE START_TIME : 06/04/21 10:04:02 START_SCNB : 401281729 START_SCNW : 3 START_UEXT : 33 START_UBAFIL : 3 START_UBABLK : 5904 START_UBASQN : 1938 START_UBAREC : 3 SES_ADDR : 0000000087584188 FLAG : 67110435 SPACE : NO RECURSIVE : YES NOUNDO : NO PTX : NO NAME : PRV_XIDUSN : 9 PRV_XIDSLT : 10 PRV_XIDSQN : 4145 PTX_XIDUSN : 0 PTX_XIDSLT : 0 PTX_XIDSQN : 0 DSCN-B : 0 DSCN-W : 0 USED_UBLK : 1 USED_UREC : 1 LOG_IO : 9 PHY_IO : 0 CR_GET : 0 CR_CHANGE : 0 START_DATE : 2021-06-04 10:04:02 DSCN_BASE : 0 DSCN_WRAP : 0 START_SCN : 13286183617 DEPENDENT_SCN : 0 XID : 090011002E100000 PRV_XID : 09000A0031100000 PTX_XID : 0000000000000000 ============================== ADDR : 0000000084CFEC50 XIDUSN : 10 XIDSLOT : 27 XIDSQN : 36881 UBAFIL : 3 UBABLK : 896 UBASQN : 7568 UBAREC : 4 STATUS : ACTIVE START_TIME : 06/04/21 10:04:02 START_SCNB : 401280496 START_SCNW : 3 START_UEXT : 3 START_UBAFIL : 3 START_UBABLK : 896 START_UBASQN : 7568 START_UBAREC : 4 SES_ADDR : 0000000087584188 FLAG : 3587 SPACE : NO RECURSIVE : NO NOUNDO : NO PTX : NO NAME : PRV_XIDUSN : 0 PRV_XIDSLT : 0 PRV_XIDSQN : 0 PTX_XIDUSN : 0 PTX_XIDSLT : 0 PTX_XIDSQN : 0 DSCN-B : 0 DSCN-W : 0 USED_UBLK : 1 USED_UREC : 1 LOG_IO : 31 PHY_IO : 0 CR_GET : 5 CR_CHANGE : 0 START_DATE : 2021-06-04 10:04:02 DSCN_BASE : 0 DSCN_WRAP : 0 START_SCN : 13286182384 DEPENDENT_SCN : 0 XID : 0A001B0011900000 PRV_XID : 0000000000000000 PTX_XID : 0000000000000000 PL/SQL procedure successfully completed. --//看到3条记录。注意看RECURSIVE=yes就是递归事务。 SYS@book> @ xid XIDUSN_XIDSLOT_XIDSQN ------------------------------ C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG ---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- ------------------- ------------ ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 10 4145; 9 10 4145 3 5905 1938 1 ACTIVE 2 2 09000A0031100000 0000000085AB9D20 2021-06-04 10:04:02 67116579 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU9_1650507775$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 5905; ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 17 4142; 9 17 4142 0 0 0 0 ACTIVE 1 1 090011002E100000 0000000085369CA0 2021-06-04 10:04:02 67110435 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU9_1650507775$'; ALTER SYSTEM DUMP DATAFILE 0 BLOCK 0; ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 27 36881; 10 27 36881 3 896 7568 4 ACTIVE 1 1 0A001B0011900000 0000000084CFEC50 2021-06-04 10:04:02 3587 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 896; --//继续ni 40，直到出现Breakpoint 1, 0x0000000000f1c6aa in ktucmt 。 SYS@book> @ xid XIDUSN_XIDSLOT_XIDSQN ------------------------------ C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG ---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- ------------------- ------------ ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 10 4145; 9 10 4145 3 5906 1938 1 ACTIVE 3 3 09000A0031100000 0000000085AB9D20 2021-06-04 10:04:02 67116579 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU9_1650507775$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 5906; ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 27 36881; 10 27 36881 3 896 7568 4 ACTIVE 1 1 0A001B0011900000 0000000084CFEC50 2021-06-04 10:04:02 3587 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 896; --//变成2个，最后： SYS@book> @ xid XIDUSN_XIDSLOT_XIDSQN ------------------------------ C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG ---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- ------------------- ------------ ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 27 36881; 10 27 36881 3 896 7568 4 ACTIVE 1 1 0A001B0011900000 0000000084CFEC50 2021-06-04 10:04:02 3587 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 896; --//实际上这次操作存在2次递归事务。 SYS@book> alter system checkpoint ; System altered. SYS@book> @ tix New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_45262_0001.trc SYS@book> alter system dump datafile 4 block 694; System altered. Block header dump: 0x010002b6 Object id on Block? Y seg/obj: 0x16ad1 csc: 0x03.17eb1529 itc: 2 flg: E typ: 2 - INDEX brn: 0 bdba: 0x10002b0 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.00a.00001031 0x00c01712.0792.02 -B-- 1 fsc 0x0000.00000000 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 --//Xid=0x0009.00a.00001031,0x1031 = 4145 能完全对上。 --//Uba=0x00c01712.0792.02 --//0x00c01712 = set dba 3,5906 = alter system dump datafile 3 block 5906 = 12588818 --//0x0792 = 1938 SYS@book> alter system dump datafile 4 block 694; System altered. Block header dump: 0x010002b3 Object id on Block? Y seg/obj: 0x16ad1 csc: 0x03.17eb12c1 itc: 1 flg: E typ: 2 - INDEX brn: 0 bdba: 0x10002b0 ver: 0x01 opc: 0 inc: 0 exflg: 0 Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.00a.00001031 0x00c01711.0792.01 -BU- 1 fsc 0x0000.17eb15a9 --//0x00c01711 = set dba 3,5905 = alter system dump datafile 3 block 5905 = 12588817 --//0x0792 = 1938 --//能与递归事务对上。 --//如果继续看可以看到： SYS@book> @ xid XIDUSN_XIDSLOT_XIDSQN ------------------------------ C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE FLAG ---------------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------ ------------ ---------------- ---------------- ------------------- ------------ ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 31 4144; 9 31 4144 3 5906 1938 54 ACTIVE 1 2 09001F0030100000 0000000085AB9D20 2021-06-04 10:23:58 67116579 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU9_1650507775$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 5906; ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU10_1197734989$' XID 10 27 36881; 10 27 36881 3 896 7568 4 ACTIVE 1 1 0A001B0011900000 0000000084CFEC50 2021-06-04 10:04:02 3587 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU10_1197734989$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 896; SYS@book> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 31 4144; System altered. *** 2021-06-04 10:29:14.043 ******************************************************************************** Undo Segment: _SYSSMU9_1650507775$ (9) xid: 0x0009.01f.00001030 Low Blk : (0, 0) High Blk : (35, 7) Object Id : ALL Layer : ALL Opcode : ALL Level : 2 +++++++++++ XID mismatch. Some records may not belong to specified transaction. + WARNING + Block xid: 0x0009.021.0000102e dba (file#, block#): 3,0x00001712 +++++++++++ Input xid: 0x0009.01f.00001030 ******************************************************************************** UNDO BLK: Extent: 33 Block: 18 dba (file#, block#): 3,0x00001712 xid: 0x0009.021.0000102e seq: 0x792 cnt: 0x37 irb: 0x37 icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1f70 0x02 0x1ef8 0x03 0x1e48 0x04 0x1dc8 0x05 0x1d1c 0x06 0x1c6c 0x07 0x1bbc 0x08 0x1b50 0x09 0x1ae4 0x0a 0x1a78 0x0b 0x19c8 0x0c 0x195c 0x0d 0x18f0 0x0e 0x1884 0x0f 0x17d4 0x10 0x1768 0x11 0x16fc 0x12 0x164c 0x13 0x15e0 0x14 0x1574 0x15 0x14c4 0x16 0x1454 0x17 0x13a4 0x18 0x12f4 0x19 0x1288 0x1a 0x11d8 0x1b 0x116c 0x1c 0x1100 0x1d 0x1050 0x1e 0x0fe4 0x1f 0x0f78 0x20 0x0f08 0x21 0x0e58 0x22 0x0dec 0x23 0x0d80 0x24 0x0d14 0x25 0x0c64 0x26 0x0bb4 0x27 0x0b48 0x28 0x0a98 0x29 0x09e4 0x2a 0x0934 0x2b 0x08c8 0x2c 0x085c 0x2d 0x07b0 0x2e 0x06fc 0x2f 0x064c 0x30 0x05d0 0x31 0x0564 0x32 0x04b8 0x33 0x0408 0x34 0x0334 0x35 0x02ac 0x36 0x0258 0x37 0x01bc *----------------------------- * Rec #0x35 slt: 0x1f objn: 92881(0x00016ad1) objd: 92881 tblspc: 4(0x00000004) * Layer: 10 (Index) opc: 21 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x00c01712.0792.34 ctl max scn: 0x0003.17eb1789 prv tx scn: 0x0003.17eb178b txn start scn: scn: 0x0003.17eb17f2 logon user: 83 prev brb: 12588816 prev bcl: 0 index general undo (branch) operations KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x0009.00a.00001031 uba: 0x00c01712.0792.01 flg: CBU- lkc: 0 scn: 0x0003.17eb1789 Dump kdige : block dba :0x010002b5, seghdr dba: 0x010002b2 unlock block (1): 01 +++++++++++ Next block not in extent map - rollback segment has been shrunk. + WARNING + Block dba (file#, block#): 0,0x00000000 +++++++++++ *----------------------------- * Rec #0x36 slt: 0x1f objn: 92881(0x00016ad1) objd: 92881 tblspc: 4(0x00000004) * Layer: 10 (Index) opc: 22 rci 0x35 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- index undo for leaf key operations KTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: C uba: 0x00c01712.0792.35 Dump kdilk : itl=1, kdxlkflg=0x1 sdc=1 indexid=0x10002b2 block=0x010002b5 (kdxlin): insert leaf row key :(10): 02 c2 02 06 01 00 02 b8 00 0c --//视乎是插入索引键值的。c2 02 编码对应100，后面的应该是rowid。 ************************************* Total undo blocks scanned = 1 Total undo records scanned = 55 Total undo blocks dumped = 1 Total undo records dumped = 2 ##Total warnings issued = 2 ************************************* 4.总结：测试有点乱。

编辑推荐：

返回列表

[20210604]如何看到递归事务.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推