Oracle: V$TRANSACTION

Join with v$session

v$transaction can be joined with v$session in order to determine which session relates to which transaction:

select 
  trx.addr,
  ses.taddr,  
  trx.ses_addr,
  ses.saddr,
  case when       trx.addr != ses.taddr then 'trx.addr != ses.taddr' end cmp_taddr
from
  v$transaction      trx                             left join
  v$session          ses on trx.ses_addr = ses.saddr;

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/transaction/join_session.sql

Join with v$sqlarea

Via v$session, it can further be joined to v$sqlarea to select, for example, the SQL statement text that is currently running in a session:

select /*+ ordered */
  round( (sysdate - to_date(trx.start_time, 'mm/dd/yy hh24:mi:ss')) * 24 * 60, 1 ) start_minutes_ago,
  trx.log_io         logical_io,
  trx.phy_io         physical_io,
  trx.cr_get         consistent_gets,
  trx.cr_change      consistent_changes,
  --
  trx.used_urec      undo_records_used,
  trx.used_ublk      undo_blocks_used,
  --
  ses.osuser,
  ses.username,
  --
  sql.sql_text,
  --
  trx.xidusn         undo_segment_number,
  trx.xidslot        slot_number,
  trx.xidsqn         sequence_number,
  --
  trx.ubafil         undo_block_address_filenum,
  trx.ubablk         uba_block_number,
  trx.ubasqn         uba_block_sequence,
  trx.ubarec         uba_record_number,
  trx.status,
  trx.start_scnb     system_change_number,
  trx.start_scnw     scn_wrap,
  trx.start_uext     start_extent_number,
  trx.start_ubafil   start_ubafile,
  trx.start_ubablk   start_uba_block,
  trx.start_ubasqn   start_uba_sequence_number,
  trx.start_ubarec   start_uba_record_nubmer,
--trx.ses_addr       session_address,
  trx.ptx            parallel_transaction
from
  v$transaction  trx                                      join
  v$session      ses on trx.ses_addr = ses.saddr     left join
  v$sqlarea      sql on ses.sql_id   = sql.sql_id
--where
--  trx.addr = '00000049DE4C7D98'
order by
  to_date(trx.start_time, 'mm/dd/yy hh24:mi:ss')
;

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/transaction/show-transactions.sql

Track transactions for sessions

v$transaction can be used to track undo generated by a session.

The following select statement shows the number of undo blocks and undo records for sessions as well as their rollback segment name:

select
   ses.sid, 
   ses.username,
   rlb.name          "Rollback segment name", 
   trx.start_date    "TRX start date",
   trx.used_ublk     "Undo blocks",
   trx.used_urec     "Undo recs"
from
   v$session     ses                          join
   v$transaction trx on ses.taddr  = trx.addr join
   v$rollname    rlb on trx.xidusn = rlb.usn;

Transaction ID

The values of xidusn, xidslot and xidsqn exhibit the three components of a transaction id.

xid is a raw that combines these three values:

select
    xidusn   undo_segement_number,
    xidslot  slot_number,
    xidsqn   sequence_number,
    xid
from
    v$transaction;

Columns related to parallel execution

Columns releated to parallel execution have the prefix PTX: