In 10g, there is a column in V$session called blocking_session to find the session id that is blocking.

Session 1) sid # 145

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
145

SQL> create table t ( x number );

Table created.

SQL> create unique index t_idx on t(x);

Index created.

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 1 );

1 row created.

Session #2) sid – 159

SQL> select userenv(‘sid’) from dual;

USERENV(‘SID’)
————–
159

— hung as 145 is blocking as i haven’t committed as the table t has primary key
SQL> insert into t values ( 1 );

Session 1) to find the blocking_session

— shows both session are active, the second session# 159 is blocked by 145

SQL> select sid, blocking_session, status from V$session where sid IN (159, 145);

SID BLOCKING_SESSION STATUS
———- —————- ——–
145 ACTIVE
159 145 ACTIVE

— another way of find sessions blocked the status of blocked session is VALID
SQL> select sid, blocking_session, seconds_in_wait from v$session where blocking_session_status = 'VALID';

SID BLOCKING_SESSION SECONDS_IN_WAIT
———- —————- —————
159 145 130

• General DBA
• Transaction