ORACLE中的KILLED SESSION

在Oracle中終止會話
alter system kill session 'sid,serial#';
在用此命令終止會話後，其狀態為killed，資源並沒有回收.
在被使用kill終止的程式paddr發生了變化，不能通過v$session和v$process檢視的關聯來找到spid。參考：http://www.eygle.com/faq/Kill_Session.htm

在最近的一個案例中，有一個程式dllhost.exe，是由於COM+呼叫時未能中斷，
利用alter system kill session 'sid,serial#'，其狀態為KILLED，

SQL> col program for a20
SQL> select sid,serial#,program,status from v$session  where program ='dllhost.e
xe' and status = 'INACTIVE';

       SID    SERIAL# PROGRAM              STATUS
---------- ---------- -------------------- --------
        24        469 dllhost.exe          INACTIVE
        37        726 dllhost.exe          INACTIVE
        44       1098 dllhost.exe          INACTIVE
        56        526 dllhost.exe          INACTIVE
        61        476 dllhost.exe          INACTIVE
        79        294 dllhost.exe          INACTIVE
        80        577 dllhost.exe          INACTIVE
        90       1552 dllhost.exe          INACTIVE
        92        732 dllhost.exe          INACTIVE
        94        757 dllhost.exe          INACTIVE
        96       1095 dllhost.exe          INACTIVE

       SID    SERIAL# PROGRAM              STATUS
---------- ---------- -------------------- --------
        97        393 dllhost.exe          INACTIVE
        98         62 dllhost.exe          INACTIVE
       102       1487 dllhost.exe          INACTIVE
       105        682 dllhost.exe          INACTIVE
       106        521 dllhost.exe          INACTIVE
       107        852 dllhost.exe          INACTIVE
       108        730 dllhost.exe          INACTIVE
       109        976 dllhost.exe          INACTIVE
       111        137 dllhost.exe          INACTIVE
       116       1030 dllhost.exe          INACTIVE
       118       3183 dllhost.exe          INACTIVE

       SID    SERIAL# PROGRAM              STATUS
---------- ---------- -------------------- --------
       120        432 dllhost.exe          INACTIVE
       121        366 dllhost.exe          INACTIVE
       122       1033 dllhost.exe          INACTIVE
       124        377 dllhost.exe          INACTIVE
       125       2074 dllhost.exe          INACTIVE
       126        704 dllhost.exe          INACTIVE
       131        137 dllhost.exe          INACTIVE
       133       1073 dllhost.exe          INACTIVE
       135        963 dllhost.exe          INACTIVE
       137        401 dllhost.exe          INACTIVE
       138        488 dllhost.exe          INACTIVE

       SID    SERIAL# PROGRAM              STATUS
---------- ---------- -------------------- --------
       139        433 dllhost.exe          INACTIVE
       141        667 dllhost.exe          INACTIVE
       142       1302 dllhost.exe          INACTIVE
       147       1029 dllhost.exe          INACTIVE
       149       1255 dllhost.exe          INACTIVE
       150       2498 dllhost.exe          INACTIVE
       152        390 dllhost.exe          INACTIVE
       154        493 dllhost.exe          INACTIVE
       155        505 dllhost.exe          INACTIVE
       156        257 dllhost.exe          INACTIVE
       159       3371 dllhost.exe          INACTIVE

       SID    SERIAL# PROGRAM              STATUS
---------- ---------- -------------------- --------
       162        704 dllhost.exe          INACTIVE
       163        668 dllhost.exe          INACTIVE
       164       1459 dllhost.exe          INACTIVE
       165        469 dllhost.exe          INACTIVE
       168        620 dllhost.exe          INACTIVE
       169       1437 dllhost.exe          INACTIVE
       170         57 dllhost.exe          INACTIVE
       171        638 dllhost.exe          INACTIVE
       173        935 dllhost.exe          INACTIVE
       176        389 dllhost.exe          INACTIVE
       177        359 dllhost.exe          INACTIVE

       SID    SERIAL# PROGRAM              STATUS
---------- ---------- -------------------- --------
       178       2623 dllhost.exe          INACTIVE
       179        947 dllhost.exe          INACTIVE
       180       1255 dllhost.exe          INACTIVE
       181       1342 dllhost.exe          INACTIVE
       184       4277 dllhost.exe          INACTIVE

60 rows selected.

SQL> select count(*) from v$session where program ='dllhost.exe' and status = 'INACTIVE';

  COUNT(*)
----------
        60

而後，其KILLED的程式也自動地回收資源
SQL> select count(*) from v$session where status='KILLED';

  COUNT(*)
----------
        12

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

       SID    SERIAL# PROGRAM              STATUS
---------- ---------- -------------------- --------
        81        371 dllhost.exe          KILLED
        84        579 dllhost.exe          KILLED
        85        547 dllhost.exe          KILLED
        91        599 dllhost.exe          KILLED
       100       1147 dllhost.exe          KILLED
       103       2018 dllhost.exe          KILLED
       113       1578 dllhost.exe          KILLED
       117       1190 dllhost.exe          KILLED
       148        488 dllhost.exe          KILLED
       174        675 dllhost.exe          KILLED
       182       1055 dllhost.exe          KILLED

       SID    SERIAL# PROGRAM              STATUS
---------- ---------- -------------------- --------
       186       1461 dllhost.exe          KILLED

12 rows selected.

檢視未能關聯的paddr
SQL> select p.addr from v$process p where pid <> 1
  2  minus
  3  select s.paddr from v$session s;

ADDR
--------
1E53E22C
1E53E5EC
1E5461AC
1E54746C
1E54782C
1E54926C
1E54A52C
1E54E4EC
1E54F02C
1E54F7AC

查詢未能關聯的spid
select p.spid, p.program, p.addr from v$process p where pid <> 1
and addr not in (select paddr from v$session);

winodows下kill session
orakill sid,spid

linux下kill session
kill -9 spid

問題：COM+怎麼自動終止會話？

kill oralce session參考：http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9932141/viewspace-665050/，如需轉載，請註明出處，否則將追究法律責任。