认真就输

www.htz.pw > select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

www.htz.pw > !uname -a
Linux orcl9i 2.6.9-89.EL #1 Mon Apr 20 10:22:29 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

CREATE TABLE htz.htz
(
   ID               NUMBER,
   OWNER            VARCHAR2 (30),
   OBJECT_NAME      VARCHAR2 (128),
   SUBOBJECT_NAME   VARCHAR2 (30),
   OBJECT_ID        NUMBER,
   DATA_OBJECT_ID   NUMBER,
   OBJECT_TYPE      VARCHAR2 (30),
   CREATED          DATE,
   LAST_DDL_TIME    DATE,
   TIMESTAMP        VARCHAR2 (30),
   STATUS           VARCHAR2 (30),
   TEMPORARY        VARCHAR2 (30),
   GENERATED        VARCHAR2 (30),
   SECONDARY        VARCHAR2 (30),
   NAMESPACE        VARCHAR2 (30),
   EDITION_NAME     VARCHAR2 (30)
)
www.cdhtz.com > alter system set "_disable_logging"=true scope=spfile;
System altered.
www.cdhtz.com > startup force;
/* Formatted on 2014/11/13 13:38:39 (QP5 v5.240.12305.39446) */
BEGIN
   FOR i IN 1 .. 2000
   LOOP
      INSERT INTO htz.htz
         SELECT htz.htz_sequence.NEXTVAL,
                OWNER,
                OBJECT_NAME,
                SUBOBJECT_NAME,
                OBJECT_ID,
                DATA_OBJECT_ID,
                OBJECT_TYPE,
                CREATED,
                LAST_DDL_TIME,
                TIMESTAMP,
                STATUS,
                TEMPORARY,
                GENERATED,
                SECONDARY,
                NAMESPACE,
                EDITION_NAME
           FROM dba_objects;
      COMMIT;
   END LOOP;
END;
/
表的大小为18G，有点大哦。
www.cdhtz.com >  select segment_name,bytes/1024/1024/1024 from dba_segments where owner='HTZ' and segment_name='HTZ';

SEGMENT_NA BYTES/1024/1024/1024
---------- --------------------
HTZ                  18.7705078
www.cdhtz.com > alter database tempfile 1 autoextend on maxsize 31g;
www.cdhtz.com > alter database datafile 4 autoextend on maxsize 31g;

www.cdhtz.com > select /*+ parallel(a 4) */ * from htz.htz a where mod(id,2)=0 for update;
等SQL还回结果后，KILL掉for update的进程，事务等待smon进程来回退。

www.cdhtz.com > oradebug setospid 13491
Oracle pid: 13, Unix process pid: 13491, image: oracle@orcl9i (SMON)
www.cdhtz.com > oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl1123/orcl1123/trace/orcl1123_smon_13491.trc
www.cdhtz.com > oradebug event 10704 trace name context forever,level 12;
Statement processed.
在trace文件中，可以看到下面的信息，这里可以看到很多TX级别的锁，模式都为6，但是在整个TRACE文件中，并找到在对象HTZ上面的任何TM级别的锁，说明SMON在事务回退过程中，只占用行级别的锁，不占用TM级别的锁，但是正常情况下获取TX级别的锁之前必须获取TM级别的锁。另外为什么说这么的TX级别说就是之前的for update语句占用的TX锁，这个问题会在另外说明的。
*** 2014-11-14 10:27:08.161
ksqcmi: TX,100020,ad mode=6 timeout=0
ksqcmi: returns 51
ksqgtl: RETURNS 51

*** 2014-11-14 10:27:08.161
ksqgtl *** TX-00100020-000000ad mode=6 flags=0x10001 timeout=0 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
        ksqlkdid: 0001-000D-00000002

*** 2014-11-14 10:27:08.161
*** ksudidTrace: ksqgtl
        ksusesdi:   0001-000D-00000003
        ksusetxn:   0001-000D-00000002

*** 2014-11-14 10:27:08.161
ksqcmi: TX,100020,ad mode=6 timeout=0
ksqcmi: returns 51
ksqgtl: RETURNS 51

*** 2014-11-14 10:27:08.161
ksqgtl *** TX-00100020-000000ad mode=6 flags=0x10001 timeout=0 ***
ksqgtl: no transaction
ksqgtl: use existing ksusetxn DID
ksqgtl:
        ksqlkdid: 0001-000D-00000002

*** 2014-11-14 10:27:08.161
*** ksudidTrace: ksqgtl
        ksusesdi:   0001-000D-00000003
        ksusetxn:   0001-000D-00000002

*** 2014-11-14 10:27:08.161
ksqcmi: TX,100020,ad mode=6 timeout=0
ksqcmi: returns 51
ksqgtl: RETURNS 51

www.cdhtz.com > update htz.htz set owner='HTZ' where id=2;
1 row updated.
www.cdhtz.com > update htz.htz set owner='HTZ' where id=4;
1 row updated.
www.cdhtz.com > rollback;
Rollback complete.
www.cdhtz.com > update htz.htz set owner='HTZ' where id=82163330;
1 row updated.
这里看到单行的DML语句是没有受到影响的
www.cdhtz.com > update htz.htz set owner='HTZ' where   mod(id,2)=0;
HANG住了，为什么这里HANG住了呢？这里我们可以查询到update处理的行号是一直在变化的，整个进程不是被真的HANG住了，而是执行相当的慢的，因为update在更新行的时候会获取TX6级别的锁，而SMON进程在回退的时候同样会获取TX为6级别的锁，所以就导致出现TX级别的等待。
所以通过DML测试可以看到SMON在回退死事务的时候是不会阻塞其它的DML事务，除非SMON回退行时，持有正在回退行的TX 6级别的锁，如果此时事务要修改此行，就会出现TX等待，但是很快就应该消失。

新开一个会话，并记录下会话的SPID
www.cdhtz.com > @tran_dead.sql
KTUXECFL                   COUNT(*)
------------------------ ----------
DEAD                              2
NONE                            470
ADDR               KTUXEUSN   KTUXESLT   KTUXESQN   KTUXESIZ
---------------- ---------- ---------- ---------- ----------
0000002A97FC5278         15          4        135          0
0000002A97FC5C18         16         32        173     556643
www.cdhtz.com > select * from v$rollname where usn=16;

       USN NAME
---------- ------------------------------
        16 _SYSSMU16$
上面查看到死事务的XID与UNDO段的名字，下面通过dump undo段的信息，这里注意到xid的信息可以跟前面提到 SMON持有的tx锁的ID1相关联。ID1就是由ktuxeusn,ktuxeslt两部分组成，可以通过下面SQL来转换
select trunc(id1 / 65536) usn, mod(id1 / 65536) slot, id2 wrap, Imode
  from v$lock
 where type = 'TX';