I have couple of statements, the pseudo code would look something like this:
insert into local_table
crease savepoint sp1
insert into remote_db //using db_link
update local_table2
delete from local_table
commit
Now I'm kinda confused as to the insert into remote_db statement. Would there be any chance that the commit which is being applied has different affect on local_db and on remote_db?
The problem statement is kind of complex. The script which copies data from local_db to remote_db is producing duplicates. After performing an investigation, that's the only place which looks suspicious but I am not sure. Would really appreciate if someone can shed light on Oracle's COMMIT
If you are asking whether the commit could potentially cause duplicate rows, no, that's not possible.
Given the way that distributed transactions take place, it is possible that that transaction would not be committed at all on the remote database (in which case it would be an in-doubt distributed transaction that the remote DBA would likely need to resolve). But if the transaction is committed successfully, it's going to be committed correctly. It's not possible that some rows would get committed and others wouldn't or that duplicate rows that didn't exist prior to the commit would be created by the act of committing.