Oracle 查询未提交事务

在使用 Oracle 数据库时，我们偶尔会遇到修改表结构（比如添加字段）无响应的情况，引起这种现象的一个可能原因是这个表上有未提交的DML事务。

那怎么查看是否有未提交的事务呢？又怎样定位到具体语句呢？

现通过示例进行演示

对已存在的表 table1 进行 delete 操作（暂不提交）：

delete from table1 where ID=2;

查询当前 session 的 SID：

select userenv('sid') from dual;

结果如下：

现通过如下所示的 SQL 语句 1，可查询到未提交的 DML 语句：

--SQL 语句 1：查询未提交事务 DML 语句
SELECT  S.SID
       ,S.SERIAL#
       ,S.USERNAME
       ,S.OSUSER 
       ,S.PROGRAM 
       ,S.EVENT
       ,TO_CHAR(S.LOGON_TIME,'YYYY-MM-DD HH24:MI:SS') 
       ,TO_CHAR(T.START_DATE,'YYYY-MM-DD HH24:MI:SS') 
       ,S.LAST_CALL_ET 
       ,S.BLOCKING_SESSION   
       ,S.STATUS
       ,( 
              SELECT Q.SQL_TEXT 
              FROM    V$SQL Q 
              WHERE  Q.LAST_ACTIVE_TIME=T.START_DATE 
              AND    ROWNUM<=1) AS SQL_TEXT   
FROM   V$SESSION S, 
       V$TRANSACTION T  
WHERE  S.SADDR = T.SES_ADDR;

结果如下：

若想 kill 该 DML 语句，可执行：

ALTER SYSTEM KILL SESSION 'SID,SERIAL#'; //SID,SERIAL# 替换成上面查到的值

即：

alter system kill session '67,20514'; 

注意：PRD 环境不要轻易 kill DML语句，需谨慎处理！

我们再做进一步测试

先回滚：

然后重新开始事务：

delete from table1 where ID=3;
insert into table2 values (5,'NEW');

查询出当前 session 的 SID如下：

SQL 语句 1 查询出未提交 DML 语句如下：

说明：上述 SQL 语句 1 只查出未提交事务中第一条 DML 语句

再次回滚，继续测试

新建事务：

insert into table2 values (5,'NEW');

delete from table1 where ID=3;

insert into table2 values (6,'OLD');

查询出当前 session 的 SID如下：

SQL 语句 1 查询出未提交 DML 语句如下：

结论： SQL 语句 1 不是总能查询出未提交的 DML 语句

补充
如果只是想查询出未提交 DML 语句的 SID 和 SERIAL#，也可通过如下语句查询：

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

若想 kill 该 DML 语句，执行：

ALTER SYSTEM KILL SESSION 'SID,SERIAL#'; //SID,SERIAL# 替换成上面查到的值