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 l2.id2 = l2.id2 ;

We don't have 600 users so I expect that many of the inactive sessions are orphans so I am looking at setting up a profile to expire session in active after a while.

do not do that, you'll kill the connection pool in the app server.

what you do want to do is reduce the size of the connection pool. Watch this video:

http://www.youtube.com/watch?v=xNDnVOCdvQ0


Now, to your problem at hand. Your developers are leaking connections - probably connections and cursors. They have some code like:

try
{
  grab a connection
  prepare statement
  execute statement  <<<==== this locks some resource
  close statement
  prepare statement2
  execute statement2 <===== this fails for whatever reason
  close statement2
  commit
  release connection back to pool
} 
catch (e exception)
{
 print out a silly message somewhere
}

I am at least 100% certain they have code similar to this - the error happens every now and then - just once is enough - and they fly over the release of the connection back to the pool. They have an outstanding transaction with locked resources associated with a connection handle they can never get access to again - it has gone out of scope.


In order to prove this - have them set their connection pool to a minimum size of ONE and a maximum size of ONE in test/qa and run a full regression. If that test cannot run forever without getting stuck (without running out of connections) - this bug exists in their code.


So, what I suggest is:

a) get a handle on your connection pool settings, always set MIN=MAX=some reasonable number (do not ever let these things grow dynamically, just have them create their connections when they start).

b) run that test in dev/qa - with connection pool = 1 until it works forever.

c) for now, use enterprise manager to see the blocking/blocker graphs and kill sessions that hold locks and block others that have been idle for some period of time. that really stinks but until the developers fix their very serious bug - it is "state of the art"


d) do not just kill idle sessions - they could be associated with a connection in a connection pool that just hasn't been used in a little while.