On Oracle 11gR2, I've recently encountered a very interesting situation involving a blocked (but idle!) MERGE statement that hangs on a "SQL*Net message from client" event, causing subsequent, concurrently executed MERGE statements to block on the first statement via "cursor: pin S wait on X" events. In Oracle Enterprise Manager, the following can be observed:

This situation turns even more severe, as the above Session-ID 1204 cannot be killed with either:

alter system kill session 'sid,serial#'; alter system kill session 'sid,serial#' immediate;

Our DBA is sometimes able to kill the operating system process, but often, the whole database needs to be restarted. Luckily, thus far, only on a test system, never in production.

Note:

I'm aware this is probably a similar issue as reported in this rather vague question: Oracle updates/inserts stuck, DB CPU at 100%, concurrency high, SQL*Net wait message from client. I'll still report it again, as I have a clear reproduction path, which I'll report as an answer.