Oracle: 行锁等待时从V$active_session_history中的信息构建rowid

行锁等待(enq: TX - row lock contention)出现时，有时想要得到具体是等待在哪一行数据上，V$active_session_history中有几个字段可用于构建rowid：current_obj#，current_file#，current_block#，current_row#。

其中需要注意的是current_obj#对应的是dba_objects.object_id，但是dbms_rowid.rowid_create构建rowid时，实际使用的是dba_objects.data_object_id。

所以当dba_objects.object_id<>dba_objects.data_object_id时，使用object_id构建的rowid会不准确或报无效rowid错误。

--dbms_rowid.rowid_create:
FUNCTION ROWID_CREATE RETURNS ROWID
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ROWID_TYPE                     NUMBER                  IN         << 1 - extended , 0 - restricted
 OBJECT_NUMBER                  NUMBER                  IN         << data_object_id
 RELATIVE_FNO                   NUMBER                  IN
 BLOCK_NUMBER                   NUMBER                  IN
 ROW_NUMBER                     NUMBER                  IN

查询V$active_session_history中行锁等待事件相关会话信息，同时获取相关数据行的rowid：

select to_char(sample_time, 'YYYYMMDD-hh24miss') as stime,
       session_id,
       session_serial# as ss,
       SQL_EXEC_START,
       session_state,
       event,
       p1,
       p2,
       P3,
       wait_time,
       time_waited,
       blocking_session as block_sid,
       sql_id,
       program,
       machine,
       dbms_rowid.rowid_create('1',
                               (select data_object_id
                                  from dba_objects
                                 where object_id = a.current_obj#),
                               current_file#,
                               current_block#,
                               current_row#) row_id
  from V$active_session_history a
 where sample_time between
       to_date('2021-03-12 11:07:01', 'YYYY-MM-DD hh24:mi:ss') and
       to_date('2021-03-12 11:08:01', 'YYYY-MM-DD hh24:mi:ss')
   and event = 'enq: TX - row lock contention'
 order by sample_time;