这个问题的研究是一步一步来的。

最开始，客户想知道对于一个已经kill掉的数据库会话，如何评估SMON需要多久才能回滚成功这个会话之前做过的事务，其实也就是这个会话多久才会被完全kill掉。

由于被kill掉的会话所打开的事务已经无法在v$transation视图中查询到，因此需要从数据库基表x$ktuxe中查询。

SELECT ktuxesiz FROM x$ktuxe WHERE ktuxesta != ‘INACTIVE’;

可以获得这个会话还在使用的undo block数量，然后过10秒钟，再查询一次，之后两次得到的数值相减，就是10秒内SMON回滚成功的undo block数量，这样再跟当前的ktuxesiz 值比较一下，就可以估算出大概还需要多长时间才能完成整个工作。
Metalink Note:43653.1详细描述了x$ktuxe的各列含义，Eygle的一篇文章也描述了这个方法。

然后，我在自己的机器上实验对这个基表的查询，忽然发现自己的机器上也有一个仍然active的transaction。但是我自己机器上的数据库我并没有做什么DML操作，为什么会有一个active transaction呢？这个transaction还使用了2个undo block。

SQL> SELECT ktuxesiz FROM x$ktuxe WHERE ktuxesta != ‘INACTIVE’;

KTUXESIZ
———-
2

继续检查自己机器上的情况，确实是有一个active的事务。

SQL> select addr,xidusn,xidslot,xidsqn,status from v$transaction ;

ADDR XIDUSN XIDSLOT XIDSQN STATUS
——– ———- ———- ———- —————-
45270E48 4 14 371 ACTIVE

查一下这个事务当前在执行什么SQL，SQL_ID是null（SQL_ID是Oracle10g以后才有的字段），也就是无从判断这个事务到底在做什么。

SQL> select sid,username,sql_id,sql_hash_value from v$session where taddr=’45270E48′;

SID USERNAME SQL_ID SQL_HASH_VALUE
———- —————————— ————- ————–
132 SCOTT 0

其实后来发现虽然SQL_ID没有值，但是PREV_SQL_ID还是有值，并且通过这个值从v$sql中就可以查到刚才这个事务做了什么。
其实SQL是：DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
恩，没错，从PLAN_TABLE中删除记录，这是一条recursive sql。

当时并没有想到可以去查PREV_SQL_ID值（因为这个字段中查出来的SQL往往都是没太大意义的递归SQL），但是从username = scott上我个人可以判断基本上是因为我做了SQL的trace，因为当时我只登陆了scott用户，只运行过查看SQL执行计划的autotrace，那么因为autotrace功能其实就是使用了PLAN_TABLE表来存储中间结果，在显示完执行计划以后再删除相应记录。

所以如果Oracle在显示完执行计划，并且删除了PLAN_TABLE的相应记录以后，并没有做commit，那么就会有一个active transaction存在。但是这仅仅是一个猜测，如何验证呢？

可以想到的，是dump undo block，来查看里面到底存了什么。

SQL> SELECT * FROM V$ROLLNAME WHERE USN = 4;

USN NAME
———- ——————————
4 _SYSSMU4_1195301203$

–仅仅是为了查看undo block的dump，不需要dump出undo heander，这里只是给出语法
SQL> ALTER SYSTEM DUMP UNDO HEADER “_SYSSMU4_1195301203$”;

System altered

SQL> ALTER SYSTEM DUMP UNDO BLOCK “_SYSSMU4_1195301203$” XID 4 14 371;

System altered

然后查看trace文件。找到如下这部分

* Rec #0x3 slt: 0x0e objn: 18002(0x00004652) objd: 18002 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x02

其中objn就是这个undo record上记录的相关对象ID，从dba_objects视图中就可以查到到底是什么了。

SQL> select owner,object_name,object_type from dba_objects where object_id=18002;

OWNER OBJECT_NAME OBJECT_TYPE
—————————— —————————— ——————-
SCOTT PLAN_TABLE TABLE

确实是SCOTTPLAN_TABLE，至此已经可以验证确实是因为set autotrace并且显示了执行计划之后产生了active transaction。

文章到这里其实就可以结束了，但是记得前面x$ktuxe基表中显示这个transaction需要2个undo block，那么下面就继续来看看是不是2个undo block以及为什么需要2个undo block。

上面这两个问题很简单。

1. 在trace文件中我们可以看到确实dump出了两个undo block，即使不去仔细看trace，从trace文件的最后总结也可以知道是2个。

Total undo blocks scanned = 2
Total undo records scanned = 8
Total undo blocks dumped = 2
Total undo records dumped = 8

2. 为什么是2个？因为1个undo block放不下delete PLAN_TABLE之后的记录前镜像。