潛水珽的異想世界

程式如下:

col username format a10

col terminal format a10

col kill(PID,SERIAL_ID) format a20

col PID format a10

col tab format a30

col request format a13

set pagesize 100

set line 400

select  nvl(S.USERNAME,'Internal') username,

        nvl(S.TERMINAL,'None') terminal,

        L.SID||','||S.SERIAL# as "kill(SID,SERIAL_ID)",

        L.SID SID,

        L.BLOCK BLOCK,

        U1.NAME||'.'||substr(T1.NAME,1,20) tab,

        decode(L.LMODE,1,'No Lock',

                2,'Row Share',

                3,'Row Exclusive',

                4,'Share',

                5,'Share Row Exclusive',

                6,'Exclusive',null) lmode,

        decode(L.REQUEST,1,'No Lock',

                2,'Row Share',

        3,'Row Exclusive',

        4,'Share',

        5,'Share Row Exclusive',

        6,'Exclusive',null) request

from    V$LOCK L,

    V$SESSION S,

    SYS.USER$ U1,

    SYS.OBJ$ T1

where  L.BLOCK=1

and    L.SID = S.SID

and    T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)

and    U1.USER# = T1.OWNER#

and    S.TYPE != 'BACKGROUND'

order by 1,2,5;

ALTER SYSTEM KILL SESSION '&SID,&serial' IMMEDIATE;

測試的方式如下:

1. 分別使用3個獨立session(session_A、session_B、session_C)。

2. session_A:

SQL>LOCK TABLE z_test IN EXCLUSIVE MODE;

3. session_B:

SQL>INSERT INTO Z_TEST VALUES(‘A’,’B’);

這個時候發生SESSION被BLOCK。

4. 開啟第三個session_C,執行以下:

$sqlplus system/password@kitty

SQL>@"/oracle/ntuh_admin/spkill.sql"

USERNAME   TERMINAL   kill(SID,SERIAL_ID)

                               SID      BLOCK TAB                            LMO

DE               REQUEST

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

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

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

SYSTEM     SUBMARINE- 142,3442

                               142          1 SYSTEM.Z_TEST                  Exc

lusive

           NB

輸入 sid 的值:  142    <= 參考kill(SID,SERIAL_ID) 欄位

輸入 serial 的值:  3442    <= 參考 kill(SID,SERIAL_ID) 欄位

舊的    1: ALTER SYSTEM KILL SESSION '&SID,&serial' IMMEDIATE

新的    1: ALTER SYSTEM KILL SESSION '142,3442' IMMEDIATE

已更改系統.

這時發生dead lock的session已經被kill了。