1

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"...

Improve this question

1 Answer 1

Reset to default
3

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

  • the usage of temporary tables is not supported in distributed transactions
  • there are a lot of ORA-00600 errors known when using global temporary tables in XA transactions. Even block corruption ca happen
  • there are some types of XA transaction where the usage of temporary tables will work. One of such situations is described in the article:

    A global temporary table can be safely used if there is only single branch transaction at the database using it, but if there are loopback database links or XA transactions involving multiple branches, then problems can occur including block corruption (...)

  • this restriction on temporary table is documented in the 10g manual . It is not documented in the 11g manual but this is considered as a bug.
This restriction on temporary table is contained in the 12c manual (you supplied the link).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.