How to Kill All Sessions of a Specific User in Oracle

April 24, 2015
Sometime you need to Kill Oracle Session to avoid any performance issues. There are chances that some session cause blocking locks on the table and you need to kill session in Oracle to release the locks.
Gather session information before killing the session:
  • Before killing check which sql being executed by that user.
  • Check for any blocking locks, long running query etc.

https://orahow.com/4-best-ways-to-find-blocking-sessions-in-oracle-11g/

  • Find inst_id, sid, serial#, machine, sql_id for that user.

To Find inst_id, sid, serial# for a user
select inst_id, sid, serial#, machine, username status, sql_id from gv$session where username=’Tiwary’;

To get more detailed information for a particular user
set lines 1234 pages 9999
col inst_id for a10
col serial# for a10
col machine for a30
col username for a10
col event for a20
col blocking_session for 999999
col blocking_instance for 999999
col status for a10
col INST_ID for 9999
col SERIAL# for 999999

select inst_id,sid,serial#,machine,username,event,blocking_session,blocking_instance,status,sql_id from gv$session where username=’TIWARY’;

Killing Session in Oracle

SQL> alter system kill session ‘sid, serial#’ immediate;

To Kill Session in RAC Environment:

SQL> alter system kill session ‘sid, serial#, @inst_id’;

NOTE: If you don’t want to put inst_id, then goto the instance from where user is connected and then kill it.

Alternative way to kill a session is using ALTER SYSTEM DISCONNECT SESSION command.
ALTER SYSTEM DISCONNECT SESSION ‘SID,SERIAL#’ immediate;
ALTER SYSTEM DISCONNECT SESSION ‘125,640’immediate;

Finally check once to verify it.
select inst_id, sid, serial#, machine, username status, sql_id from gv$session where username=’Tiwary’;

no rows selected

Conclusion: In this post we saw that how to kill session in Oracle using alter system kill session command.

Must Read: