{"id":40,"date":"2012-03-01T02:17:04","date_gmt":"2012-03-01T02:17:04","guid":{"rendered":"http:\/\/oranotes.tuktaroff.com\/?page_id=40"},"modified":"2016-06-03T05:22:03","modified_gmt":"2016-06-03T05:22:03","slug":"blocking-locks","status":"publish","type":"page","link":"http:\/\/oranotes.tuktaroff.com\/?page_id=40","title":{"rendered":"Blocking locks"},"content":{"rendered":"<p><code><br \/>\n<script type=\"text\/javascript\"> \nfunction copy(what) {\n    window.clipboardData.setData('Text',what.value);\n}\nfunction paste(what) {\n    what.value = window.clipboardData.getData('Text');\n}\n<\/script><\/p>\n<h2>\nBlocking locks<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nSET LINESIZE  300\r\nSET PAGESIZE  9999\r\nSET VERIFY    off\r\n\r\nSELECT\r\n    SUBSTR(s1.username,1,12)           \"WAITING USER\"\r\n  , SUBSTR(s1.osuser,1,8)              \"OS User\"\r\n  , SUBSTR(TO_CHAR(w.session_id),1,5)  \"Sid\"\r\n  , p1.spid                            \"PID\"\r\n  , SUBSTR(s2.username,1,12)           \"HOLDING User\"\r\n  , SUBSTR(s2.osuser,1,8)              \"OS User\"\r\n  , SUBSTR(TO_CHAR(h.session_id),1,5)  \"Sid\"\r\n  , p2.spid                            \"PID\"\r\nFROM\r\n    sys.v_$process p1\r\n  , sys.v_$process p2\r\n  , sys.v_$session s1\r\n  , sys.v_$session s2\r\n  , dba_locks  w\r\n  , dba_locks  h\r\nWHERE\r\n      h.mode_held      != 'None'\r\n  AND h.mode_held      != 'Null'\r\n  AND w.mode_requested != 'None'\r\n  AND w.lock_type  (+)  = h.lock_type\r\n  AND w.lock_id1   (+)  = h.lock_id1\r\n  AND w.lock_id2   (+)  = h.lock_id2\r\n  AND w.session_id      = s1.sid   (+)\r\n  AND h.session_id      = s2.sid   (+)\r\n  AND s1.paddr          = p1.addr  (+)\r\n  AND s2.paddr          = p2.addr  (+)\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nSET LINESIZE  300<br \/>\nSET PAGESIZE  9999<br \/>\nSET VERIFY    off<\/p>\n<p>SELECT<br \/>\n    SUBSTR(s1.username,1,12)           \"WAITING USER\"<br \/>\n  , SUBSTR(s1.osuser,1,8)              \"OS User\"<br \/>\n  , SUBSTR(TO_CHAR(w.session_id),1,5)  \"Sid\"<br \/>\n  , p1.spid                            \"PID\"<br \/>\n  , SUBSTR(s2.username,1,12)           \"HOLDING User\"<br \/>\n  , SUBSTR(s2.osuser,1,8)              \"OS User\"<br \/>\n  , SUBSTR(TO_CHAR(h.session_id),1,5)  \"Sid\"<br \/>\n  , p2.spid                            \"PID\"<br \/>\nFROM<br \/>\n    sys.v_$process p1<br \/>\n  , sys.v_$process p2<br \/>\n  , sys.v_$session s1<br \/>\n  , sys.v_$session s2<br \/>\n  , dba_locks  w<br \/>\n  , dba_locks  h<br \/>\nWHERE<br \/>\n      h.mode_held      != 'None'<br \/>\n  AND h.mode_held      != 'Null'<br \/>\n  AND w.mode_requested != 'None'<br \/>\n  AND w.lock_type  (+)  = h.lock_type<br \/>\n  AND w.lock_id1   (+)  = h.lock_id1<br \/>\n  AND w.lock_id2   (+)  = h.lock_id2<br \/>\n  AND w.session_id      = s1.sid   (+)<br \/>\n  AND h.session_id      = s2.sid   (+)<br \/>\n  AND s1.paddr          = p1.addr  (+)<br \/>\n  AND s2.paddr          = p2.addr  (+)<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nCODE_TO_COPY_1<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nBlocking locks (RAC)<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nset linesize 500\r\nset pagesize 9999\r\nselect  \/*+ ordered *\/ \r\n\t\tto_char(l.sid)|| ' - ' || to_char(w.sid) SID,\r\n\t\t'DB instance: ' || trim(i.instance_name) || ' : blocking lock : ' ||trim(w.event) ||' - '\r\n        ||' WAITER: dbuser='|| ltrim(s.username)\r\n       ||' sid='|| rtrim(ltrim(w.sid))\r\n       ||' ospid='|| rtrim(ltrim(p.spid))\r\n       ||' time_waited(s)='|| rtrim(ltrim(to_char(w.seconds_in_wait)))\r\n       ||' program='|| rtrim(ltrim(s.program))\r\n       ||' machine='|| rtrim(ltrim(s.machine))\r\n       ||' - BLOCKER: dbuser='|| ltrim(s1.username)\r\n       ||' sid='|| rtrim(ltrim(l.sid))\r\n       ||' ospid='|| rtrim(ltrim(p1.spid))\r\n       ||' program='|| rtrim(ltrim(s1.program))\r\n       ||' machine='|| rtrim(ltrim(s1.machine)) lock_desc,\r\n\t   w.seconds_in_wait\r\nfrom gv$lock l, v$session s, v$process p, v$session_wait w,\r\n     gv$session s1, gv$process p1, v$instance i\r\nwhere ( w.event = 'enqueue' or w.event like 'enq%' )\r\nand w.state = 'WAITING'\r\nand w.p2 = l.id1\r\nand l.block in (1, 2)\r\nand upper(l.type) in ('TM','TX','UL')\r\nand w.sid = s.sid\r\nand s.paddr = p.addr\r\nand l.sid = s1.sid\r\nand l.inst_id  = s1.inst_id\r\nand s1.paddr = p1.addr\r\nand s1.inst_id = p1.inst_id\r\norder by s.sid, s.serial#;\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nset linesize 500<br \/>\nset pagesize 9999<br \/>\nselect  \/*+ ordered *\/<br \/>\n\t\tto_char(l.sid)|| ' - ' || to_char(w.sid) SID,<br \/>\n\t\t'DB instance: ' || trim(i.instance_name) || ' : blocking lock : ' ||trim(w.event) ||' - '<br \/>\n        ||' WAITER: dbuser='|| ltrim(s.username)<br \/>\n       ||' sid='|| rtrim(ltrim(w.sid))<br \/>\n       ||' ospid='|| rtrim(ltrim(p.spid))<br \/>\n       ||' time_waited(s)='|| rtrim(ltrim(to_char(w.seconds_in_wait)))<br \/>\n       ||' program='|| rtrim(ltrim(s.program))<br \/>\n       ||' machine='|| rtrim(ltrim(s.machine))<br \/>\n       ||' - BLOCKER: dbuser='|| ltrim(s1.username)<br \/>\n       ||' sid='|| rtrim(ltrim(l.sid))<br \/>\n       ||' ospid='|| rtrim(ltrim(p1.spid))<br \/>\n       ||' program='|| rtrim(ltrim(s1.program))<br \/>\n       ||' machine='|| rtrim(ltrim(s1.machine)) lock_desc,<br \/>\n\t   w.seconds_in_wait<br \/>\nfrom gv$lock l, v$session s, v$process p, v$session_wait w,<br \/>\n     gv$session s1, gv$process p1, v$instance i<br \/>\nwhere ( w.event = 'enqueue' or w.event like 'enq%' )<br \/>\nand w.state = 'WAITING'<br \/>\nand w.p2 = l.id1<br \/>\nand l.block in (1, 2)<br \/>\nand upper(l.type) in ('TM','TX','UL')<br \/>\nand w.sid = s.sid<br \/>\nand s.paddr = p.addr<br \/>\nand l.sid = s1.sid<br \/>\nand l.inst_id  = s1.inst_id<br \/>\nand s1.paddr = p1.addr<br \/>\nand s1.inst_id = p1.inst_id<br \/>\norder by s.sid, s.serial#;<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<h2>\nBlocking session<br \/>\n<\/h2>\n<pre class=\"qcode\">\r\nselect\r\n    blocking_session,\r\n    sid,    serial#,\r\n    wait_class,\r\n    seconds_in_wait \r\nfrom\r\n    v$session \r\nwhere\r\n    blocking_session is not NULL\r\norder by\r\n    blocking_session\r\n\/\r\n<\/pre>\n<form action=\"\"><textarea name=\"data\" cols=\"50\" rows=\"10\" style=\"display:none;\"><br \/>\nselect<br \/>\n    blocking_session,<br \/>\n    sid,    serial#,<br \/>\n    wait_class,<br \/>\n    seconds_in_wait<br \/>\nfrom<br \/>\n    v$session<br \/>\nwhere<br \/>\n    blocking_session is not NULL<br \/>\norder by<br \/>\n    blocking_session<br \/>\n\/<br \/>\n<\/textarea><input type=\"button\" value=\"Clipboard\" onclick=\"copy(this.form.data)\"><\/form>\n<p><\/code><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Blocking locks SET LINESIZE 300 SET PAGESIZE 9999 SET VERIFY off SELECT SUBSTR(s1.username,1,12) &#8220;WAITING USER&#8221; , SUBSTR(s1.osuser,1,8) &#8220;OS User&#8221; , SUBSTR(TO_CHAR(w.session_id),1,5) &#8220;Sid&#8221; , p1.spid &#8220;PID&#8221; , SUBSTR(s2.username,1,12) &#8220;HOLDING User&#8221; , SUBSTR(s2.osuser,1,8) &#8220;OS User&#8221; , SUBSTR(TO_CHAR(h.session_id),1,5) &#8220;Sid&#8221; , p2.spid &#8220;PID&#8221; FROM sys.v_$process p1 , sys.v_$process p2 , sys.v_$session s1 , sys.v_$session s2 , dba_locks w , [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":34,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-40","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2NVny-E","_links":{"self":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/40","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=40"}],"version-history":[{"count":6,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/40\/revisions"}],"predecessor-version":[{"id":570,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/40\/revisions\/570"}],"up":[{"embeddable":true,"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=\/wp\/v2\/pages\/34"}],"wp:attachment":[{"href":"http:\/\/oranotes.tuktaroff.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=40"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}