{"id":103,"date":"2012-08-14T09:53:51","date_gmt":"2012-08-14T07:53:51","guid":{"rendered":"http:\/\/clemente.pamplona.name\/dba\/?p=103"},"modified":"2012-10-20T20:04:54","modified_gmt":"2012-10-20T18:04:54","slug":"a-la-caza-del-usuario-ii-bloqueos","status":"publish","type":"post","link":"http:\/\/clemente.pamplona.name\/dba\/a-la-caza-del-usuario-ii-bloqueos\/","title":{"rendered":"Sqlplus para dummies III     ( Bloqueos )"},"content":{"rendered":"<p>Otra de las cosas que mas amenudo tenemos que hacer es el encontrar quien nos est\u00e1 bloqueando y que bloquea.<\/p>\n<p>Para ello, la consulta mas sencilla que podemos hacer es:<\/p>\n<pre>SELECT\r\n\u00a0\u00a0\u00a0\u00a0 a.session_id , username ,type , mode_held, mode_requested \r\n\u00a0\u00a0\u00a0\u00a0 lock_id1,lock_id2\r\nFROM\r\n\u00a0\u00a0\u00a0\u00a0 sys.v_$session b,\r\n\u00a0\u00a0\u00a0\u00a0 sys.dba_blockers c,\r\n\u00a0\u00a0\u00a0\u00a0 sys.dba_lock a\r\nWHERE\r\n\u00a0\u00a0\u00a0\u00a0 c.holding_session=a.session_id AND\r\n\u00a0\u00a0\u00a0\u00a0 c.holding_session=b.sid<\/pre>\n<p>Pero,\u00a0 seguramente queramos saber mas cosas, como que tipo de bloqueos y que objetos est\u00e1n implicados, para ello podemos usar esta consulta en la que aparecen\u00a0 datos de la sesi\u00f3n, del proceso tanto en el server como en el cliente y de los objetos y tipo de bloqueo que lleva a cabo<\/p>\n<p>&nbsp;<\/p>\n<pre>SELECT \r\nsubstr(to_char(l.sid),1,4) \"SID\", \r\ns.serial# \"SERIAL\",\r\nP.spid \"Server\u00a0 PID\", \r\ns.USERNAME,\r\ns.type, \r\ns.process \"Client\u00a0 PID\", \r\nMACHINE, \r\nl.type, \r\nDECODE(L.TYPE,'MR','File_ID: '||L.ID1, \r\n'TM', LO.NAME, \r\n'TX','USN: '||to_char(TRUNC(L.ID1\/65536))||' RWO: '||nvl(RWO.NAME,'None'),L.ID1) LOCK_ID1, \r\ndecode(l.lmode, \r\n0, 'None', \r\n1, 'Null', \r\n2, 'Row-S (SS)', \r\n3, 'Row-X (SX)', \r\n4, 'Share', \r\n5, 'S\/Row-X (SSX)', \r\n6, 'Exclusive', \r\nsubstr(to_char(l.lmode),1,13)) \"Locked Mode\", \r\ndecode(l.request, \r\n0, 'None', \r\n1, 'Null', \r\n2, 'Row-S (SS)', \r\n3, 'Row-X (SX)', \r\n4, 'Share', \r\n5, 'S\/Row-X (SSX)', \r\n6, 'Exclusive', \r\nsubstr(to_char(l.request),1,13)) \"Requested\", \r\nl.ctime, \r\nl.block ,\r\ns.logon_time\r\nFROM v$process P, \r\nv$session S, \r\nv$lock l, \r\nsys.obj$ lo, \r\nsys.obj$ rwo \r\nWHERE l.sid = S.sid (+) \r\nAND S.paddr = P.addr (+) \r\nAND LO.OBJ#(+) = L.ID1 \r\nAND RWO.OBJ#(+) = S.ROW_WAIT_OBJ#\r\nand S.type!='BACKGROUND'\r\norder by SID;<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Otra de las cosas que mas amenudo tenemos que hacer es el encontrar quien nos est\u00e1 bloqueando y que bloquea. Para ello, la consulta mas sencilla que podemos hacer es: SELECT \u00a0\u00a0\u00a0\u00a0 a.session_id , username ,type , mode_held, mode_requested \u00a0\u00a0\u00a0\u00a0 &hellip; <a href=\"http:\/\/clemente.pamplona.name\/dba\/a-la-caza-del-usuario-ii-bloqueos\/\">Sigue leyendo <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,7,3],"tags":[248,21],"class_list":["post-103","post","type-post","status-publish","format-standard","hentry","category-dummie","category-sqlplus","category-tunning","tag-dummie","tag-locks"],"_links":{"self":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/comments?post=103"}],"version-history":[{"count":7,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/103\/revisions"}],"predecessor-version":[{"id":265,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/posts\/103\/revisions\/265"}],"wp:attachment":[{"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/media?parent=103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/categories?post=103"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/clemente.pamplona.name\/dba\/wp-json\/wp\/v2\/tags?post=103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}