Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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