Page Comparison

When a conflict arises, especially the "0 rows affected" conflict, it may be useful to view the opened uncommitted transactions on APPLY. 

The This can be done with the list transactions command:

dbvrep> LIST TRANSACTIONS

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

We are only interested in the INSERT statements.

 the List transactions command.

When the Transaction ID of the INSERT statement is found, then the record can be committed with:

dbvrep> apply commit transaction xxx

...

This will then cause the update statement to proceed as the insert statement is now committed on the target database.

 The transaction can also be rolled back with command:

dbvrep> apply rollback transaction xxx

If uncommitted transactions happen on a frequent basis, please Contact Support

...