A-A+
唯一性组合索引引起的插入的sql hang住的分析
唯一性组合索引引起的插入的sql hang住的分析
问题分析：拥有相同组合索引列键值的sql，在批量操作时可能会导致undo争用，致使会话挂起。
=============以下为分析过程=============
表TBLCERTREQ有以下有关的唯一性索引
第一个：主键产生的唯一性索引
SQL> select * from dba_ind_columns where table_owner='CAUSER' and table_name='TBLCERTREQ' and index_name='SYS_C001605'; INDEX_OWNE INDEX_NAME TABLE_OWNE TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC ---------- ------------------------------ ---------- ---------- --------------- --------------- ------------- ----------- ---- CAUSER SYS_C001605 CAUSER TBLCERTREQ REF 1 32 32 ASC
第二个：组合键值产生的唯一性索引
SQL> select * from dba_ind_columns where table_owner='CAUSER' and table_name='TBLCERTREQ' and index_name='TBLCERTREQ_INDEX02'; INDEX_OWNER INDEX_NAME TABLE_OWNE TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC -------------------- ------------------------------ ---------- ---------- -------------------- --------------- ------------- ----------- ---- CAUSER TBLCERTREQ_INDEX02 CAUSER TBLCERTREQ DN 1 512 512 ASC CAUSER TBLCERTREQ_INDEX02 CAUSER TBLCERTREQ CERTTMLID 2 22 0 ASC CAUSER TBLCERTREQ_INDEX02 CAUSER TBLCERTREQ USERSTATUS 3 22 0 ASC CAUSER TBLCERTREQ_INDEX02 CAUSER TBLCERTREQ STATUSEX 4 32 32 ASC
创建时间
SQL> select OWNER,OBJECT_NAME,CREATED from dba_objects where OBJECT_NAME='SYS_C001605'; OWNER OBJECT_NAME CREATED ------------------------------ -------------------- ------------------- CAUSER SYS_C001605 2009/08/01 01:50:45 SQL> select OWNER,OBJECT_NAME,CREATED from dba_objects where OBJECT_NAME='TBLCERTREQ_INDEX02'; OWNER OBJECT_NAME CREATED ------------------------------ -------------------- ------------------- CAUSER TBLCERTREQ_INDEX02 2009/08/01 01:49:30
第一个会话(session id:29)
插入一条记录但迟迟不提交
SQL> select sid from v$mystat where rownum<2;
SID
----------
29
SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
2 values('08f03e7a3793acd295e1366c41dc5caf','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
3 'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
4 113,0,3,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');
1 row created.
SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
2 values('08f03e7a3793acd295e1366c41dc5ccf','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
3 'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
4 113,0,3,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');
insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
*
ERROR at line 1:
ORA-00001: unique constraint (YALLONKING.TBLCERTREQ_INDEX02) violated <-同一会话中继续插入组合索引值相同时报违反唯一性索引的错误
第二个会话(session id:33)
正常操作
SQL> select sid from v$mystat where rownum<2;
SID
----------
33
SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
2 values('08f03e7a3793acd295e1366c41dc5cag','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
3 'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
4 113,0,3,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');
^Cinsert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation <-此处发现插入hang住了，人为取消
注：这次插入的记录中，(DN,CERTTMLID,USERSTATUS,STATUSEX)组和值和第一个会话插入的一致，也就是在违反唯一性组合索引的情况下插入。
由于第一个会话没有提交，根据事务ACID特性中的隔离性使得会话二无法看到会话一的数据，所以数据库也就不会报违反唯一性的相关错误以至于会话二一直等待会话一提交。如果会话一提交后，则会话二会报违反唯一性的相关错误。
如下：
SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
2 values('08f03e7a3793acd295e1366c41dc5cag','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
3 'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
4 113,0,3,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');
insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
*
ERROR at line 1:
ORA-00001: unique constraint (YALLONKING.TBLCERTREQ_INDEX02) violated
继续查看将要插入的条目的主键ref是否和已存在的记录重复了
SQL> select * from tblcertreq where ref='08f03e7a3793acd295e1366c41dc5cag'; no rows selected <-发现主键不存在重复 SQL> select * from tblcertreq where dn='CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN'; no rows selected <-发现该DN也不存在
但是发现插入USERSTATUS=5却是可以的
SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
2 values('08f03e7a3793acd295e1366c41dc5cag','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
3 'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
4 113,0,5,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');
1 row created.
SQL> rollback;
回滚掉5后继续插入
SQL> insert into tblcertreq (ref,AUTHCODE,DN,NOTBEFORE,NOTAFTER,CERTTMLID,KEYSTRUSTEDFLAG,USERSTATUS,AUTHNOTAFTER,SIGN,STATUSEX)
2 values('08f03e7a3793acd295e1366c41dc5cag','AnszcgexrSoXfzgiZ/0cj9h/mtUZrvfCYM9GQnT8OWTpnAEqUn/U6Uq4RHJSYYbj>',
3 'CN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA Operation CA2,C=CN','2013.11.22-18:38:00','2018.11.22-18:38:00',
4 113,0,3,'2013.12.20-14:53:29','4LHHItaSMfB1q5HS8oX1KA==>','0');
//此处继续hang住
第三个会话(session id:36)
在该回话中查看锁相关信息
SQL> select sid from v$mystat where rownum<2;
SID
----------
36
SQL> sho user
USER is "SYS"
SQL> SELECT inst_id,DECODE(request, 0, 'Holder :', 'Waiter :') || sid sess,
2 id1,
3 id2,
4 lmode,
5 2 request,
6 type
7 FROM gV$LOCK
8 WHERE (id1, id2, type) IN
9 (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
10 ORDER BY inst_id,id1, request;
INST_ID SESS ID1 ID2 LMODE REQUEST TY
---------- ------------------------------------------------ ---------- ---------- ---------- ---------- --
1 Waiter :33 196614 706 0 2 TX
1 Holder :29 196614 706 6 2 TX
此处发现确实是会话33在等待会话29释放锁
注： TX - Transaction enqueue
LMODE 6 - exclusive (X)
REQUEST 2 - row-S (SS)
ID1:以十进制数值表示该事务所占用的回滚段号与该事务在该回滚段的事务表（Transaction table）中所占用的槽号（slot number，可理解为记录号）。其组成形式为： 0xRRRRSSSS ( RRRR = RBS number, SSSS = slot )。
ID2:以十进制数值表示环绕（wrap）次数，即该槽（slot）被重用的次数；
从上述查询结果可知，2个会话争用的资源是同一回滚段资源
将上边的ID1换算如下
SQL> select trunc(196614/65536),mod(196614,65536) from dual;
TRUNC(196614/65536) MOD(196614,65536)
------------------- -----------------
3 6
说明：争用的是3号回滚段的6号事务槽
查询事务表获得更详细信息
SQL> select ADDR,XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------------
0000000095A18098 6 32 889 3 535 181 31 ACTIVE
0000000095A753F8 3 6 706 3 322 155 22 ACTIVE
SQL> select FILE_ID, FILE_NAME,TABLESPACE_NAME from dba_data_files where FILE_ID=3;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- --------------------
3 /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf UNDOTBS1
说明：从上边这个查询可以看到具体争用的是位于3号数据文件(也就是UNDO数据文件,322号数据块上)的3号回滚段的6号事务槽的第706次记录
转储3号回滚段的UNDO段头
SQL> select * from V$ROLLNAME where USN=3;
USN NAME
---------- ------------------------------
3 _SYSSMU3_307413221$
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump undo header '_SYSSMU3_307413221$';
System altered.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_2310.trc
SQL> oradebug close_trace
Statement processed.
trace中关于事务表信息如下：
TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ------------------------------------------------------------------------------------------------ 0x00 9 0x00 0x02c2 0x0013 0x0000.000eab9c 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x01 9 0x00 0x02c2 0x0019 0x0000.000eacba 0x00c00140 0x0000.000.00000000 0x00000001 0x00000000 1387368974 0x02 9 0x00 0x02c3 0x000c 0x0000.000eae5a 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1387370017 0x03 9 0x00 0x02c1 0x0016 0x0000.000eae97 0x00c00142 0x0000.000.00000000 0x00000001 0x00000000 1387370127 0x04 9 0x00 0x02c2 0x0002 0x0000.000eae0a 0x00c00141 0x0000.000.00000000 0x00000001 0x00000000 1387369828 0x05 9 0x00 0x02c3 0x0001 0x0000.000eac7d 0x00c00140 0x0000.000.00000000 0x00000003 0x00000000 1387368958 0x06 10 0x80 0x02c2 0x0005 0x0000.000eafa7 0x00c00142 0x0000.000.00000000 0x00000001 0x00000000 0 0x07 9 0x00 0x02c4 0x000e 0x0000.000eaeaa 0x00c00142 0x0000.000.00000000 0x00000001 0x00000000 1387370127 0x08 9 0x00 0x02c3 0x0012 0x0000.000eac26 0x00c0013a 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x09 9 0x00 0x02c3 0x0015 0x0000.000eab46 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x0a 9 0x00 0x02c1 0x000b 0x0000.000eab23 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x0b 9 0x00 0x02c3 0x0018 0x0000.000eab2d 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x0c 9 0x00 0x02c2 0x0003 0x0000.000eae8e 0x00c00142 0x0000.000.00000000 0x00000001 0x00000000 1387370127 0x0d 9 0x00 0x02c1 0x001e 0x0000.000eab78 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x0e 9 0x00 0x02c3 0x0017 0x0000.000eaeb3 0x00c00142 0x0000.000.00000000 0x00000001 0x00000000 1387370127 0x0f 9 0x00 0x02c1 0x001d 0x0000.000eab0f 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x10 9 0x00 0x02c4 0x0008 0x0000.000eac19 0x00c0013a 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x11 9 0x00 0x02c4 0x001a 0x0000.000eabbd 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x12 9 0x00 0x02c3 0x0005 0x0000.000eac33 0x00c0013a 0x0000.000.00000000 0x00000001 0x00000000 1387368928 0x13 9 0x00 0x02c3 0x0011 0x0000.000eabae 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x14 9 0x00 0x02c0 0x0021 0x0000.000eabd9 0x00c0013a 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x15 9 0x00 0x02c1 0x001f 0x0000.000eab57 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x16 9 0x00 0x02c2 0x0007 0x0000.000eaea0 0x00c00142 0x0000.000.00000000 0x00000001 0x00000000 1387370127 0x17 9 0x00 0x02c4 0x0020 0x0000.000eaebc 0x00c00142 0x0000.000.00000000 0x00000001 0x00000000 1387370127 0x18 9 0x00 0x02c1 0x0009 0x0000.000eab38 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x19 9 0x00 0x02c2 0x0004 0x0000.000ead49 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1387369353 0x1a 9 0x00 0x02c2 0x0014 0x0000.000eabcb 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x1b 9 0x00 0x02c2 0x0010 0x0000.000eac07 0x00c0013a 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x1c 9 0x00 0x02c3 0x001b 0x0000.000eabf9 0x00c0013a 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x1d 9 0x00 0x02c1 0x000a 0x0000.000eab19 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x1e 9 0x00 0x02c2 0x0000 0x0000.000eab89 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x1f 9 0x00 0x02c1 0x000d 0x0000.000eab67 0x00c0013e 0x0000.000.00000000 0x00000001 0x00000000 1387368927 0x20 9 0x00 0x02c3 0xffff 0x0000.000eaf30 0x00c00142 0x0000.000.00000000 0x00000001 0x00000000 1387370428 0x21 9 0x00 0x02c2 0x001c 0x0000.000eabe8 0x00c0013a 0x0000.000.00000000 0x00000001 0x00000000 1387368927
注： 11g中该表只能存储34（0x21=33）条记录，然后就进行覆盖重用
state:9-incative,10-active
说明：发现只有第七个记录（0x06）是活动的，其wrap#=0x02c2（706）
把这个块dump出来继续分析
SQL> oradebug setmypid Statement processed. SQL> alter system dump datafile 3 block 322; System altered. SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_2310.trc
部分trace文件如下：
UNDO BLK:
xid: 0x0003.006.000002c2 seq: 0x9b cnt: 0x16 irb: 0x16 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f38 0x02 0x1e88 0x03 0x1e1c 0x04 0x1d6c 0x05 0x1cc0
0x06 0x1c38 0x07 0x1bd4 0x08 0x1b90 0x09 0x1b3c 0x0a 0x1af8
0x0b 0x1aa4 0x0c 0x1a1c 0x0d 0x19c0 0x0e 0x1930 0x0f 0x18f4
0x10 0x1894 0x11 0x1834 0x12 0x17e0 0x13 0x1770 0x14 0x170c
0x15 0x1664 0x16 0x15f0
... ...
*-----------------------------
* Rec #0x13 slt: 0x06 objn: 71495(0x00011747) objd: 71495 tblspc: 5(0x00000005)
* Layer: 11 (Row) opc: 1 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: 0x00c00142.009b.0e ctl max scn: 0x0000.000eaafb prv tx scn: 0x0000.000eab05
txn start scn: scn: 0x0000.000eaede logon user: 80
prev brb: 12583230 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0140000f hdba: 0x0140000a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 5(0x5)
*-----------------------------
* Rec #0x14 slt: 0x06 objn: 71496(0x00011748) objd: 71496 tblspc: 5(0x00000005)
* Layer: 10 (Index) opc: 22 rci 0x13
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: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
Dump kdilk : itl=3, kdxlkflg=0x1 sdc=0 indexid=0x1400012 block=0x01400013
(kdxlpu): purge leaf row
key :(33):
20 30 38 66 30 33 65 37 61 33 37 39 33 61 63 64 32 39 35 65 31 33 36 36 63
34 31 64 63 35 63 61 66
*-----------------------------
* Rec #0x15 slt: 0x06 objn: 71497(0x00011749) objd: 71497 tblspc: 5(0x00000005)
* Layer: 10 (Index) opc: 22 rci 0x14
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: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x000a.008.000002cf uba: 0x00c00118.00c1.39
flg: C--- lkc: 0 scn: 0x0000.000eacc3
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x140001a block=0x0140001b
(kdxlpu): purge leaf row
key :(77):
43 43 4e 3d 39 35 35 38 30 31 30 30 34 32 31 35 38 38 38 35 2c 4f 55 3d 43
75 73 74 6f 6d 65 72 73 2c 4f 55 3d 59 55 5a 48 49 2c 4f 3d 43 46 43 41 20
4f 70 65 72 61 74 69 6f 6e 20 43 41 32 2c 43 3d 43 4e 03 c2 02 0e 02 c1 04
01 30
*-----------------------------
* Rec #0x16 slt: 0x06 objn: 71498(0x0001174a) objd: 71498 tblspc: 5(0x00000005)
* Layer: 10 (Index) opc: 22 rci 0x15
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: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x000a.008.000002cf uba: 0x00c00118.00c1.3a
flg: C--- lkc: 0 scn: 0x0000.000eacc3
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1400022 block=0x01400023
(kdxlpu): purge leaf row
key :(27):
13 32 30 31 33 2e 31 32 2e 32 30 2d 31 34 3a 35 33 3a 32 39 06 01 40 00 0f
00 05
End dump data blocks tsn: 2 file#: 3 minblk 322 maxblk 322
查看争用的资源
SQL> select utl_raw.cast_to_varchar2('13323031332e31322e32302d31343a35333a3239060140000f0005') from dual;
UTL_RAW.CAST_TO_VARCHAR2('13323031332E31322E32302D
--------------------------------------------------
2013.12.20-14:53:29@ <-由于测试数据日期相同，无法分辨
SQL> select utl_raw.cast_to_varchar2('43434e3d393535383031303034323135383838352c4f553d437573746f6d6572732c4f553d59555a48492c4f3d43464341204f7065726174696f6e204341322c433d434e03c2020e02c1040130') from dual;
UTL_RAW.CAST_TO_VARCHAR2('43434E3D3935353830313030
--------------------------------------------------
CCN=9558010042158885,OU=Customers,OU=YUZHI,O=CFCA
Operation CA2,C=CN??0 <-由于测试数据DN相同，无法分辨
SQL> select utl_raw.cast_to_varchar2('203038663033653761333739336163643239356531333636633431646335636166') from dual;
UTL_RAW.CAST_TO_VARCHAR2('203038663033653761333739336163643239356531333636633431
--------------------------------------------------------------------------------
08f03e7a3793acd295e1366c41dc5caf <-此处为具有可识别性的第一个会话插入的ref(主键)值
至此，发现该回滚段的事物槽存放的第一个一直迟迟没有提交的会话的记录信息
下边继续用bbed查看在物理数据块中的具体值,进一步确认所争用的资源信息
BBED> set dba 3,322
DBA 0x00c00142 (12583234 3,322)
BBED> find /c 08f03e7a3793acd295e1366c41dc5cag
BBED-00212: search string not found
BBED> find /c 08f03e7a3793acd295e1366c41dc5caf
File: /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf (3)
Block: 322 Offsets: 5985 to 6496 Dba:0x00c00142
------------------------------------------------------------------------
30386630 33653761 33373933 61636432 39356531 33363663 34316463 35636166
00000008 004c0008 00140047 17010047 17010005 00000000 0000000b 01060008
0c010000 00000042 01c0009b 000e00fb aa0e0000 00290005 ab0e0000 0000009f
aa0e00de ae0e0000 0001003e 01c00000 00000050 00000003 0d000008 0c01000f
0040010a 004001fa 12030102 00ffff05 0000000c 00180010 00140002 0008000d
0100000d 01000000 00000000 0000000a 16201100 00c30202 0d140018 00200042
01c0009b 000e0012 02059780 07400081 07400000 00000000 0015ad01 80000000
80ee4f00 00440008 00180020 0020000e 0100000c 01000001 00000000 0000000b
01201000 00c30204 0d0700c4 02000008 001000ca 020000fa 00c000ce 00040000
800000c0 440c0085 b6800092 018000fa 120a0101 00000000 80ec5e00 000080ee
48000001 00000008 00180020 0020000e 0100000c 01000001 00000000 0000000b
01200f00 00c30204 0d0700c4 02000008 001000ca 020000fa 00c000ce 00020000
800000c0 440c005e ec800092 018000fa 120a0101 000000ac 0101ad85 b60080b6
85000002 00000008 00180008 0014000e 0100000c 01000001 00000000 0000000b
01200e00 00c30203 0d0000a5 ae0e0048 ee800092 018000fa 12030101 00000000
0000ad0a 004c0020 00140002 0000000d 0100000d 01000000 00000000 0000000a
<32 bytes per line>
BBED> dump /v dba 3,322 offset 5985 count 32
File: /u01/app/oracle/oradata/ora11gr2/undotbs01.dbf (3)
Block: 322 Offsets: 5985 to 6016 Dba:0x00c00142
-------------------------------------------------------
30386630 33653761 33373933 61636432 l 08f03e7a3793acd2
39356531 33363663 34316463 35636166 l 95e1366c41dc5caf
<16 bytes per line>
以上均确认了争用的资源是会话一持有的3号数据文件的3号回滚段的6号事务槽的第706次记录位。
