Finding blocked sessions

Ever had an oracle command blocked by a hanging session? Here's how to find them...and kill them if you want to.


select 
  c.owner,
  c.object_name,
  c.object_type,
  b.sid,
  b.serial#, 
  b.status,
  b.osuser,
  b.machine 
from 
  v$locked_object a,
  v$session b,
  dba_objects c 
where 
  b.sid = a.session_id 
  and a.object_id = c.object_id;

This query will display blocked session, along with their owner, and other useful info.

If you wish to kill one of the session displayed, grab the session sid and serial, and execute the following :

alter system kill session sid,serial;

Done.