If I have obj_id (table), file_id, block and row, how can I query the actual data row from table?

If I update same data row from 2 sessions and don't commit, then run query from the 3rd session:

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where lockwait is not null;

I'll see the blocked session pending on:

row_wait_obj#    row_wait_file# row_wait_block#  row_wait_row#
----------       ----------     ----------       ----------
       258          1           2082             0

Suppose row_wait_obj#=258 is a table. How can I see the actual data row where contention happened?

Solution

Columns like ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, and ROW_WAIT_ROW# can be converted into the actual data row by using a combination of data dictionary queries and used to find the actual data and the DBMS_ROWID package.

The exact values posted in the question cannot be replicated, since they depend on many system-specific details. But the below example demonstrates how to find the data for a similar situation.

Create a table and open up two separate sessions. One sessions will update a row, and the other session will try to update the same row but will be blocked and have to wait for the first session to finish.

-- Create a simple table with 100K rows.
create table table1(a number);
insert into table1 select level from dual connect by level <= 100000;
commit;

-- Session #1: Update one row in the middle of the table to a negative number;
update table1 set a = -50000 where a = 50000;

-- Session #2: Waits until session #1 is committed or rolled back.
update table1 set a = -12345 where a = 50000;

That waiting second session will now show up in the data dictionary:

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where lockwait is not null;


ROW_WAIT_OBJ#   ROW_WAIT_FILE#   ROW_WAIT_BLOCK#   ROW_WAIT_ROW#
-------------   --------------   ---------------   -------------
       137247               12             91565             499

Find the table name with this query. As expected, the table name is the table we just created.

select * from dba_objects where object_id = 137247;

OBJECT_NAME
---
TABLE1

Find the ROWID, which is like a physical pointer to the relevant row, using DBMS_ROWID:

select dbms_rowid.rowid_create(rowid_type    => 1 /* extended rowid */,
                               object_number => 137247,
                               relative_fno  => 12,
                               block_number  => 91565,
                               row_number    => 499) the_rowid
from dual;

THE_ROWID
---------
AAAhgfAAMAAAWWtAHz

Finally, use the ROWID to query the individual row that was changed:

select * from table1 where rowid = 'AAAhgfAAMAAAWWtAHz';

A
-----
50000

This approach may not work perfectly for this specific case

While the above code answers your question in most cases, it looks like you have an unusual issue. Your low OBJECT_ID of 258 implies the waits are for a system-owned object. And the ROW_WAIT_ROW# of 0 implies that the wait is not based on a row, and it is either a DDL wait or a SELECT wait. Performance "wait events", such as the time it might take to read from a block, appear in GV$ACTIVE_SESSION_HISTORY with CURRENT_ROW# of 0, and I would guess the same thing is happening here.

You'll probably need to investigate your problem a different way. You'll still want to start with querying DBA_OBJECTS, but then you might need to find the the relevant sessions by querying GV$SESSION, then find the SQL_IDs, confirm that the SQL_IDs are really slow by looking at GV$ACTIVE_SESSION_HISTORY (each row represents one second of waiting time, so if only pay attention to statements with many rows of waiting), then look up the relevant statements in GV$SQL. You may still need a lot of troubleshooting to get to the bottom of this issue.