Here’s a handy bit of SQL which will find blocked sessions in your Oracle database:

1
2
3

select blocking_session, sid, serial#, sql_id, wait_class,seconds_in_wait 
from v$session where blocking_session is not NULL 
order by blocking_session;

This returns some useful info about the blocked sessions, including how long its been blocked for and the wait class event. Wait class events will give you an indication about the root cause of the hung session i.e. what it is waiting for. Burleson has a nice intro article on Oracle wait class events here - http://www.dba-oracle.com/concepts/query_wait_events.htm

Once you have the sql_id from the above SQL you can view the problematic SQL query like this:

1

select sql_text from v$sql where sql_id = 'insert sql_id here';

If you decide you need to kill the problematic session use the sid and serial# like this :

1

alter system kill session 'sid,serial#';