Global Temporary Tables

[gonzo044]

Hi all. Does anybody know of any way to see all of the data in a global temporary table, regardless of session? I have a ColdFusion app that connects to Oracle and uses global temporary tables to build it's data. It is a session level temp table. The session always remains open (I've monitored through v$session view), but for no apparent reason the data seems to go away for that given session at random times. We will click a button to perform a query and it will work the 1st and 2nd times or so, then the 3rd or 4th time it's clicked, the data is suddenly gone. The developer insists (I have not personally seen the code) that there is no delete/truncate being issued here, so we'll have to say that's not what's causing it, and like I said before, the session remains open on the Oracle end.

Does anybody have any experience with the data seemingly truncating for no reason? Any ideas? I was hoping to ses all of the data so I could monitor exactly when the data was deleting so we can pinpoint the problem.

Thanks.

[patel_dil]

I dont think you can view data for all sessions in Global Temporary Tables. The basic purpose of Global Temporary Tables is to view session specific Data. Maybe you have created your Global Temporary Table as Transaction specific in which case, The session will lose data after every commit or rollback.

[gonzo044]

I think we've figured this out. Apparently ColdFusion uses a single connection for multiple users. Global Temporary tables can only have one user using it at a time. Since we have multiple users using the same connection/session, it is confusing the temp table and one user's data is gone after another user executes a query through this same connection.

We need to have some sort of temporary table functionality as some of these queries are very large and ColdFusion cached tables can't handle this much data in an acceptable amount of time. The only other thing we can think of is creating a regular table to use as a temporary repository for data which has some sort of unique userid column so a given user only queries his/her data. I don't really like the idea of inserting and deleting thousands of records constantly in a regular table. The table would reside in a locally managed tablespace, so according to Oracle there should be no performance degradation since all extent management would be internal.

Does anybody have any other ideas or will I be forced to use a regular table as my temporary table?

Thanks for your help.

[jmodic]

I don't think you've realy figured it out. You've actually got it all wrong!

Global Temporary tables can only have one user using it at a time.

There could be zillion users using the same global temporary table at the same time, absolutely no limitation in that. And each of them will see only the data of his own session in it. It is the very nature of GTT, it is as simple as that.

I think we've figured this out. Apparently ColdFusion uses a single connection for multiple users. Since we have multiple users using the same connection/session, it is confusing the temp table and one user's data is gone after another user executes a query through this same connection.

I know nothing about ColdFusion, but I think you've got that wrong too. It might use a single physical connection for multiple users, but each of those users is having his own session in the database. And each of those session is having its own "instance" of that glogal temporary table they are all using. Nothing realy "confuse" Oracle when it comes to GTT, they behave exactly as they should.

As patel_dil has allready mentioned, you should check for what duration scope your GTT was created - SESSION or TRANSACTION. Execute the following SQL and let us know what is the result:

select table_name, temporary, duration from user_tables
where table_name = _my_GTT_table_;

[gonzo044]

I guess I wasn't quite clear enough in my other reply. To start, they are session level global temporary tables, so I know it's not that the data is being deleted after transaction. When I meant users were using the same connection, that meant they actually are using the same session. ColdFusion connects through an ODBC connection to Oracle. Each connection is a session in Oracle. If a user logs off of the ColdFusion app, it leaves that Oracle session open so it can reuse the connection. So if a new user (or the same user) would connect soon after, ColdFusion will connect that user to Oracle using the same session as the previously described user. So, as far as Oracle can see, it's still the same session connected, when it's actually a different user connected.

I understand the concept of global temporary tables and how they work. I know that only a single session can see that session's data. What's happening though are 2 things: 1) Sometimes a user will see data that he/she is not supposed to (like it's from a different user's data, this I would assume is from using a session that someone else was connected to previously) and 2) Without issuing a truncate on a table and always maintaining a connection/session with the database, the user's data goes away in the table.

Thanks for the help and keep the suggestions coming!