Killing Blocking sessions

Query user session

Check blocking sessions

SELECT SID,SERIAL#,USERNAME,BLOCKING_INSTANCE,BLOCKING_SESSION,BLOCKING_SESSION_STATUS FROM V$SESSION WHERE BLOCKING_SESSION IS NOT NULL

on RAC

SELECT B.INSTANCE_NAME,A.inst_id,A.SID,A.SERIAL#,A.USERNAME,A.BLOCKING_INSTANCE,A.FINAL_BLOCKING_SESSION,A.BLOCKING_SESSION,A.LOCKWAIT,A.STATUS,A.PROGRAM,A.MODULE,A.BLOCKING_SESSION_STATUS,A.EVENT,A.STATE FROM GV$INSTANCE B, GV$SESSION A
WHERE A.INST_ID=B.INST_ID AND BLOCKING_SESSION IS NOT NULL 

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='USERNAME'

Example

SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='FARHAT'

SID                  SERIAL#

1076                 23335

Now kill session using SID and SERIAL#

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Example

ALTER SYSTEM KILL SESSION '1076,23335';




Query using specific username

SELECT SID, SERIAL#, USERNAME, BLOCKING_SESSION, LOCKWAIT, STATUS, PROGRAM, MODULE, BLOCKING_SESSION_STATUS,EVENT,STATE FROM V$SESSION WHERE USERNAME LIKE 'FAR%'

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

ON RAC

SELECT inst_id, SID, SERIAL#, USERNAME, BLOCKING_SESSION, LOCKWAIT, STATUS, PROGRAM,MODULE,BLOCKING_SESSION_STATUS,EVENT,STATE FROM GV$SESSION

WHERE USERNAME LIKE 'MOHE%'

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

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

TO CHECK INSTANCE_NAME WITH INST_ID

 

SELECT B.INSTANCE_NAME,A.inst_id,A.SID,A.SERIAL#,A.USERNAME,A.BLOCKING_INSTANCE,A.FINAL_BLOCKING_SESSION,A.BLOCKING_SESSION,A.LOCKWAIT,A.STATUS,A.PROGRAM,A.MODULE,A.BLOCKING_SESSION_STATUS,A.EVENT,A.STATE FROM GV$INSTANCE B, GV$SESSION A

WHERE A.INST_ID=B.INST_ID


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

select 'alter system kill session ' || '''' || sid || ',' || serial# || '''' || ' immediate;' from v$session where BLOCKING_SESSION IS NOT NULL