1) utllockt.sql found in $ORACLE_HOME/rdbms/admin does just this...


WAITING_S LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
--------- -------------- -------------- -------------- ----------------- --------
20 None
18 Transaction Exclusive Exclusive 65568 28615
19 Transaction Exclusive Exclusive 65568 28615

it prints out a lockee-locker graph. You might have to run catblock.sql before using this.

2) v$session has a column, last_call_et -- that shows how long that session has been blocked in their current call...

3) well, generally this is an "application issue". Think about it -- there is no general purpose solution. Given the nature of your application -- this could be a very natural, expected situation (eg: you have a conference room scheduling system and a very very very popular conference room).

On the other hand, it could be a missing index on a foreign key causing a child table to become fully locked whenever the parent table is deleted from or has its primary key updated.

Or it could be a serious application design flaw and the only fix is to correct the flawed application logic.

Killing the blockee will move everyone up a level perhaps, but it certainly won't "fix" anything (you have a really mad end user -- you killed them, and you probably just promoted the first blocked session into the "blockee" session now)