Category Archives: Oracle

Killing Oracle Sessions (ALTER SYSTEM KILL / DISCONNECT SESSION)

There are a number of ways to kill rogue sessions both within Oracle and externally.
If you use Oracle sql developer then follow steps 1 and 2 and for others please scroll down to see the alternative ways.

Step 1:

Steps 2:

—————————- Alternative Ways ———————-

 ALTER SYSTEM KILL SESSION 

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

The KILL SESSION command doesn’t actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases, the session will have a status of “marked for kill”. It will then be killed as soon as possible.

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

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

Identify the Session to be Killed
Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.

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

SELECT s.inst_id,
       s.sid,
       s.serial#,
       --s.sql_id,
       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>

For More : Killing Oracle Sessions