Orphaned Transaction records

I recently was investigating an issue on growing S_DOCK_TXN_LOG table and came across the Oracle support Identified#477816.1. This issue was due to the Orphan records in S_DOCK_TXN_LOG

Details from Technote -

Siebel Systems introduced the concept of multi-operation transactions. For a multi-operation transaction there is one parent and many children. The child transactions have PAR_TXN_ID set to the parent transaction. In some cases, if the parent transaction is not present, Transaction Processor cannot delete the child transactions. These child transactions are then considered to be orphaned transactions.


To determine whether there are any orphaned transactions, run the following SQL query:

Oracle

SELECT B.TXN_ID, B.ROW_ID, B.PAR_TXN_ID, B.OPERATION, B.ITEM_NAME
FROM S_DOCK_TXN_LOG A, S_DOCK_TXN_LOG B
WHERE A.ROW_ID(+) = B.PAR_TXN_ID
AND A.ROW_ID IS NULL;

DB2 or MS SQL Server

SELECT B.TXN_ID, B.ROW_ID, B.PAR_TXN_ID, B.OPERATION, B.ITEM_NAME FROM S_DOCK_TXN_LOG A
RIGHT OUTER JOIN S_DOCK_TXN_LOG B ON B.PAR_TXN_ID = A.ROW_ID
WHERE A.ROW_ID IS NULL

The orphan records can be deleted using the Endtxnutl Utility or manually deleted.