本文共 6552 字，大约阅读时间需要 21 分钟。
[20170518]不同事务能使用相同回滚段吗.txt
--//昨天别人问的问题,不同事务能使用相同回滚段吗?按照道理oracle会均匀分配事务到不同的回滚段,如果事务很多,oracle会自动建立
--//新的回滚段.正常的测试应该模拟建立多个事务,不提交看看是否存在回滚段是相同的.--//oracle还可以通过alter system set "_smu_debug_mode" = 45;加上set transaction use rollback segment "_SYSSMUx_yyyyy$";
--//指定回滚段.如果测试一样可以说明问题.1.环境:
SCOTT@book> @ &r/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 ProductionSCOTT@book> select us#,name,file# from sys.undo$ where file#=3;
US# NAME FILE# ---------- ------------------------------ ---------- 1 _SYSSMU1_3724004606$ 3 2 _SYSSMU2_2996391332$ 3 3 _SYSSMU3_1723003836$ 3 4 _SYSSMU4_1254879796$ 3 5 _SYSSMU5_898567397$ 3 6 _SYSSMU6_1263032392$ 3 7 _SYSSMU7_2070203016$ 3 8 _SYSSMU8_517538920$ 3 9 _SYSSMU9_1650507775$ 3 10 _SYSSMU10_1197734989$ 3 10 rows selected.--//我测试环境没有什么事务,仅仅10个回滚段,不包括system的回滚段.
SCOTT@book> alter system set "_smu_debug_mode" = 45 scope=memory;
System altered.2.测试:
--//session 1: SCOTT@book> @&r/s SCOTT@book(24,401)> @ &r/spid SID SERIAL# SPID PID P_SERIAL# C50 ---------- ---------- ------ ------- ---------- -------------------------------------------------- 24 401 55159 26 190 alter system kill session '24,401' immediate;SCOTT@book(24,401)> set transaction use rollback segment "_SYSSMU3_1723003836$";
Transaction set.SCOTT@book(24,401)> update deptx set dname=lower(dname) where deptno=10;
1 row updated.SCOTT@book(24,401)> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN ------------------------------ 3.2.1774C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU3_1723003836$' XID 3 2 1774; 3 2 1774 3 1444 934 17 ACTIVE 1 1 03000200EE060000 0000000081926DA0 2017-05-18 09:20:10 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU3_1723003836$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1444;--//session 2:
SCOTT@book> @ &r/s SCOTT@book(46,1415)> @ &r/spid SID SERIAL# SPID PID P_SERIAL# C50 ---------- ---------- ------ ------- ---------- -------------------------------------------------- 46 1415 55171 28 210 alter system kill session '46,1415' immediate;SCOTT@book(46,1415)> set transaction use rollback segment "_SYSSMU3_1723003836$"; Transaction set.
SCOTT@book(46,1415)> update deptx set dname=lower(dname) where deptno=20;
1 row updated.SCOTT@book(46,1415)> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN ------------------------------ 3.0.1774C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU3_1723003836$' XID 3 0 1774; 3 0 1774 3 1445 934 1 ACTIVE 1 1 03000000EE060000 00000000818C5090 2017-05-18 09:21:55 ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU3_1723003836$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1445;ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU3_1723003836$' XID 3 2 1774; 3 2 1774 3 1444 934 17 ACTIVE 1 1 03000200EE060000 0000000081926DA0 2017-05-18 09:20:10
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU3_1723003836$'; ALTER SYSTEM DUMP DATAFILE 3 BLOCK 1444;--//可以发现2个事务可以使用相同的回滚段.
3.做一个转储.
SYS@book> alter system checkpoint ; System altered.SYS@book> alter system checkpoint ;
System altered.SYS@book> alter system checkpoint ;
System altered.SYS@book> ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU3_1723003836$';
System altered.--//检查转储内容:
******************************************************************************** Undo Segment: _SYSSMU3_1723003836$ (3) ******************************************************************************** Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 3 #blocks: 143 last map 0x00000000 #maps: 0 offset: 4080 Highwater:: 0x00c005a5 ext#: 2 blk#: 37 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 2 Unlocked Map Header:: next 0x00000000 #extents: 3 obj#: 0 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x00c000a1 length: 7 0x00c00128 length: 8 0x00c00580 length: 128Retention Table
----------------------------------------------------------- Extent Number:0 Commit Time: 1495040439 Extent Number:1 Commit Time: 1495040439 Extent Number:2 Commit Time: 1495040439TRN CTL:: seq: 0x03a6 chd: 0x0005 ctl: 0x001f inc: 0x00000000 nfb: 0x0000
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe) uba: 0x00c005a5.03a6.01 scn: 0x0003.1769ada2 Version: 0x01 FREE BLOCK POOL:: uba: 0x00000000.03a6.10 ext: 0x2 spc: 0x16ca uba: 0x00000000.03a6.02 ext: 0x2 spc: 0x1f06 uba: 0x00000000.03a6.26 ext: 0x2 spc: 0x32a uba: 0x00000000.03a0.01 ext: 0x2 spc: 0x1f84 uba: 0x00000000.03a0.01 ext: 0x2 spc: 0x1f84 TRN TBL::index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------ 0x00 10 0xc0 0x06ee 0x0002 0x0003.1769d9b8 0x00c005a5 0x0000.000.00000000 0x00000001 0x00000000 0 0x01 9 0x00 0x06ee 0x0006 0x0003.1769d17e 0x00c005a1 0x0000.000.00000000 0x00000001 0x00000000 1495069249 0x02 10 0xc0 0x06ee 0x0002 0x0003.1769d95d 0x00c005a4 0x0000.000.00000000 0x00000001 0x00000000 0 0x03 9 0x00 0x06ec 0x000f 0x0003.1769c053 0x00c005a1 0x0000.000.00000000 0x00000001 0x00000000 1495065949 0x04 9 0x00 0x06ec 0x0007 0x0003.1769b251 0x00c005a1 0x0000.000.00000000 0x00000001 0x00000000 1495062948 0x05 9 0x00 0x06eb 0x001c 0x0003.1769adfe 0x00c005a1 0x0000.000.00000000 0x00000001 0x00000000 1495062047 0x06 9 0x40 0x06ed 0x0012 0x0003.1769d253 0x00c005a1 0x0000.000.00000000 0x00000001 0x00000000 1495069356 0x07 9 0x00 0x06ed 0x001e 0x0003.1769b2cf 0x00c005a1 0x0000.000.00000000 0x00000001 0x00000000 1495062948 0x08 9 0x00 0x06eb 0x0020 0x0003.1769d753 0x00c005a1 0x0000.000.00000000 0x00000001 0x00000000 1495070303 0x09 9 0x00 0x06eb 0x0015 0x0003.1769d775 0x00c0059c 0x0000.000.00000000 0x00000001 0x00000000 1495070303 ...--//可以发现slot=0,2的是事务是激活的,还没有提交.这也从另外的方面证明不同事务使用相同回滚段是可能的.
转载地址：http://erqtx.baihongyu.com/