Killing Oracle session

列出目前的 session

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

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       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';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1         30         15 3859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         23        287 3834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         40        387 4663                  oracle@oel5-11gr2.localdomain (J000)
         1         38        125 4665                  oracle@oel5-11gr2.localdomain (J001)

SQL>

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