具有“提交时删除行”的全局临时表没有保存任何数据

发布于 2024-08-29 14:04:41 字数 519 浏览 8 评论 0原文

我在创建脚本中定义了一个全局临时表 (GTT)，使用提交时删除行的选项。我希望能够让不同的用户在 GTT 中看到自己的数据，而不是其他人会话的数据。这在我们的测试环境中完美运行。

但随后，我将 GTT 部署为客户数据库功能更新的一部分。客户打电话给我，既沮丧又担心，因为 GTT 不再保存任何数据，他们也不知道为什么。

具体来说，如果有人这样做：

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

数据库会响应：

1 row inserted.

但是，如果同一个最终用户尝试：

select * from my_GTT

数据库会响应：

0 rows returned.

此问题发生在客户端站点上，我们无法在内部重现它。什么可能导致这种行为？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情归归情 2024-09-05 14:04:42

我认为达米安是对的，并且有一个自动提交。我能想到的唯一其他选项是某种连接池问题（即选择是从单独的会话到插入完成的）

回复收藏 0 原文

掩耳倾听 2024-09-05 14:04:41

ON COMMIT DELETE ROWS = 一个事务中的数据

ON COMMIT PRESERVE ROWS = 一个数据库会话中的数据（一个用户有 2 个会话 = 2 个会话 = 不同的内容）

如果定义了 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

如果 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

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

回复收藏 0 原文