Killing Oracle session

列出目前的 session

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN machine FORMAT A20
COLUMN program FORMAT A45

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

SQL>

   INST_ID        SID    SERIAL# SPID       USERNAME   MACHINE              PROGRAM
---------- ---------- ---------- ---------- ---------- -------------------- ---------------------------------------------
         1        903      38450 28384      WIN        tpemisep11           fglrun-bin@tpemisep11 (TNS V1-V3)
         1        816      64334 20994      WIN        tpemisep11           fglrun-bin@tpemisep11 (TNS V1-V3)
         1        801      47559 14335      WIN        tpemisep11           fglrun-bin@tpemisep11 (TNS V1-V3)
         1        836       6214 578        WIN        tpemisep11           fglrun-bin@tpemisep11 (TNS V1-V3)
         1        760      56680 30330      WIN        tpemisep11           fglrun-bin@tpemisep11 (TNS V1-V3)
         1        951      41565 32207      WIN        tpemisep11           fglrun-bin@tpemisep11 (TNS V1-V3)
 

• SPID = Linux 下的 PID
• SID = Oracle Session ID

Kill session

Kill 方式一：ALTER SYSTEM KILL SESSION

刪除這個 session 本身

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

如果是 RAC 環境，使用下述語法

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

Kill 方式二：ALTER SYSTEM DISCONNECT SESSION

等同於在作業系統下刪除指定的程序

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

• POST_TRANSACTION: 等待線上的交易完成後，才會執行刪除
• IMMEDIATE: 立即執行，而線上的未完成交易會被 roll back