SQL Queries for Database Locks

SQL to check lock held by sessions

select substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser,
substr(l.os_user_name||'/'||l.oracle_username,1,12) username,
l.process,
p.spid,
substr(o.owner||'.'||o.object_name,1,35) owner_object,
decode(l.locked_mode,
1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Excl',
6,'Exclusive',null) locked_mode,
substr(s.status,1,8) status
from
v$locked_object l,
all_objects o,
v$session s,
v$process p
where
l.object_id = o.object_id
and l.session_id  = s.sid
and s.paddr = p.addr
and s.status != 'KILLED';

SQL to check lock held by particular session 

select substr(to_char(l.session_id)||','||to_char(s.serial#),1,12) sid_ser,
    substr(l.os_user_name||'/'||l.oracle_username,1,12) username,
    l.process,
  p.spid,
    substr(o.owner||'.'||o.object_name,1,35) owner_object,
    decode(l.locked_mode,
    1,'No Lock',
    2,'Row Share',
    3,'Row Exclusive',
    4,'Share',
    5,'Share Row Excl',
    6,'Exclusive',null) locked_mode,
    substr(s.status,1,8) status
    from
  v$locked_object l,
    all_objects o,
    v$session s,
    v$process p
        where
    l.object_id = o.object_id
    and l.session_id = s.sid
    and s.paddr = p.addr
    and s.sid=978;

-------------
sample output
-------------

SID_SER      USERNAME    PROCESS      SPID        OWNER_OBJECT                      LOCKED_MODE    STATUS
------------ ------------ ------------ ------------ ----------------------------------- -------------- --------
978,60077    appmyfai/APP 6275        15225        ONT.OE_HEADERS_IFACE_ALL            Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        ONT.OE_ACTIONS_IFACE_ALL            Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        ONT.OE_PRICE_ADJS_IFACE_ALL        Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        ONT.OE_LINES_IFACE_ALL            Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225      BOLINF.XXMF_TX_IN_ORDER_HEADERS    Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        BOLINF.XXMF_TX_IN_COUPONS          Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        BOLINF.XXMF_TX_IN_ORDER_LINES      Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        BOLINF.XXMF_ORDER_HEADERS          Row Exclusive  KILLED
978,60077    appmyfai/APP 6275        15225        BOLINF.XXMF_ORDER_LINES            Row Exclusive  KILLED

Regards
Manoj