oracle 具有“提交时删除行”的全局临时表不包含任何数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2696519/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Global Temporary Table with "On commit delete rows" is not holding any data
提问by glasnt
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.
我在创建脚本中定义了一个全局临时表 (GTT)，使用提交时删除行的选项。我希望能够让不同的用户在 GTT 中看到他们自己的数据，而不是其他人会话的数据。这在我们的测试环境中非常有效。
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.
但后来，我部署了 GTT 作为客户端数据库功能更新的一部分。客户打电话给我，我既沮丧又担心，因为 GTT 不再持有任何数据，他们也不知道为什么。
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?
此问题发生在客户站点上，我们无法在内部重现。什么可能导致这种行为？
回答by Stano Januschke
ON COMMIT DELETE ROWS= data in one transaction
ON COMMIT DELETE ROWS= 一笔交易中的数据
ON COMMIT PRESERVE ROWS= data in one database session (one user with 2 sessions = 2 session = different content)
ON COMMIT PRESERVE ROWS= 一个数据库会话中的数据（一个用户有 2 个会话 = 2 个会话 = 不同的内容）
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):
如果 GTT 是用 定义的ON COMMIT DELETE ROWS，则在任何显式提交或隐式提交之后它将为空（= 隐式提交 = 在任何 DLL 命令之后，包括例如截断表、更改索引、添加分区、修改列、交换分区）：
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:
如果 GTT 是用 定义的ON COMMIT PRESERVE ROWS，它将保存数据直到会话结束：
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
回答by Damien_The_Unbeliever
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)
（我的经验是在 SQL Server 中，这是默认值，但我理解在 Oracle 中，默认值是保持事务打开直到显式提交。请注意，自 2000 年以来我就没有接触过 Oracle）
回答by Gary Myers
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)
我认为 Damien 是对的，并且有一个自动提交。我能想到的唯一其他选项是某种连接池问题（即选择是从单独的会话到插入完成的）
