RE: Deceptive commit after select from dblink.

Whether we call it distributed transaction or not, a simple query over dblink will require transaction to be created on the local and the remote database, this is needed for transaction recoverabilty if there is any failure on local ore remote database,  but to make it work for read only standby databases, I suspect Oracle changed remote database to only create transaction in the transaction state array but no transaction will appear in the undo segment transaction table unless if there is any DML.

I believe, it is for this reason, a commit on the local database trigger rollback on the remote database first when there were no DML's on the remote database since commit with session having an entry in transaction state object probably require corresponding undo segment entries. By the way, I didn't observe rollback or commit statistic values going up on the remote database session statistics, it is weird that it appeared in the trace file only.

Whenever any DML happens on the remote database over dblink, transaction table in the undo segment will have an entry and hence commit or rollback on the local database can have similar effect on the remote database too.

Thanks,
 Sai
http://sai-oracle.blogspot.com

--
http://www.freelists.org/webpage/oracle-l