使用DBMS_ROWID擷取被阻塞行的rowid

在使用v$session視圖在查詢會話的行鎖的等待事件時，視圖中提供了會話等待的對象号(ROW_WAIT_OBJ#)、檔案号(ROW_WAIT_FILE#)、塊号(ROW_WAIT_BLOCK#)和行号(ROW_WAIT_ROW#)但是如何使用這些資訊定位出會話等待的是哪一行呢？答案就是使用DBMS_ROWID

打開兩個會話同時更新同一條資料

<code>#session 1</code>

<code>zx@ORCL&gt;</code><code>select</code> <code>distinct</code> <code>sid </code><code>from</code> <code>v$mystat;</code>

<code>       </code><code>SID</code>

<code>----------</code>

<code>    </code><code>22</code>

<code>zx@ORCL&gt;</code>

<code>zx@ORCL&gt;</code><code>update</code> <code>zx </code><code>set</code> <code>name</code><code>=</code><code>'zx'</code> <code>where</code> <code>id=1;</code>

<code>1 row updated.</code>

<code>#session 2</code>

<code>       </code><code>145</code>

<code>       </code> 

此時session2會被session1阻塞，查詢v$session會話145在等待enq: TX - row lock contention

<code>zx@ORCL&gt;col event </code><code>for</code> <code>a40</code>

<code>zx@ORCL&gt;</code><code>select</code> <code>SID,EVENT,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# </code><code>from</code> <code>v$session </code><code>where</code> <code>sid=145;</code>

<code>       </code><code>SID EVENT                    ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#</code>

<code>---------- ---------------------------------------- ------------- -------------- --------------- -------------</code>

<code>       </code><code>145 enq: TX - row lock contention           99754         18     15571      7</code>

查詢v$lock确認會話145在請求會話22的TX鎖

<code>zx@ORCL&gt;</code><code>select</code> <code>sid,type,id1,id2,lmode,request </code><code>from</code> <code>v$lock </code><code>where</code> <code>sid=145 </code><code>or</code> <code>sid=22 </code><code>order</code> <code>by</code> <code>1;</code>

<code>       </code><code>SID TYPE     ID1       ID2      LMODE    REQUEST</code>

<code>---------- ------ ---------- ---------- ---------- ----------</code>

<code>    </code><code>22 AE       100         0   4     0</code>

<code>    </code><code>22 TM         99754       0   3     0</code>

<code>    </code><code>22 TX       4390915       581     6     0</code>

<code>       </code><code>145 TM          99754       0   3     0</code>

<code>       </code><code>145 TX        4390915       581     0     6</code>

<code>       </code><code>145 AE        100         0   4     0</code>

使用如下語句查詢會話145等待哪個表的哪個行

<code>zx@ORCL&gt;col owner </code><code>for</code> <code>a10</code>

<code>zx@ORCL&gt;col object_name </code><code>for</code> <code>a10</code>

<code>zx@ORCL&gt;col rowid </code><code>for</code> <code>a30</code>

<code>zx@ORCL&gt;</code><code>select</code> <code>b.owner,b.object_name,dbms_rowid.rowid_create(1,s.ROW_WAIT_OBJ#,s.ROW_WAIT_FILE#,s.ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) </code><code>"rowid"</code> <code>from</code> <code>v$session s,dba_objects b </code><code>where</code> <code>s.ROW_WAIT_OBJ#=b.object_id </code><code>and</code> <code>s.si</code>

<code>d=145;</code>

<code>OWNER      OBJECT_NAM rowid</code>

<code>---------- ---------- ------------------------------</code>

<code>ZX     ZX          AAAYWqAASAAADzTAAH</code>

<code>--使用上面查詢出的rowid檢視資料，即為session2等待的行</code>

<code>zx@ORCL&gt;</code><code>select</code> <code>* </code><code>from</code> <code>zx.zx </code><code>where</code> <code>rowid=</code><code>'AAAYWqAASAAADzTAAH'</code><code>;</code>

<code>    </code><code>ID </code><code>NAME</code>

<code>---------- ------------------------------</code>

<code>     </code><code>1 ZX</code>

使用下面語句查找會話之間的阻塞關系

<code>SELECT</code> <code>(</code><code>'節點'</code> <code>|| a.inst_id || </code><code>' session '</code> <code>|| a.sid || </code><code>','</code> <code>|| a_s.serial# ||</code>

<code>       </code><code>'阻塞了節點'</code> <code>|| b.inst_id || </code><code>' session '</code> <code>|| b.sid || </code><code>','</code> <code>|| b_s.serial#) blockinfo,</code>

<code>       </code><code>a.inst_id,</code>

<code>       </code><code>a_s.sid,</code>

<code>       </code><code>a_s.schemaname,</code>

<code>       </code><code>a_s.module,</code>

<code>       </code><code>a_s.status,</code>

<code>       </code><code>a_s.event,</code>

<code>       </code><code>a.type lock_type,</code>

<code>       </code><code>a.id1,</code>

<code>       </code><code>a.id2,</code>

<code>       </code><code>decode(a.lmode,</code>

<code>              </code><code>0,</code>

<code>              </code><code>'none'</code><code>,</code>

<code>              </code><code>1,</code>

<code>              </code><code>NULL</code><code>,</code>

<code>              </code><code>2,</code>

<code>              </code><code>'row-S(SS)'</code><code>,</code>

<code>              </code><code>3,</code>

<code>              </code><code>'row-X(SX)'</code><code>,</code>

<code>              </code><code>4,</code>

<code>              </code><code>'share(S)'</code><code>,</code>

<code>              </code><code>5,</code>

<code>              </code><code>'S/Row-X(SSX)'</code><code>,</code>

<code>              </code><code>6,</code>

<code>              </code><code>'exclusive(X)'</code><code>) lock_mode,</code>

<code>       </code><code>a.ctime time_hold,</code>

<code>       </code><code>'後為被阻塞資訊'</code> <code>remark_flag,</code>

<code>       </code><code>b.inst_id blocked_inst_id,</code>

<code>       </code><code>b.sid blocked_sid,</code>

<code>       </code><code>b.type blocked_lock_type,</code>

<code>       </code><code>decode(b.request,</code>

<code>              </code><code>'exclusive(X)'</code><code>) blocked_lock_request,</code>

<code>       </code><code>b.ctime time_wait,</code>

<code>       </code><code>b_s.schemaname blocked_schemaname,</code>

<code>       </code><code>b_s.module blocked_module,</code>

<code>       </code><code>b_s.status blocked_status,</code>

<code>       </code><code>b_s.sql_id blocked_sql_id,</code>

<code>       </code><code>b_s.event,</code>

<code>       </code><code>obj.owner blocked_owner,</code>

<code>       </code><code>obj.object_name blocked_name,</code>

<code>       </code><code>obj.object_type blocked_object_type,</code>

<code>       </code><code>CASE</code>

<code>         </code><code>WHEN</code> <code>b_s.row_wait_obj# &lt;&gt; -1 </code><code>THEN</code>

<code>          </code><code>dbms_rowid.rowid_create(1,</code>

<code>                                  </code><code>obj.data_object_id,</code>

<code>                                  </code><code>b_s.row_wait_file#,</code>

<code>                                  </code><code>b_s.row_wait_block#,</code>

<code>                                  </code><code>b_s.row_wait_row#)</code>

<code>         </code><code>ELSE</code>

<code>          </code><code>'-1'</code>

<code>       </code><code>END</code> <code>blocked_rowid, </code><code>--被阻塞資料的rowid</code>

<code>       </code><code>decode(obj.object_type,</code>

<code>              </code><code>'TABLE'</code><code>,</code>

<code>              </code><code>'select * from '</code> <code>|| obj.owner || </code><code>'.'</code> <code>|| obj.object_name ||</code>

<code>              </code><code>' where rowid='</code><code>''</code> <code>||</code>

<code>              </code><code>dbms_rowid.rowid_create(1,</code>

<code>                                      </code><code>obj.data_object_id,</code>

<code>                                      </code><code>b_s.row_wait_file#,</code>

<code>                                      </code><code>b_s.row_wait_block#,</code>

<code>                                      </code><code>b_s.row_wait_row#) || </code><code>''</code><code>''</code><code>,</code>

<code>              </code><code>NULL</code><code>) blocked_data_querysql</code>

<code>  </code><code>FROM</code> <code>gv$lock     a,</code>

<code>       </code><code>gv$lock     b,</code>

<code>       </code><code>gv$session  a_s,</code>

<code>       </code><code>gv$session  b_s,</code>

<code>       </code><code>dba_objects obj</code>

<code> </code><code>WHERE</code> <code>a.id1 = b.id1</code>

<code>   </code><code>AND</code> <code>a.id2 = b.id2</code>

<code>   </code><code>AND</code> <code>a.block &gt; 0 </code><code>--阻塞了其他人</code>

<code>   </code><code>AND</code> <code>b.request &gt; 0 </code><code>--AND ((a.INST_ID=b.INST_ID AND a.SID&lt;&gt;b.SID) OR (a.INST_ID&lt;&gt;b.INST_ID ))</code>

<code>   </code><code>AND</code> <code>a.sid = a_s.sid</code>

<code>   </code><code>AND</code> <code>a.inst_id = a_s.inst_id</code>

<code>   </code><code>AND</code> <code>b.sid = b_s.sid</code>

<code>   </code><code>AND</code> <code>b.inst_id = b_s.inst_id</code>

<code>   </code><code>AND</code> <code>b_s.row_wait_obj# = obj.object_id(+)</code>

<code> </code><code>ORDER</code> <code>BY</code> <code>a.inst_id, a.sid;</code>

     本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1896136，如需轉載請自行聯系原作者