myorama
4/9/2014 - 3:42 PM

Lock resolutions

Lock resolutions

set line 200
col table for a25 
col owner for a15
col node for a10
col mode for a10

SELECT 
  s.sid "SID", 
  s.serial# "SER", 
  o.object_name "Table", 
  o.owner, 
  s.osuser "OS User", 
  s.machine "Node", 
  s.terminal "Terminal", 
  --p.spid "SPID", 
  --s.process "CPID", 
  decode (s.lockwait, NULL, 'Have Lock(s)', 'Waiting for <' || b.sid || '>') "Mode", 
  substr (c.sql_text, 1, 150) "SQL Text" 
FROM v$lock l, 
  v$lock d, 
  v$session s, 
  v$session b, 
  v$process p, 
  v$transaction t, 
  sys.dba_objects o, 
  v$open_cursor c 
WHERE l.sid = s.sid 
  AND o.object_id (+) = l.id1 
  AND c.hash_value (+) = s.sql_hash_value 
  AND c.address (+) = s.sql_address 
  AND s.paddr = p.addr 
  AND d.kaddr (+) = s.lockwait 
  AND d.id2 = t.xidsqn (+) 
  AND b.taddr (+) = t.addr 
  AND l.type = 'TM' 
GROUP BY 
  o.object_name, 
  o.owner, 
  s.osuser, 
  s.machine, 
  s.terminal, 
  p.spid, 
  s.process, 
  s.sid, 
  s.serial#, 
  decode (s.lockwait, NULL, 'Have Lock(s)', 'Waiting for <' || b.sid || '>'), 
  substr (c.sql_text, 1, 150) 
ORDER BY 
  decode (s.lockwait, NULL, 'Have Lock(s)', 'Waiting for <' || b.sid || '>') DESC, 
  o.object_name ASC, 
  s.sid ASC; 
SELECT c.owner,  c.object_name,  c.object_type,  b.sid, b.serial#,  b.status,  b.osuser,  b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id
and a.object_id = &objid;
SELECT s1.username || '@' || s1.machine as blocker,
        s1.sid as blocker_sid, s1.serial# as blocker_serial,
        s2.username || '@' || s2.machine as blocked,
        decode(l1.type,'TM','DML','TX','Trans','UL','User',l1.type),
        decode(l1.lmode,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6,'Exclusive', l1.lmode),
        round(l1.ctime / 60) as blocker_duration, round(l2.ctime / 60) as blocked_duration
FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.block=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2;
select count(sid) sessions, block, max(round(ctime/60)) duration 
  from gv$lock where type = 'TX' and lmode > 0 group by block;