List transactions

Owned by Arjen Visser
Last updated: Dec 30, 2013
1 min read
dbvrep> LIST TRANSACTIONS

This commands shows the Transaction ID of the SQL that is uncommitted on APPLY. The transaction id is the hex value of the combined columns XIDUSN, XIDSLOT, XIDSQN from v$transaction on the source database. The Transaction ID is also set in columns MODULE and ACTION from V$SESSION on the target database.

The transaction list will also include internal non committed transaction not related to the replication.

Run the list transaction command repeatedly with about 1-2 seconds apart. Only if the same transactions ID stays after repeatedly running this command, then this is worthy of investigation. 

The Transaction ID is the hex value of the combined columns XIDUSN, XIDSLOT, XIDSQN from v$transaction on the source database. The Transaction ID is also set in columns MODULE and ACTION from V$SESSION on the target database.

 To find the actual SQL statement run the following query and match the Transaction ID (run on source database):

SQL> select inst_id, to_char(XIDUSN, 'xxxxxxxx'), to_char(XIDSLOT,'xxxxxxxx'), to_char(XIDSQN,'xxxxxxxx') from gv$transaction;
Use v$transaction on Non RAC systems instead of gv$transaction

If this above transaction does not return any rows, then look into v$session on target database to find the Transaction ID in columns MODULE and ACTION

The list transactions command is most useful to be able to diagnose Uncommitted transactions

Please also see Command affected 0 row(s) conflict