Open Database Transactions Effecting Oracle Golden Gate

Oracle Golden Gate Replication will not propagate transactions to the destination which have NOT been committed on the source yet. However Oracle Golden gate will write the open transaction data to the dirtemp directory in the home directory. This data will then be read back later when the transaction is committed and needs to be propagated to the destination. This is especially true when bulk transactions are run, processing a large account of the uncommitted data, which then has to be written temporarily to the dirtemp location.

In cases such as this and others you may need to investigate these open transactions which are awaiting a commit. The script below will identify the SID, SCHEMANAME, etc providing info on these open transactions. Also the USED_UBLK and UREC can be used to gauge the the amount of changes made by the statement.

Open Database Transactions effecting GoldenGate Query

-- Open Database Transactions effecting GoldenGate

COL start_time FOR A20
COL sid FOR 99999
COL serial# FOR 999999
COL username FOR A20
COL status FOR A10
COL schemaname FOR A10
COL process FOR A10
COL machine FOR A15
COL program FOR A30
COL module FOR A35
COL logon_time FOR A20

SELECT t.start_time,s.sid,s.serial#,s.username,
s.status, s.schemaname, s.process,s.machine,
s.program, s.module, used_ublk, used_urec,
TO_CHAR(s.logon_time,'mon-dd-yyyy HH24:MI:SS') logon_time
FROM v$transaction t, v$session s
WHERE s.saddr = t.ses_addR
ORDER BY start_time;

Go to Goldengate for a complete list of articles on Golden gate replication.

Troubleshooting Series

GoldenGate: Skipping Transactions
GoldenGate: Finding Open Database Transactions
GoldenGate: How to use handle Collisions correctly?


  1.'Tharimana S Raju says

    Hi Mr. Natik Ameen,
    I first refer your writings on blog about GoldenGate before searching anywhere. Thank you so much. I am wondering how GoldenGate handles Source Updates into Hadoop HDFS. I mean, what mechanism or process GoldenGate uses to update (not Append) source changed records on HDFS? Thanks in advance.

Leave a Reply

Your email address will not be published.