Oracle Locks and killing the blocking sessions

The below queries will identify the locked sessions, the owner of the locked objects.

This also explains how to kill the blocking locked sessions.

--Active sessions
select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
--     b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by os_user,username;

-- Query to identify the locks
SELECT username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
  DECODE(v.locked_mode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
  ) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id;

--Identify the locks of the owner

SELECT *
FROM DBA_DML_LOCKS where owner=<owner_name>;

--Use this session id to find SERIAL# by using following SELECT statement
SELECT SID,SERIAL# 
FROM V$SESSION 
WHERE SID IN (SELECT SESSION_ID 
FROM DBA_DML_LOCKS 
 where owner=<owner_name>);

--Use ALTER SYSTEM command to KILL SESSION and this will release the lock:
ALTER SYSTEM KILL SESSION '152,361';

In order to execute this, you may need Oracle DBA previlieges. Check with your DBA to execute this step.