ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何查找表被锁住的行

如何查找表被锁住的行

原创 Linux操作系统 作者:Kenniu 时间:2019-04-22 12:18:05 0 删除 编辑

     如何查询一张表被锁住的是哪一行?也就是说会话是在哪条记录上产生了等待呢?
其实主要就是通过查询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博客 ” ,链接:http://blog.itpub.net/30193/viewspace-509626/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-11-29

  • 博文量
    142
  • 访问量
    107374