Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【oracle】解决无法kill的session

19 Sep 2019 » oracle

解决无法kill的session

场景:

查看锁住的sql语句:

SQL>select 'alter system kill session ''' || b.session_id || ','||c.serial#||''';'killString,
a.object_name,b.session_id,c.serial#,c.program,c.username,c.command,c.machine,c.lockwait from all_objects a, v$locked_object b, v$session c where a.object_id = b.object_id and c.sid = b.session_id;

查询结果如下:

KILLSTRING
--------------------------------------------------------------------------------------------------------------
OBJECT_NAME                    SESSION_ID    SERIAL# PROGRAM
------------------------------ ---------- ---------- ------------------------------------------------
USERNAME                          COMMAND MACHINE
------------------------------ ---------- ----------------------------------------------------------------
LOCKWAIT
----------------
alter system kill session '1163,30199';
T_PM_ACCTFI_ACCTT                    1163      30199 新一代ARPxxxx.exe
SYSTEM                                  2 LAPTOP-A3H727RT

执行alter system kill session '1163,30199';无法删除,如下:

SYS@oradb> alter system kill session '1163,30199';                       
alter system kill session '1163,30199'
*
 1 行出现错误:
ORA-00031: 标记要终止的会话

解决思路

找到linux的进程id

SYS@oradb> select spid, osuser, s.program
  2  from v$session s,v$process p
  3  where s.paddr=p.addr and s.sid=1163;

SPID                     OSUSER                         PROGRAM
------------------------ ------------------------------ ------------------------------------------------
29746                    hyy                            新一代ARPxxxx.exe

杀掉进程

[oracle@oradb ~]$ ps -ef |grep 29746
oracle   12475 12373  0 15:07 pts/0    00:00:00 grep 29746
oracle   29746     1  0 Sep17 ?        00:00:00 oracleoradb (LOCAL=NO)
[oracle@oradb ~]$ kill -9 29746