Global Temporary Table with "On commit delete rows" is not holding any data

I have a global temporary table (GTT) defined in a creation script using the option to delete rows on commit. I wanted to be able to have different users see their own data in the GTT and not the data of other people's sessions. This worked perfectly in our test environment.开发者_高级运维

But then, I deployed GTT as part of an update to functionality to a client's database. The client called me up all upset and worried, because the GTT wasn't holding any data any more, and they didn't know why.

Specifically, if someone did:

insert into my_GTT (description) values ('Happy happy joy joy')

the database would respond:

1 row inserted. 

However, if the same end user tried:

select * from my_GTT

The database would respond:

0 rows returned.

This issue is happening on the client site, and we can't reproduce it in house. What could be causing this behavior?

ON COMMIT DELETE ROWS = data in one transaction

ON COMMIT PRESERVE ROWS = data in one database session (one user with 2 sessions = 2 session = different content)

If GTT is defined with ON COMMIT DELETE ROWS, it would be empty after any explicit commit or implicit commit (= implicit commit = after any DLL command including for example truncate table, alter index, add partition, modify column, exchange partition):

CREATE GLOBAL TEMPORARY TABLE GTT__TEST (A NUMBER) ON COMMIT DELETE ROWS;
INSERT INTO GTT__TEST VALUES (1); 
SELECT * FROM GTT__TEST; -- 1 ROW; 
COMMIT; -- commit = delete rows
SELECT * FROM GTT__TEST; -- 0 ROWS; 
INSERT INTO GTT__TEST VALUES (1); 
SELECT * FROM GTT__TEST; -- 1 ROW; 
ALTER TABLE GTT__TEST MODIFY A NOT NULL; -- DLL = commit = delete rows
SELECT * FROM GTT__TEST; -- 0 ROWS 

If GTT is defined with ON COMMIT PRESERVE ROWS, it would hold data till end of session:

DROP TABLE GTT__TEST; 
CREATE GLOBAL TEMPORARY TABLE GTT__TEST (A NUMBER) ON COMMIT PRESERVE ROWS;
INSERT INTO GTT__TEST VALUES (1); 
SELECT * FROM GTT__TEST; -- 1 ROW 
COMMIT; 
SELECT * FROM GTT__TEST; -- 1 ROW

Do you have some setting turned on in your target environment where each statement is auto-committing?

(My experience is in SQL Server, where such is the default, but I understand in Oracle, the default is to keep the transaction open until an explicit commit. Mind, I haven't touched Oracle since ~2000)

I think Damien is right and there is an autocommit. The only other option I can come up with is some sort of connection pool issue (ie the select is being done from a separate session to the insert)