如何查看数据库TX锁对应的表的哪一行

大家好，欢迎来到IT知识分享网。

实验如下：

三个会话，分别为sid141 sid18 及单独会话查看系统状态的窗口。实验对象为tx_eg。两个会话更新表的第三行（num=3）,我们通过相关视图进行查看，能否找出对应的行？

sid141执行如下

LUQX@oradb>select distinct(sid) from v$mystat;
SID
----------
141
LUQX@oradb>select * from tx_eg;
NUM TXT SEX
---------- ---------- ----------
1 Garbage FEMALE
2 Second MALE
3 Third MALE
4 Garbage MALE
5 Fifth MALE
LUQX@oradb>update tx_eg set txt='Thirdsd' where num=3;
1 row updated.

sid18执行如下

LUQX@oradb>select distinct(sid) from v$mystat;
SID
----------
18
LUQX@oradb>update tx_eg set txt='Thirdsd' where num=3;

单独窗口执行状态查看 查看当前锁的状态目前持有锁的会话为141（LMODE=6）

SYS@oradb> Select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from v$lock where block >0;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
141 TX 196638 1774 6 0 110 1

2、查看141阻塞的会话及对应的block信息

select sid,row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where blocking_session=&blocking_session;
SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ------------- -------------- --------------- -------------
18 91604 6 435 2
Details of Blocked Session
SID of blocked session: 18
Object_id: 91604    对象名称
File #: 6    所再数据文件编号
Block #: 435  	
row_wait_row#－－－被锁定行的rownum，但是准确的说是对应的于rowid的rownum，并非是单纯的rownum。下面再详细的说。
row_wait_obj#－－－row_wait_row#对应的rowid所在的表的object id。
row_wait_file#－－ row_wait_row#对应的rowid所在的数据文件id
row_wait_block#－－row_wait_row#对应的rowid所在的block的id

3、进一步确认行内容

查看对象信息通过object_id

column owner format a20
column object_name format a40
select owner,object_name from dba_objects where object_id=&object_id ;

通过wait_file#查看对象存在的文件信息

SELECT rfile#, file# , name FROM v$datafile WHERE file# = &file_number;

通过关联查询查看sid141的会话对应的行rowid

 column  object_name format a30
 SELECT do.object_name ,
       s.row_wait_obj#  ,
       s.row_wait_file# ,
       s.row_wait_block#,
       s.row_wait_row#  ,
       dbms_rowid.rowid_create ( 1, data_object_id, rfile#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
FROM   v$session s,
       dba_objects do,
       v$datafile v
WHERE  s.blocking_session = &blocking_session                            
AND    s.row_wait_obj# = do.object_id
AND    s.row_wait_file# = v.file#;    输入141会话的sid
红色为对应的rowid

通过rowid查看row信息，num=3

SYS@oradb>select * from luqx.tx_eg where rowid='AAAWXUAAGAAAAGzAAC';
NUM TXT SEX
---------- ---------- ----------
3 Third MALE