处理ORA-00031: session marked for kill报错

问题出现前做的操作

delete P2SP_USER_INFOS where LOG_TIME>=to_date('2011-09-24','YYYY-MM-DD') and LOG_TIME<=to_date('2011-09-27','YYYY-MM-DD')

执行后很长时间没有结果，打算结束，但是无法结束。
只能用kill session；
先用下面的语句查找锁表信息

select sid,serial#,username,SCHEMANAME,osuser,MACHINE,terminal,PROGRAM,owner,object_name,object_type,o.object_id

from dba_objects o,v$locked_object l,v$session s

where o.object_id=l.object_id and s.sid=l.session_id;

SID SERIAL# USERNAME SCHEMANAME OSUSER MACHINE TERMINAL PROGRAM OWNER OBJECT_NAME OBJECT_TYP OBJECT_ID

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

114 114 DOWNLOAD DOWNLOAD root xxx unknown xx Developer DOWNLOAD P2SP_USER_INFOS TABLE 89679

然后kill session。发现报错，不能杀死session

SQL> alter system kill session '114,30486';

alter system kill session '114,30486'

*

ERROR at line 1:

ORA-00031: session marked for kill

找出sid对应的sid

select spid, osuser, s.program

from v$session s,v$process p

where s.paddr=p.addr and s.sid=114（114是上面的sid）



SPID OSUSER PROGRAM

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

21787 root SQL Developer

杀死对应进程就解决了这个问题。

kill -9 21787（即查询出的spid）