Could you elaborate on what you mean by 'temporary transaction table'?
The database connections aren't tight to a user session. But in your application you should get a connection from the datasource. Perform your UI transaction on that connection and then 'close' it, which means that you give it back to the pool.
I think you (or your developers) should rethink what you try to accomplish and then build it accordingly.
For instance, if you would program this as a standalone application. What would you do? Create a connection, perform your transaction(s) and then close your connection?
Then translate that to the use within Weblogic. And probably you'll find that you could do this pretty much 1:1 in the same way.
Thank you for your reply, Martien.
We are using SOA Suite. We want to make several calls to Oracle SOA Suite APIs and contain all of those calls in an XA transaction. We need to do this in order to commit or rollback all of the calls at once rather than commit one call at a time.
It turns out that, in that situation, SOA Suite uses a temporary transaction table to store some transaction information. From time to time we get an error when calling one of the APIs contained in our XA transaction. The error is an ORA-14450, "attempt to access a transactional temporary table already in use".
We speculate that this might be caused by the fact that a single user can use several actual database connections if we use a connection pool. That's why we wan to try a run without using a connection pool. We're not sure that will help us, unfortunately ... but that is why we want to do this.
Hope this helps !!
I don't think that will help. I'd check the database driver settings (XA driver, LLR settings, COmmit settings, etc.)
What you also could try is to create a separate datasource expecially for this purpose and check the pin to thread check box.
Next create a workmanager that has a max thread constraint based on the datasource https://docs.oracle.com/middleware/1221/wls/WLACH/pagehelp/J2EEappworkmaxthreadsconstraintconfigtitle.html . Create a new folder in SOASuite and set that workmanager on that folder.
Then deploy your composite to that folder.
It will make sure that there are no more threads running that composite then defined in the maxthread constraint that is based on the datasource. In essence the number of threads is determined from the max connections in the datasource. Each thread gets it's own database connections and keeps it. Therefor it is important to have a work manager that has no more threads than there are connections in the datasource. That will make sure that the code is running in a thread will use the same connection from the datasource.
This only works when there are no dehydrations. Which, will by the way, break your transaction anyway.