ORACLE DML LOCK时找出对应SESSION正在执行的SQL语句（ORA-12841，无法变更事务处理中的会话并行 DML 状态）

问题描述：

SQL> altersession enable parallel dml;

ERROR:

ORA-12841: 无法变更事务处理中的会话并行 DML 状态

 

解决:

1、查找dml lock的锁的sid

SQL> select session_id, owner,name,MODE_HELD fromsys.dba_dml_locks;

 

SESSION_ID OWNER                                                       NAME                                                MODE_HELD

------------------------------------------------------------------------------------------------------------------------------------------------------------

       373SCOTT                                                        T_DRIVEWAYDATA                                       Row-X(SX)

       245SCOTT                                                       T_DRIVEWAYDATA                                       Row-X(SX)

       373SYS                                                          SEQ$                                                Row-X (SX)

       124SYS                                                         PLAN_TABLE$                                          Row-X(SX)

       126 SCOTT                                                       T_DRIVEWAYDATA                                       Row-X(SX)

       363SCOTT                                                       T_DRIVEWAYDATA                                       Row-X (SX)

 

已选择6行。

2、依据上文找到sid，查询锁住的sql语句

SQL> selectb.sql_text from v$session a,v$sqlarea b where a.sql_hash_value=b.hash_value anda.sid='126';

 

SQL_TEXT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

----------------

SELECT T_DRIVEWAYDATA_SEQUENCE.NEXTVAL FROM DUAL

 

原来此时SID为126的session正在对表T_DRIVEWAYDATA进行INSERT操作，而表中有一个自增的主键。