I'm trying to identify blocking sessions using the following query:
SELECT v1.sid esid, v1.blocking_session bsid, v1.process, v1.wait_class, v1.event, v1.sql_id, v1.seconds_in_wait waitss, v2.blocking_session bsid2, v2.process, v2.sql_id, v2.wait_class, v2.event from v$session v1, v$session v2 where v1.blocking_session is not null and v1.blocking_session = v2.sid order by waitss desc
Here is sample output:
ESID BSID PROCESS WAIT_CLASS EVENT SQL_ID WAITSS BSID2 PROCESS_1 SQL_ID_1 WAIT_CLASS_1 EVENT_1 2707 2313 8526 Application enq: TX - row lock contention 05v90pbavkptk 11 (null) 28152 (null) Idle SQL*Net message from client
Which indicates that the blocking session is idle. Maybe I'm missing something but I don't understand how an idle session that is not executing any statement can be blocking anything.