当前位置: 首页 > Exadata, Oracle, oracle 11g > 正文

ORA-00054 ORA-00031错误

在搞做测试的时候,用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.

 

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

该日志由 dbdream 于2013年11月14日发表在 Exadata, Oracle, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: ORA-00054 ORA-00031错误 | 信春哥,系统稳,闭眼上线不回滚!
关键字: ,

ORA-00054 ORA-00031错误:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter