Find all transactions on instance

Sometimes you want to know what transactions are actually on your db instance(e.g. when db works slow).
You can do it using the following query:

SELECT s.taddr,
       s.sid,
       s.serial#,
       s.username,
       s.SCHEMANAME,
       s.machine,
       s.status,
       s.lockwait,
       t.start_time
  FROM v$transaction t
  INNER JOIN v$session s ON t.addr = s.taddr;

v$transaction contains data about each transaction existing on the instance when v$session contains all sessions.

As a result you will get all transactions on instance with info about corresponding sessions.

Find out which sessions blocks another sessions

Moreover you can check if any session wait for another. You can easy find it by blocking_sesssion column in v$session view.

SELECT sid blocked_session_id,
       seconds_in_wait,
       blocking_session blocking_session_id
  FROM v$session
  WHERE blocking_session IS NOT NULL
  ORDER BY blocking_session;

Find out on which sql statement session was blocked

To find out this sql you can join to previous query v$sql view by sql_id column in v$session.

SELECT s.sid blocked_session_id,
       s.seconds_in_wait,
       s.blocking_session blocking_session_id,
       sq.sql_fulltext
  FROM v$session s
  LEFT JOIN v$sql sq ON sq.sql_id = s.sql_id
  WHERE blocking_session IS NOT NULL
  ORDER BY blocking_session;

Links:

https://stackoverflow.com/questions/1299694/oracle-how-to-find-out-if-there-is-a-transaction-pending
http://www.dba-oracle.com/t_find_blocking_sessions.htm
Reference – V$SESSION
Reference – V$TRANSACTION
Reference – V$SQL