如何查询一张表被锁住的是哪一行？也就是说会话是在哪条记录上产生了等待呢?
其实主要就是通过查询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 OoNiceDream

ITPub博客

如何查找表被锁住的行

最新文章