同事来问,在他机器上执行了很长时间都没有结束的sql,用
Alter system kill session 'SID,SERIAL#'也没停掉。
问怎么办,想了想来个狠点的。
执行如下sql;
SQL> select s.INST_ID,
2 s.USERNAME,
3 s.OSUSER,
4 s.PADDR,
5 p.ADDR,
6 s.SID,
7 s.SERIAL#,
8 p.spid,
9 s.ACTION,
10 s.SQL_ID,
11 s.LOGON_TIME,
12 s.EVENT,
13 ' kill -9 '|| p.SPID
14 from gv$session s, gv$process p
15 where s.paddr = p.addr
16 AND S.USERNAME = 'CAREPORT'
17 AND S.OSUSER = 'taoyan';
INST_ID USERNAME OSUSER PADDR ADDR SID SERIAL# SPID ACTION SQL_ID LOGON_TIME EVENT 'KILL-9'||P.SPID
------- ---------- ------- ---------------- ---------------- ---- ------- ------- -------------------------------- ------------- ----------- ---------------------------- ---------------------
2 CAREPORT taoyan 000000024E2AA588 000000024E2AA588 584 22203 5863 Main session 2010-10-13 SQL*Net message from client kill -9 5863
2 CAREPORT taoyan 000000024E2AA588 000000024E2AA588 584 22203 20488 Main session 2010-10-13 SQL*Net message from client kill -9 20488
2 CAREPORT taoyan 000000024E2AA588 000000024E2AA588 584 22203 25009 Main session 2010-10-13 SQL*Net message from client kill -9 25009
3 CAREPORT taoyan 000000024C26D818 000000024C26D818 623 2730 26457 Test Window - Script for procedu cm4y8f0w2wndr 2010-10-13 gc cr multi block request kill -9 26457
然后登录INST_ID为3的操作系统上执行
kill -9 26457
此操作有一定的小风险,生产环境上需要谨慎执行。