Oracle 彻底 kill session

kill session 是DBA经常碰到的事情之一。如果kill掉了不该kill的session，则具有破坏性，因此尽可能的避免这样的错误发生。同时也应当注意，如果kill的session属于Oracle后台进程，则容易导致数据库实例宕机。通常情况下，并不需要从操作系统级别杀掉Oracle会话进程，但并非总是如此，下面的描述中给出了在Oracle级别杀掉会话以及操作系统级别杀掉进程。

 

一、获得需要kill session的信息(使用V$SESSION和GV$SESSION视图)

 

   set linesize 150

   col spid for a10

   col username for a10

   col program for a40

 

 SELECT  s.inst_id,
        s.sid,
        s.serial#,
        p.spid,
        s.username,
        s.program,
        s.paddr,
        s.STATUS
 FROM  gv$session s JOIN gv$process p
 ON p.addr = s.paddr
 AND p.inst_id = s.inst_id
 WHERE s.type !='BACKGROUND';

 

    INST_ID       SID   SERIAL# SPID      USERNAME  PROGRAM                                      PADDR   STATUS

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

          1       146        2327573     TEST      sqlplus@oracle10g (TNS V1-V3)                4C621950INACTIVE

          1       160        1727610     SYS       sqlplus@oracle10g (TNS V1-V3)                4C624174 ACTIVE

          1       144        4227641     SCOTT     sqlplus@oracle10g (TNS V1-V3)                4C624730INACTIVE

        

二、使用alter system kill session 命令实现

 语法：

 

     

     SQL> alter system kill session 'sid,serial#';

 

     SQL> alter system kill session 'sid,serial#'immediate

    

   对于RAC环境下的killsession,需要搞清楚需要kill的session位于哪个节点，可以查询GV$SESSION视图获得。

   killsession的时候仅仅是将会话杀掉。在有些时候，由于较大的事务或需要运行较长的SQL语句将导致需要kill的session并不能立即杀掉。对 于这种情况将收到"marked for kill"提示(如下)，一旦会话当前事务或操作完成，该会话被立即杀掉。

   

   alter system kill session '4730,39171'

   *

   ERRORatline1:

   ORA-00031:session marked for kill

 

 

 在下面的操作中将杀掉会话146，144

alter system kill session'146,23';

Systemaltered.

alter system kill session'144,42';

Systemaltered.

   

   select inst_id,saddr,sid,serial#,paddr,username,status,program from gv$session where username is not null;

   

      INST_ID SADDR          SID   SERIAL# PADDR   USERNAME  STATUS  PROGRAM

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

            14C70BF04       144        424C6545A0SCOTT     KILLED  sqlplus@oracle10g (TNS V1-V3)

            14C70E6B4       146        234C6545A0TEST      KILLED  sqlplus@oracle10g (TNS V1-V3)

            14C71FC84       160        174C624174SYS       ACTIVE  sqlplus@oracle10g (TNS V1-V3)

                 

    注意：在查询中可以看到被杀掉的会话的PADDR地址发生了变化，参照查询结果中的红色字体。如果多个session被kill掉，则多个session的PADDR

    被改为相同的进程地址。

 

 通过下面的语句来找回被kill掉的ADDR先前的地址

 

   SELECTs.username,s.status,

   x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,

   decode(bitand(x.ksuprflg,2),0,null,1)

   FROMx$ksupr x,v$session s

   WHEREs.paddr(+)=x.addr

   andbitand(ksspaflg,1)!=0;      

   

   USERNAME  STATUS  ADDR      KSLLAPSC  KSLLAPSN KSLLASPO      KSLLID1RKSD

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

              ACTIVE  4C623BB8        99         427468              275EV1

              ACTIVE  4C623040         9        2427444                0   1

              ACTIVE  4C622A84       101         427480              274EV1

              ACTIVE  4C6224C8         1        4827450                0   1

              ACTIVE  4C621F0C         1        4827450                0   1

              ACTIVE  4C6235FC         2         427468                0   1

   SYS       ACTIVE  4C624174         2        1527442                0

              ACTIVE  4C62081C         1        4827440                0   1

              ACTIVE  4C621394         1        4827440                0   1

              ACTIVE  4C620DD8        11        2427476                0   1

              ACTIVE  4C61F6E8        15         427610                0   1

              ACTIVE  4C620260       222        2427450                0   1

              ACTIVE  4C61FCA4         7        2527573                0   1

              ACTIVE  4C61F12C         6        2527573                0   1

              ACTIVE  4C61EB70         4        2427458                0   1

              ACTIVE  4C61E5B4         1        4827440                0   1

              ACTIVE  4C61DFF8         2        2427444                0   1

                       4C624730         0         0                      0

                       4C621950         0         0                      0

                       4C61DA3C         0         0                      0

                   

 

 或者根据下面的语句来获得发生变化的addr

 

SQL> select p.addr from v$process p where pid <>1 minus

     select s.paddr from v$session s;

   

   ADDR

   --------

   4C621950

   4C624730                   

 

三、在操作系统级别杀掉会话

 寻找会话对应的操作系统的进程ID

select SPID from v$process where ADDR in('4C621950','4C624730') ;       

   

   SPID

   ----------

   27573

   27641

   

 使用kill命令来杀掉操作系统级别进程ID

 

  kill session -927573

  kill session -927641

 

四、获得当前会话的SID

 SQL> select userenv('sid')from dual;

 

 USERENV('SID')

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

            627

 

五、多个会话需要kill的处理办法

 1.根据给定的SID(用户名)查找需要杀掉会话的信息，包括位于哪一个实例

   set line size 160
   col program format a35
   col username format a18

 

    select inst_id,saddr,sid,serial#,paddr,username,status,program from gv$session
   where sidin('2731','2734','2720','2678','2685')
   and username='CTICUST'
   order by inst_id;

   

      INST_ID SADDR                  SID   SERIAL# PADDR           USERNAME          STATUS  PROGRAM

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

            100000003DAF8F870      2678      826500000003DBC6CA08 MSS4USR           INACTIVE JDBC Thin Client

            100000003DAF98E48      2685        8300000003DBC08510 MSS4USR           ACTIVE  JDBC Thin Client

            100000003DAFC7B80      2720         500000003DBBEDA20 MSS4USR           INACTIVE JDBC Thin Client

            100000003DAFD66F8      2731         300000003DBBE9AE0SYS               ACTIVE racgimon@svdg0028(TNS V1-V3)

            100000003DAFDA730      2734        1500000003DBBEC268 MSS4USR           INACTIVE JDBC Thin Client

            200000003DAFD66F8      2731         100000003DBBE92F8                   ACTIVE  oracle@svdg0029 (ARC0)

   

   上面的查询中有一个SID为2731的位于节点2上。

   也可以通过下面的方式来获得RAC的节点信息，便于确定需要kill的session究竟位于哪一个节点。

   

        set linesize 150
       col HOST_NAME format a25

       SQL>select INSTANCE_NUMBER,INSTANCE_NAME,HOST_NAME,VERSION,STATUS from gv$instance order by1;

 

     INSTANCE_NUMBER INSTANCE_NAME   HOST_NAME                VERSION          STATUS

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

                   1O02WMT1A        svd0051                 10.2.0.4.0       OPEN

                   2O02WMT1B        svd0052                 10.2.0.4.0       OPEN

                   3O02WMT1C        svd0053                 10.2.0.4.0       OPEN

                   

 2.使用下面查询来生成killsession的语句

   select 'alter system kill session '''||sid||','||SERIAL# ||''''||';' from gv$session
  where sid in('2731','2734','2720','2678','2685')
  order by inst_id;

     

   获得下列kill session的语句，根据要求由于此次需要杀掉的session全部位于节点1，因此登录到节点节点1执行下面的语句  

        

   altersystemkillsession'2678,8265';

   

   altersystemkillsession'2685,83';

   

   altersystemkillsession'2720,5'; 

   

   altersystemkillsession'2731,3';

    

   altersystemkillsession'2734,15';

    

   altersystemkillsession'2731,1';   --此条命令不需要执行，该session位于节点2。