在搞做测试的时候，用INSERT INTO SELECT的方式向数据库插入44G(近1亿条记录)的数据时，忘了用并行，CTRL+C不能及时中断，于是在操作系统上KILL了这个进程。

[oracle@dm0101 ~]$ ps -ef | grep sqlplus
oracle    1461   858  0 15:01 pts/0    00:00:00 rlwrap sqlplus xxx/xxx
oracle    1462  1461  0 15:01 pts/2    00:00:00 sqlplus                    
oracle   23015 14947  0 15:17 pts/5    00:00:00 grep sqlplus
[oracle@dm0101 ~]$ kill -9 1461

然后再TRUNCATE这张表的时候，报ORA-00054错误。

SQL> truncate table ENMO_ASICDA;
truncate table ENMO_ASICDA
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

猜想这张表是在回滚，还没回滚完，被锁住了。

SQL> select sid,serial#,status,EVENT,WAIT_CLASS#,WAIT_CLASS,WAIT_CLASS_ID from v$session where username='HONGYEDBA';

       SID    SERIAL# STATUS   EVENT                          WAIT_CLASS# WAIT_CLASS WAIT_CLASS_ID
---------- ---------- -------- ------------------------------ ----------- ---------- -------------
      1165      21759 ACTIVE   SQL*Net message to client                7 Network       2000153315
      1822      36017 ACTIVE   cell single block physical read          8 User I/O      1740759767

SQL> select sid from v$mystat where rownum<2;

       SID
----------
      1165

尝试杀掉1822这个会话，遇到ORA-00031错误。

SQL> alter system kill session '1822,36017';
alter system kill session '1822,36017'
*
ERROR at line 1:
ORA-00031: session marked for kill

可以用OERR命令来看下ORA-00031错误是什么情况。

[oracle@dm0101 ~]$ oerr ora 00031
00031, 00000, "session marked for kill"
// *Cause:  The session specified in an ALTER SYSTEM KILL SESSION command
//          cannot be killed immediately (because it is rolling back or blocked
//          on a network operation), but it has been marked for kill.  This
//          means it will be killed as soon as possible after its current
//          uninterruptable operation is done.
// *Action: No action is required for the session to be killed, but further
//          executions of the ALTER SYSTEM KILL SESSION command on this session
//          may cause the session to be killed sooner.

这是由于这张表(事务)还没回滚完，不能做TRUNCATE操作。

SQL> select sid,serial#,status,EVENT,WAIT_CLASS#,WAIT_CLASS,WAIT_CLASS_ID from v$session where username='HONGYEDBA';

       SID    SERIAL# STATUS   EVENT                          WAIT_CLASS# WAIT_CLASS WAIT_CLASS_ID
---------- ---------- -------- ------------------------------ ----------- ---------- -------------
      1165      21759 ACTIVE   SQL*Net message to client                7 Network       2000153315
      1822      36017 KILLED   cell single block physical read          8 User I/O      1740759767

即使这个会话显示的是KILLED状态，也不可以TRUNCATE操作。

SQL> truncate table ENMO_ASICDA;
truncate table ENMO_ASICDA
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

回滚完之后，在V$SESSION视图中查询不到这个会话，TRUNCATE操作可以进行。

SQL> select sid,serial#,status,EVENT,WAIT_CLASS#,WAIT_CLASS,WAIT_CLASS_ID from v$session where username='HONGYEDBA';

       SID    SERIAL# STATUS   EVENT                          WAIT_CLASS# WAIT_CLASS WAIT_CLASS_ID
---------- ---------- -------- ------------------------------ ----------- ---------- -------------
      1165      21759 ACTIVE   SQL*Net message to client                7 Network       2000153315

SQL> truncate table ENMO_ASICDA;

Table truncated.

本文固定链接: https://www.dbdream.com.cn/2013/11/ora-00054-ora-00031%e9%94%99%e8%af%af/ | 信春哥，系统稳，闭眼上线不回滚！