How to find blocking session?

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

Transaction isolation level (Serial and Read-committed)

The following 2 links explains transaction isolation.

http://www.acs.ilstu.edu/docs/Oracle/server.101/b10743/consist.htm
http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html