Locking Information

select
sid, lt.name,
decode(lmode,
0, 'None',         
1, 'Null',         
2, 'Row-S (SS)',   
3, 'Row-X (SX)',   
4, 'Share',        
5, 'S/Row-X (SSX)',
6, 'Exclusive',     
to_char(lmode)) mode_held,
decode(request,
0, 'None',      
1, 'Null',         
2, 'Row-S (SS)',   
3, 'Row-X (SX)', 
4, 'Share',        
5, 'S/Row-X (SSX)', 
6, 'Exclusive',    
to_char(request)) mode_requested,
lt.id1_tag||':'||to_char(l.id1) ID1, lt.id2_tag||':'||to_char(l.id2) ID2,
ctime last_convert,
decode(block,
0, 'Not Blocking',
1, 'Blocking',    
2, 'Global',       
to_char(block)) blocking_others
from v$lock l , v$lock_type lt where
l.type = lt.type
order by lt.name,sid

Download Speedway to graphically plot the above script results.

Complete Database Performance Management