How to Kill All Sessions of a Specific User in Oracle

Any time request may come like "Can you please kill all the sessions that is on ORAHOW database for user name Tiwary. 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.

Before killing session gather session information for that user from Oracle.

• Before killing check which sql being executed by that user.
• Check for any blocking locks, long running query etc.

Best Way to kill Blocking Sessions in Oracle

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


In RAC @ sign is now required to kill a session or when using an inst_id.
SQL>  alter system kill session '125,640,1';
 alter system kill session '125,640,1'
*
ERROR at line 1:
ORA-00026: missing or invalid session ID

After specifying @inst_id, it works

alter system kill session '125,640,@1';

System altered.

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


Best 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


Finally we have killed all the sessions for a special user connected to the oracle database. Now there is no active sessions for that user.