This is causing issues as our procedures use session based global temp tables; need that for good reason. The actions of one web client interferes with that of another. Why is the same Oracle session reused by a separate client.
There is connection pooling with Web logic in place. I have printed the following to confirm that indeed 2 clients are being assigned same oracle session.
SELECT SYS_CONTEXT ('USERENV', 'INSTANCE'),
SYS_CONTEXT ('USERENV', 'SID'),
SYS_CONTEXT ('USERENV', 'SESSIONID')
FROM DUAL;
How to ensure each client gets a different session (not HTTP session, but Oracle session)? Is this something at Weblogic level that needs to be modified?
If you're using connection pooling in the middle tier, then once your middle tier code closes a connection, it is returned to the pool and is available to be used by another middle tier session. If you are trying to use global temporary tables that store data past the point that the middle tier closes a connection, you're doing something wrong.
You could design and build your middle tier so that it doesn't use connection pooling and so that each middle tier session opens a private database connection that is used only by that user. That would generally be a horrible idea, however, since you would generally end up spending oodles of time opening and closing database connections, you'd end up with thousands of orphaned database connections when an application user simply navigates away from the site rather than explicitly logging out, and it would become very difficult to do things like have the same user serviced by different app servers at different points in time for load balancing purposes.
A better approach would be to get rid of the global temporary table and store whatever data you need in a permanent table that includes your unique session ID as part of the key. You'd need to write code to purge the data at some point but that shouldn't be terribly difficult.