Find blocking sessions
Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another sessions wants to update the same data. This will block the second until the first one has done its work.
From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You'll often have to identify these sessions in order to improve your application to avoid as many blocking sessions as possible.
Recipe #1 - find blocking sessions with v$session
SELECT s.blocking_session, s.sid, s.serial#, s.seconds_in_wait FROM v$session s WHERE blocking_session IS NOT NULL
Recipe #2 - find blocking sessions using v$lock
SELECT l1.sid || ' is blocking ' || l2.sid blocking_sessions FROM v$lock l1, v$lock l2 WHERE l1.block = 1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l1.id2 = l2.id2
Recipe #3 - blocking sessions with all available information
The next query prints a few more information, it let's you quickly see who's blocking who. Run this query and you can immediately call the colleague who's locking your table:
SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status FROM v$lock l1, v$session s1, v$lock l2, v$session s2 WHERE s1.sid=l1.sid AND s2.sid=l2.sid AND l1.BLOCK=1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l1.id2 = l2.id2
Recipe #4 - identifying blocked objects
The view v$lock we've already used in the queries above exposes even more information. There are differnet kind of locks - check this site for a complete list: http://download.oracle.com/docs/cd/B13789_01/server.101/b10755/dynviews_1123.htm#sthref3198
If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other. Unless one sessions finished (commit or rollback), you'll never have to wait forever.
The following queries shows you all the TM locks:
SELECT sid, id1 FROM v$lock WHERE TYPE='TM'
The ID you get from this query refers to the actual database object which can help you to identify the problem, look at the next query:
SELECT object_name FROM dba_objects WHERE object_id=20127
There queries should help you to identify the cause of your blocking sessions!