查找锁定行的会话

Question

我在我的oracle DB中遇到了row lock contention。我试图杀死一些会话来解锁它们，但这些行仍然被锁定。我知道哪一行被锁定了。我是否可以找到锁定此行的会话ID。我可以得到该行的ROWID。

Answer 1

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

但是，如果你想尝试这样做--它会在你的数据库中按用户显示锁，包括行锁。

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)

这是SQL Developer中的报表。

​

​