Deadlocks in database

How to find the blocking/blocked sessions?
There are a number of reasons why a database will hang or appear to hang. One cause can be due to 2 or more sessions blocking each other when attempting to access the same object in incompatible modes. How to identify who is waiting and who is blocking?

You can use some useful scripts here.
Method 1 - SQLPLUS (1)
column "ORACLE USER" format a11
column SERIAL# format 9999999
column "OS USER" format a8

select distinct substr(s.username,1,11) "ORACLE USER", p.pid "PROCESS ID", s.sid "SESSION ID", s.serial#, osuser "OS USER", p.spid "PROC SPID", s.process "SESS SPID", s.lockwait "LOCK WAIT"
from v$process p, v$session s, v$access a
where a.sid=s.sid and
p.addr=s.paddr and
s.username != 'SYS' and
a.sid in (select l.sid from v$lock l
where (id1,id2) in
(select b.id1, b.id2 from v$lock b where b.id1=l.id1 and
b.id2=l.id2 and b.request>0));

Another script (2)
set linesize 132
pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
select nvl(S.USERNAME,'Internal') username, nvl(S.TERMINAL,'None') terminal, L.SID','S.SERIAL# Kill, U1.NAME'.'substr(T1.NAME,1,20) tab,
decode(L.LMODE,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) lmode,
decode(L.REQUEST,1,'No Lock', 2,'Row Share', 3,'Row Exclusive', 4,'Share', 5,'Share Row Exclusive', 6,'Exclusive',null) request
from V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1
where L.SID = S.SID and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5

Another method - 3 (using oracle script)
SQL>@$ORACLE_HOME/rdbms/admin/utllockt.sql

if you encounter missing table error, run this scripts
SQL>@$ORACLE_HOME/rdbms/admin/catblock.sql

Method 2 - OEM
Oracle Enterprise Manager provides an easy way to detect blocking sessions. For 10g, go to Performance -> Blocking session. Simply select the first node and click the "kill" button