oracle回滚机制的深入探究
undo机制是oracle的巧妙之处，也是学习oracle的难点之一。
下面我们从内部来具体了解它。
会话1：scott用户，发起一个事务
[sql]
hr@ORCL> conn scott/oracle
Connected.
scott@ORCL> UPDATE emp SET sal=4000 WHERE empno=7788;
1 row updated.
www.2cto.com
scott@ORCL> SELECT empno,ename,job,mgr,sal FROM emp WHERE empno=7788;
EMPNO ENAME JOB MGR SAL
---------- ---------- --------- ---------- ----------
7788 SCOTT ANALYST 7566 4000
先不提交这个事务，会话2：SYS用户
获得事务信息
从v$transaction,可得：该事务在9号回滚段，位于26号事务槽
[sql]
sys@ORCL> SELECT xidusn,xidslot FROM v$transaction;
XIDUSN XIDSLOT
---------- ----------
9 26
从v$rollstat，也可见该事务高就9号回滚段
[sql]
sys@ORCL> SELECT usn,xacts FROM v$rollstat;
www.2cto.com
USN XACTS
---------- ----------
0 0
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 1
10 0
dump回滚段的段头信息
[sql]
sys@ORCL> SELECT * FROM v$rollname a WHERE a.usn=9;
USN NAME
---------- ------------------------------
9 _SYSSMU9$
sys@ORCL> alter system dump undo header '_SYSSMU9$';
www.2cto.com
System altered.
sys@ORCL> select spid from v$process where addr in (select paddr from v$session where sid in (select sid
2 from v$mystat where rownum=1));
SPID
------------
10086
部分内容摘入如下：
[sql]
********************************************************************************
Undo Segment: _SYSSMU9$ (9)
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 4 #blocks: 31
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00800615 ext#: 2 blk#: 4 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 0 www.2cto.com
mapblk 0x00000000 offset: 2
Unlocked
Map Header:: next 0x00000000 #extents: 4 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x0080008a length: 7
0x008000d9 length: 8
0x00800611 length: 8
0x00800669 length: 8
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1344269736
Extent Number:1 Commit Time: 1344276385
Extent Number:2 Commit Time: 1344264118
Extent Number:3 Commit Time: 1344264252
www.2cto.com
TRN CTL:: seq: 0x0105 chd: 0x0019 ctl: 0x0014 inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00800615.0105.11 scn: 0x0000.000d2615
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.0105.10 ext: 0x2 spc: 0x16f6
uba: 0x00000000.0105.03 ext: 0x2 spc: 0x1eac
uba: 0x00000000.0101.0b ext: 0xb spc: 0x198c
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index st