Oracle:查找锁定行返回错误的行ID

Question

我有一个Oracle DB 19c，这是客户应用程序的基础。

当应用程序访问表行时，它将锁定该行，然后从另一台计算机启动的应用程序将无法再访问该记录。我想知道是谁、哪台电脑锁定了哪条记录。

在我的研究中，我发现了这个指南:http://www.orafaq.com/node/854它很好地列出了要做的步骤，最后我为我的目的创建了以下查询:

select LCK.*,OBJ.*,SESS.*, (CASE WHEN SESS.ROW_WAIT_OBJ# > -1 THEN dbms_rowid.rowid_create( 1, obj.Object_ID, SESS.ROW_WAIT_FILE#, SESS.ROW_WAIT_BLOCK#, SESS.ROW_WAIT_ROW# ) ELSE 'NA' END) as ROWID111
from v$locked_object LCK
left join dba_objects OBJ
on LCK.OBJECT_ID = OBJ.OBJECT_ID
left join v$session SESS
on LCK.SESSION_ID = SESS.SID;

我的目标是返回表中被锁定的行。

不幸的是，dbms_rowid_rowid_create()方法不能生成正确的行ID。我做了一些逆向工程，发现…

• 提供给rowid_create()的Object ID必须是dba_objects
中的object_id
• 捐。ROW_WAIT_FILE#指向正确的文件
• 捐。ROW_WAIT_BLOCK不是包含被锁记录
的表的块ID。
• 捐。row_wait_row#不是被锁记录的行号

似乎不知何故，v$session表不包含我需要检索生成rowID的正确值的信息....

Answer 1

我总是在不同的Oracle版本中使用以下语句，我从来没有遇到任何问题

获取阻塞会话和相关进程

set lines 300
COLUMN username FORMAT A30
COLUMN osuser FORMAT A10
COLUMN machine FORMAT A30
COLUMN logon_time FORMAT A20
COLUMN PROGRAM format a20
COLUMN MODULE format a20
COLUMN sid_serial_inst format a20
SELECT level,
LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid || ',' || s.serial# || ',@'|| inst_id as sid_serial_inst,
s.blocking_session,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   gv$session s
WHERE  level > 1
OR     EXISTS (SELECT 1
FROM   gv$session
WHERE  blocking_session = s.sid and inst_id=s.inst_id)
CONNECT BY PRIOR  s.sid || '@inst'|| inst_id = s.blocking_session || '@inst'|| inst_id 
START WITH s.blocking_session IS NULL;

获取行锁定

set lines 200
select s.sid,do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid in (SELECT sid FROM  GV$LOCK WHERE  request>0)
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

会话1

SQL> create table test1 ( c1 number, c2 number );
Table created.
SQL> insert into test1 values ( 1, 1);
1 row created.
SQL> insert into test1 values ( 2,2) ;
1 row created.
SQL> commit ;
Commit complete.

会话2

SQL> update test1 set c1=3 where c1=2 ;
1 row updated.
SQL>

然后在会话1

SQL> update test1 set c1=4 where c1=2 ;

这里会话被锁定，等待另一个会话提交或回滚，因此让我们运行查询来检查受影响的行

SQL> r
1  select s.sid,do.object_name,
2  row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3  dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
4  from v$session s, dba_objects do
5  where sid in (SELECT sid FROM  GV$LOCK WHERE  request>0)
6* and s.ROW_WAIT_OBJ# = do.OBJECT_ID
Session-ID OBJECT_NAME                    ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
---------- ------------------------------ ------------- -------------- --------------- ------------- ------------------
601 TEST1                                1032538              1          123257             1 AAD8FaAABAAAeF5AAB