CODEWITHSUNDEEP
oracle-databaseoracle10gtemp-tables
glasnt
glasnt

Reputation: 2973

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?

Upvotes: 3

Views: 10821

Answers (3)

Stano Januschke
Stano Januschke

Reputation: 31

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

Upvotes: 3

Gary Myers
Gary Myers

Reputation: 35401

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)

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

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)

Upvotes: 2

Related Questions