全局临时表从 Oracle 中的不同会话获取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2914732/
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 tables getting data from different session in Oracle
提问by Omnipresent
We have a stored procedure in Oracle that uses global temporary tables. In most of our other stored procedures, first thing we do is delete data from global temporary tables. However, in few of the stored procedures we do not have the delete's.
我们在 Oracle 中有一个使用global temporary tables. 在大多数其他存储过程中，我们做的第一件事是从全局临时表中删除数据。但是，在少数存储过程中我们没有delete's.
Are there any other options other than adding the delete statements? Can something be done on the Server side to forcefully delete data from those temporary tables when that SP is ran?
除了添加删除语句之外还有其他选择吗？可以在服务器端做些什么来在运行该 SP 时从这些临时表中强行删除数据吗？
the GTT's are defined with ON COMMIT PRESERVE ROWS;
GTT的定义为 ON COMMIT PRESERVE ROWS;
回答by APC
I think your title is misleading: the problem is not "getting data from different session", it is re-using the same session. Terminating a session always flushes a temporary table:
我认为您的标题具有误导性：问题不在于“从不同会话中获取数据”，而是重用同一个会话。终止会话总是刷新一个临时表：
SQL> conn apc
Enter password:
Connected.
SQL> create global temporary table tmp_23 (username varchar2(30))
2 on commit preserve rows
3 /
Table created.
SQL> insert into tmp_23 values (user)
2 /
1 row created.
SQL> commit
2 /
Commit complete.
SQL> select * from tmp_23
2 /
USERNAME
------------------------------
APC
SQL> conn apc
Enter password:
Connected.
SQL> select * from tmp_23
2 /
no rows selected
SQL>
From within a session there is no way to flush a temporary table which has PRESERVE ROWS except by deletion of truncation. There is no way to annotate a stored procedure in the manner you suggest. So I'm afraid that if you are experiencing the problem as you describe it you will have to bite the bullet and add the DELETE (or TRUNCATE) calls to your procedures. Or define the tables with DELETE ROWS; but that probably won't suit your processing.
在会话中，除了删除截断外，无法刷新具有 PRESERVE ROWS 的临时表。无法按照您建议的方式注释存储过程。因此，恐怕如果您遇到了您所描述的问题，您将不得不硬着头皮将 DELETE（或 TRUNCATE）调用添加到您的过程中。或者用 DELETE ROWS 定义表；但这可能不适合您的处理。
Incidentally, it seems like you are using temporary tables quite heavily. This is unusual in Oracle systems, because temporary tables are relatively expensive objects (all those writes to disk) and there is normally a more performant way approaching things: e.g. caching data in PL/SQL collections or just using SQL. It is common for developers coming from a non-Oracle background - especially SQL Server - to overuse temporary tables because they are used to that way of working.
顺便说一句，您似乎大量使用临时表。这在 Oracle 系统中是不常见的，因为临时表是相对昂贵的对象（所有那些写入磁盘的对象）并且通常有一种更高效的方法来处理这些事情：例如在 PL/SQL 集合中缓存数据或仅使用 SQL。对于来自非 Oracle 背景的开发人员（尤其是 SQL Server）来说，过度使用临时表是很常见的，因为他们已经习惯了这种工作方式。
