Find session of locked row

Question

I am experiencing row lock contention in my oracle DB. I tried to kill some session to unlock them, but this rows are still locked. I know exact which row are locked. Can I find the session ID that has locked this row. I can get the ROWID of that row.

Answer 1

As the good folks on AskTom say, we don't maintain a list of locked rows

But, if you want to try this - it'll show you locks by USER in your database, including Row locks.

SELECT
    p.username   username,
    p.pid        pid,
    s.sid        sid,
    s.serial#    serial,
    p.spid       spid,
    s.username   ora,
    DECODE(l2.type, 'TX', 'TRANSACTION ROW-LEVEL', 'RT', 'REDO-LOG', 'TS', 'TEMPORARY SEGMENT ', 'TD', 'TABLE LOCK', 'TM', 'ROW LOCK'
    , l2.type) vlock,
    DECODE(l2.type, 'TX', 'DML LOCK', 'RT', 'REDO LOG', 'TS', 'TEMPORARY SEGMENT', 'TD', DECODE(l2.lmode + l2.request, 4, 'PARSE '
    || u.name || '.' || o.name, 6, 'DDL', l2.lmode + l2.request), 'TM', 'DML ' || u.name || '.' || o.name, l2.type) type,
    DECODE(l2.lmode + l2.request, 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 6, 'X', l2.lmode + l2.request) lmode,
    DECODE(l2.request, 0, NULL, 'WAIT') wait
FROM
    gv$process p,
    gv$_lock l1,
    gv$lock l2,
    gv$resource r,
    sys.obj$ o,
    sys.user$ u,
    gv$session s
WHERE
    s.paddr = p.addr
    AND s.saddr = l1.saddr
    AND l1.raddr = r.addr
    AND l2.addr = l1.laddr
    AND l2.type <> 'MR'
    AND r.id1 = o.obj# (+)
    AND o.owner# = u.user# (+)
                                                  --AND  u.name = 'GME'
    AND ( :user_name IS NULL
          OR s.username LIKE upper(:user_name) )
ORDER BY
    p.username,
    p.pid,
    p.spid,
    ora,
    DECODE(l2.type, 'TX', 'TRANSACTION ROW-LEVEL', 'RT', 'REDO-LOG', 'TS', 'TEMPORARY SEGMENT ', 'TD', 'TABLE LOCK', 'TM', 'ROW LOCK'
    , l2.type)

This is a report in SQL Developer.