Killing Oracle Sessions

Killing Oracle Sessions

Today i had killed session and i would like to share the details with you.

The important thing is here identify session. "DO NOT KILL WRONG SESSION!" 

Connecting to sqlplus as SYS admin

[oracle@ora ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun feb 5 19:26:28 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Check user

SQL> show user

USER is "SYS"

setting format

SQL> set linesize 100

SQL> column spid format A10

SQL> column username format A10

SQL> column program format A45

SQL> set pagesize 60

Now Selecting our session to be killed

SQL> Select

  2  x.inst_id,

  3  x.sid,

  4  x.serial#,

  5  y.spid,

  6  x.username,

  7  x.program

  8  From gv$session x     

  9  Join gv$process y ON y.addr = x.paddr AND y.inst_id = x.inst_id

 10  Where x.type != 'BACKGROUND';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM

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

         1         55         19 4616       VEYSI      sqlplus@ora.localdomain (TNS V1-V3)

         1          1         91 4920       SYS        sqlplus@ora.localdomain (TNS V1-V3)

         1         49         23 4923       VEY        sqlplus@ora.localdomain (TNS V1-V3)

         1         51         38 5375                  oracle@ora.localdomain (J000)

         1         47         37 5377                  oracle@ora.localdomain (J001)

Or the other select way

SQL> select inst_id,sid,serial# from gv$session where username='VEYSI';

   INST_ID        SID    SERIAL#

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

         1         55         19

Here we go the session information 

Now the simple command used to killing!

Unix commands

find SPID 

% ps -ef | grep ora

Kill by Unix Command 

% kill -9 spid

OR...

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

SQL> ALTER SYSTEM KILL SESSION '55,19,1';

However Oracle 11g change the killing command

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

SQL> ALTER SYSTEM KILL SESSION '55,19,@1';