Last active
December 26, 2018 15:14
-
-
Save fljdin/a7f013e26329605efd1d to your computer and use it in GitHub Desktop.
Lock resolutions
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select count(sid) sessions, block, max(round(ctime/60)) duration | |
from gv$lock where type = 'TX' and lmode > 0 group by block; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment