killing rac sessions

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';


The basic syntax for killing a session is shown below.


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



In a RAC environment, you optionally specify the INST_ID, shown when querying the

GV$SESSION view. This allows you to kill a session on different RAC node.


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


In addition to the syntax described above, you can add the IMMEDIATE clause.


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


DISCONNECT SESSION


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

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


The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation

states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram

suggests both clauses are optional, but in reality, one or both must be specified or you

receive an error.


SQL> alter system disconnect session '30,7';

alter system disconnect session '30,7'

*

ERROR at line 1:

ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword


The Windows Approach

To kill the session on the Windows operating system, first identify the session, then

substitute the relevant SID and SPID values into the following command issued from the

command line.


C:> orakill ORACLE_SID spid


The UNIX Approach

To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.


% kill spid

If after a few minutes the process hasn't stopped, terminate the session using the following.


% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using.


% ps -ef
grep ora


----------------Session Waits--------------------

SQL> select event, seconds_in_wait, sid from v$session_wait where sid in (10,14);