Locks
select * form v$lock where request > 0;

 
select decode(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
from v$lock
where (id1, id2, type) IN (select id1, id2, type
from v$lock where request > 0)
order by id1, request;
 
SELECT LPAD(' ',DECODE(l.xidusn,0,3,0))||l.oracle_username "USER NAME"
, o.owner
, o.object_name
, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER BY o.object_id, 1 desc;
 
SELECT waiting_session
, holding_session
, lock_type
, mode_held
, mode_requested

FROM dba_waiters;
 
3. Who is holding the lock



SELECT waiting_session
, holding_session
, lock_type
, mode_held
, mode_requested
FROM dba_waiters;

4.usersnames for sessions from query above

SELECT username,sid
FROM V$SESSION
WHERE SID IN (12,15)

replace 12 & 15 with output from query 3