There are various situations where in a session of an user would hang due to various environmental situations. It could be due to a shared resource, or inefficient queries on tables without indexes, a massive update statement could be taking its toll, or an user could have placed a lock on some rows e.t.c. In simple terms, it could be this - "when any user accesses a shared data resource held by another one" there is a hang.
Oracle has this very information stored. in In such situations, the V$Session view in the row_wait_file# and row_wait_block# come handy. They help you to determine the session that is waiting on a shared resource. The file number and the block number can be used toe determine the table on which the wait is happening.
Below is the code snippet:
Column host format a6;
Column username format a10;
Column os_user format a8;
Column program format a30;
Column tsname format a12;
b.row_wait_file# = a.file_id
c.file_id = row_wait_file#
row_wait_block# between c.block_id and c.block_id + c.blocks - 1
row_wait_file# <> 0
The username will tell you the user who is waiting for a lock to be released at the block number.
To find out the what is blocking use the code snippet below,
Select blocking_session, sid, serial#, wait_class,seconds_in_wait From v$session
where blocking_session is not NULLorder by blocking_session;