Protect Oracle Distributed Transactions from Network Failures

Question

I am synchronizing a table in a local database with data from a table in a database on the opposite side of the earth using distributed transactions. Networks connect via vpn over the Internet. In most cases, it works fine, but when the connection is interrupted during an active transaction, the lock prevents the task from starting again. I cannot kill a blocking session. Attempting to do this simply returns "ORA-00031: Session marked for kill", and it is not actually killed before I loop the local database.

Sync job basically

CURSOR TRANS_CURSOR IS    
SELECT COL_A, COL_B, COL_C    
 FROM REMOTE_MASTERTABLE@MY_LINK    
 WHERE UPDATED IS NULL;    

BEGIN    
  FOR TRANS IN TRANS_CURSOR LOOP    

    INSERT INTO LOCAL_MASTERTABLE    
      (COL_A, COL_B, COL_C)    
    VALUES    
      (TRANS.COL_A, TRANS.COL_B, TRANS.COL_C);    

    INSERT INTO LOCAL_DETAILSTABLE (COL_A, COL_D, COL_E)
      SELECT COL_A, COL_D, COL_E
      FROM REMOTE_DETAILSTABLE@MY_LINK
      WHERE COL_A = TRANS.COL_A;

    UPDATE REMOTE_MASTERTABLE@MY_LINK SET UPDATED = 1 WHERE COL_A = TRANS.COL_A;    
  END LOOP;    
END;

, , . Oracle Standard Edition One, Enterprise .

Answer 1

-, ? Oracle , Streams, , , . , , , .

, , Oracle , , . , . DBA_2PC_PENDING. .

Answer 2

. DML , , , Oracle . , . ( Oracle, , .)

, , , SQL-. , 20 , . ( , , .)

BEGIN
    INSERT INTO LOCAL_MASTERTABLE    
      (COL_A, COL_B, COL_C)    
    SELECT COL_A, COL_B, COL_C    
     FROM REMOTE_MASTERTABLE@MY_LINK
     WHERE UPDATED IS NULL;    

    INSERT INTO LOCAL_DETAILSTABLE (COL_A, COL_D, COL_E)
      SELECT REMOTE_DETAILSTABLE.COL_A, REMOTE_DETAILSTABLE.COL_D, REMOTE_DETAILSTABLE.COL_E
      FROM REMOTE_DETAILSTABLE@MY_LINK
        INNER JOIN (SELECT COL_A FROM REMOTE_MASTERTABLE@MY_LINK WHERE UPDATED IS NULL) TRANS
        ON REMOTE_DETAILSTABLE.COL_A = TRANS.COL_A;

    UPDATE REMOTE_MASTERTABLE@MY_LINK SET UPDATED = 1 WHERE UPDATED IS NULL;
END;
/