I'm confused about the compatibility of Oracle XA with global temporary tables. I've read several places on the internet and in the oracle documentation that say "Distributed transactions are not supported for temporary tables." but it doesn't seem 100% clear whether "not supported" is referring to the creation of a temp table or its usage.
I understand that issuing any DDL will implicitly commit the current transaction with Oracle, and that creating a temporary table is DDL so that can't be done inside an XA transaction. However, does this mean that I can't even insert into an already created temporary table in an XA transaction?
I performed a test inserting and querying a temp table inside an XA (distributed) transaction and it worked fine but I'm afraid to build an application around the results of that one test when the documentation says "not supported"…
Best Answer
For me the wording "Distributed transactions are not supported for temporary tables" is clear: In a distributed transaction you should not use statements that involve temporary tables. This means you should not even insert into an already created temporary table in an XA transaction. Of course it can happen that it works but nevertheless it is not supported so you should not use it.
I found Note 1051543.1 "Known issues with Global Temporary Tables and Distributed or XA transactions" on Oracle Metalink stating