如何查找表被锁住的行

     如何查询一张表被锁住的是哪一行？也就是说会话是在哪条记录上产生了等待呢?
其实主要就是通过查询v$session,再结合dbms_rowid.ROWID_CREATE获得所等待的OBJECT及其ROWID：

select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row# from v$session;   select owner||'.'||object_name object,object_type from dba_objects where object_id=row_wait_obj#;   select dbms_rowid.ROWID_CREATE (1,:row_wait_obj#,:row_wait_file#,:row_wait_block#,:row_wait_row#) from dual; 简单演示下： sys@CMTR>@lock ------------------------- Sessions about lock --------------------------   STATUS SID INST_ID CTIME ID1 ID2 LMODE REQUEST TYPE -------- -------- ------- ---------- ---------- ---------- ------ ------- ---------- Holder: 16 1 1550 655377 309962 6 0 TX Waiter: 136 1 1550 655377 309962 0 6 TX     ------------------------- Object be locked --------------------------   SYSDATE INST_ID SID OBJECT_ID OBJECT_NAME USERNAME ---------------- ------- -------- ---------- ------------------ ---------- 2007-01-03 11:09 1 136 54649 T_ORDER EPROD sys@CMTR>   sys@CMTR>@wait_row Enter value for input_sid: 136   ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# ------------- -------------- --------------- ------------- 54649 35 244719 5   OBJECT OBJECT_TYPE -------------------------------------------------- ------------ EPROD.T_ORDER TABLE   ROW_ID ------------------------------------ AAANV5AAjAAA7vvAAF   sys@CMTR>select ROW_ID,CREATED,CREATED_BY from EPROD.T_ORDER 2 where rowid='AAANV5AAjAAA7vvAAF';   ROW_ID CREATED CREATED_BY ------------------------------ ---------------- ------------------------------ 1-F3PWJO 2008-11-19 02:47 1-DG6DF3   sys@CMTR>! cat wait_row.sql define l_sid='&input_sid' col row_wait_obj# new_value obj col row_wait_file# new_value file col row_wait_block# new_value block col row_wait_row# new_value row col object for a50   select row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row# from v$session where sid='&l_sid';   select owner||'.'||object_name object,object_type from dba_objects where object_id=&obj;   select dbms_rowid.ROWID_CREATE(1,&obj,&file,&block,&row) row_id from dual; From

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30193/viewspace-509626/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30193/viewspace-509626/