Oracle - Tracking events that lead to a deadlock Executing the below query from http://www.orafaq.com/node/854

SQL&gt; select s1.username || '@' || s1.machine
  2  || ' ( SID=' || s1.sid || ' )  is blocking '
  3  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  4  from v$lock l1, v$session s1, v$lock l2, v$session s2
  5  where s1.sid=l1.sid and s2.sid=l2.sid
  6  and l1.BLOCK=1 and l2.request &gt; 0
  7  and l1.id1 = l2.id1
  8  and l2.id2 = l2.id2 ;


I found that "SID=52 is blocking SID=730" and "SID=730 is blocking SID=52", a clear case of a deadlock. However all I could find from the v$session, v$sqlarea views is the SQL that is currently being blocked by both sessions. How do I see which SQL executed in both sessions that caused the locks.

We are using Oracle 11g (11.2.0.4) and recently noticing deadlock issues everywhere.
Deadlocks are automatically detected by the engine, and one of the statements will be rolled back.  You can find queries by examining alert.log file (it will have a reference to trace file where detailed deadlock information is stored). It's not gonna give you the full stack of queries per sessions that contribute to the deadlock though . 

The query you are using shows deadlock until it's resolved by the database.
You can try checking not just v$session.sql_id, but v$session.prev_sql_id (previous query executed within the session context). If you're running Enterprise Edition (and as far as I remember Diagnostic+Tuning Packs) you may examine v$active_session_history view.  

If you have an option to modify client applications that use the database, you can use DBMS_APPLICATION_INFO package to identify modules and actions that are being executed.