Global temporary table labeled "On commit delete rows" does not contain any data
I have a global temporary table (GTT) defined in the create script using the option to delete lines in commit. I wanted different users to be able to see their own data in GTT and not other people's data. This worked great in our test environment.
But then I deployed GTT as part of an upgrade for the client database functionality. The client made me all upset and worried because the GTT no longer contained any data and they didn't know why.
Specifically, if someone did:
insert into my_GTT (description) values ('Happy happy joy joy')
the database will respond:
1 row inserted.
However, if the same end user tried:
select * from my_GTT
The database will respond:
0 rows returned.
This issue occurs on a client site and we cannot reproduce it at home. What can cause this behavior?
a source to share
Do you have some setting in your target environment where every statement is automatically committed?
(My experience with SQL Server where this is the default, but my understanding is that Oracle supports a transaction before explicit commit by default. Reason, I haven't touched Oracle since ~ 2000)
a source to share
ON COMMIT DELETE ROWS
= data in one transaction
ON COMMIT PRESERVE ROWS
= data in one database session (one user with two sessions = 2 sessions = other content)
If GTT is defined with ON COMMIT DELETE ROWS
, it will be empty after any explicit commit or implicit commit (= implicit commit = after any DLL command including, for example, truncate table, change index, add section, change column, swap section):
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 will keep the data until the end of the 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
a source to share