This content has been marked as final. Show 1 reply
I haven't actually tried copy/paste deployment with system.transactions, so take this all with a grain of salt, trust nothing I say, and test thoroughly:
1110620 and higher ODP supports promoting transactions from local to distributed, as long as the first database you connect to is 11g or higher. If you connect only to a single 11g database, you should get a local transaction. If you then open a second connection inside the transaction, it gets promoted to distributed.
If you connect to only a single 10.2.0.4 database, you get a distributed transaction right off the bat, even though only a single db/connection is involved.
If you do end up with a distributed transaction, you need more than just the oramts* dll's. You need those dll's to help enlist in a distributed transaction, but you'll also need the Oracle MTS Recovery Service, as well as some database objects created on the database that sweep DD views for in doubt transactions in order to try to recover them by contacting the initiating client via utl_http. Otherwise you may end up with "lock held by in doubt transaction" errors if a txn goes in doubt.
If you set enlist=false, and use system.transactions, you get no transaction at all, and get the normal default auto-commit everything behavior, if I recall correctly.
Hope it helps,