oracle会话不释放,kill session之后，会话一直无法释放的巧妙解决办法（lsof）

我的数据库版本：

SQL> select * from v$version ;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for HPUX: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

下面这些非法连接，需要被kill掉：

SQL> select 'alter system kill session '''||sid||','||serial#||'''; ',client_info,program,status

2  from v$session where client_info like '132.224.36.10';

'ALTERSYSTEMKILLSESSION'''||SI             CLIENT_INFO    PROGRAM                    STATUS

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

alter system kill session '1365,1937';     132.224.36.10  (TNS V1-V3)  INACTIVE

alter system kill session '1367,17258';    132.224.36.10  (TNS V1-V3)  INACTIVE

alter system kill session '1383,7729';     132.224.36.10  (TNS V1-V3)  INACTIVE

alter system kill session '1402,3818';     132.224.36.10  (TNS V1-V3)  INACTIVE

alter system kill session '1638,4784';     132.224.36.10  (TNS V1-V3)  INACTIVE

alter system kill session '1982,7247';     132.224.36.10  (TNS V1-V3)  INACTIVE

alter system kill session '2004,4734';     132.224.36.10  (TNS V1-V3)  INACTIVE

为避免出现kill session之后，会话长期存在，先保存一下spid，也方便与后面的方法做对比

一般在10g之后，不会出现session被kill之后，在v$process中无法找到spid的情况，但实际上，我又反复碰到了

SQL> select spid from v$process where addr in

2  (select paddr from v$session where client_info='132.224.36.10') ;

SPID

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

15843

16581

13466

16862

8224

7332

12161

7 rows selected

利用上面的脚本杀掉下面的会话：

alter system kill session '1365,1937';

alter system kill session '1367,17258';

alter system kill session '1383,7729';

alter system kill session '1402,3818';

alter system kill session '1638,4784';

alter system kill session '1982,7247';

alter system kill session '2004,4734';

结果KILLED状态的会话一直不释放，查询v$process也找不到对应的spid

无法通过在server端kill spid来快速释放连接了

SQL> select sid,serial#,client_info,status from v$session where status='KILLED';

SID    SERIAL# CLIENT_INFO                    STATUS

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

1365       1937 132.224.36.10                  KILLED

1367      17258 132.224.36.10                  KILLED

1383       7729 132.224.36.10                  KILLED

1402       3818 132.224.36.10                  KILLED

1638       4784 132.224.36.10                  KILLED

1982       7247 132.224.36.10                  KILLED

2004       4734 132.224.36.10                  KILLED

7 rows selected

SQL> select spid from v$process where addr in

2  (select paddr from v$session where status='KILLED') ;

SPID

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

SQL>

SQL> select paddr from v$session where status='KILLED' ;

PADDR

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

C000000CFD7023D8

C000000CFD7023D8

C000000CFD7023D8

C000000CFD7023D8

C000000CFD7023D8

C000000CFD7023D8

C000000CFD7023D8

7 rows selected

因为知道IP地址等信息，我们可以通过lsof工具，根据netstat的端口情况来判定spid

下面给出的就是这个方法：

crmdb2:/oracle>netstat -an | grep tcp | grep 132.224.36.10 |grep ESTABLISHED

tcp        0      0  130.34.3.10.1521       132.224.36.10.55190     ESTABLISHED

tcp        0      0  130.34.3.10.1521       132.224.36.10.55040     ESTABLISHED

tcp        0      0  130.34.3.10.1521       132.224.36.10.55004     ESTABLISHED

tcp        0      0  130.34.3.10.1521       132.224.36.10.55163     ESTABLISHED

tcp        0      0  130.34.3.10.1521       132.224.36.10.55065     ESTABLISHED

tcp        0      0  130.34.3.10.1521       132.224.36.10.54982     ESTABLISHED

tcp        0      0  130.34.3.10.1521       132.224.36.10.55227     ESTABLISHED

crmdb2:/oracle>/usr/local/bin/lsof -i tcp:54982 |grep 132.224.36.10

oracle  16862 oracle   22u  IPv4 0xe000000b88c31a00  0t14832  TCP crmdb2_vip:1521->132.224.36.10:54982 (ESTABLISHED)