SQL 如何在 Oracle 中创建一个临时表作为另一个表的副本？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27093937/
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
How to create a temporary table as copy of another table in Oracle?
提问by the_dude
I already tried this:
我已经试过了：
CREATE GLOBAL TEMPORARY TABLE tempTable AS
SELECT * FROM realTable;
But then tempTable has only the structure of realTable, but not the elements themselves.
但是 tempTable 只有 realTable 的结构，而没有元素本身。
回答by APC
"But then tempTable has only the structure of realTable, but not the elements themselves."
“但是 tempTable 只有 realTable 的结构，而没有元素本身。”
What makes a global temporary table temporary is that the data is transient. Firstly, the data is only visible within the session which inserts it; any other session will see an empty table. Secondly, the data can persist for either a transaction or the session, depending on the ON COMMIT clause; the default is ON COMMIT DELETE ROWS. Find out more.
使全局临时表临时的是数据是瞬态的。首先，数据仅在插入它的会话中可见；任何其他会话将看到一个空表。其次，数据可以为事务或会话持久化，这取决于 ON COMMIT 子句；默认值为 ON COMMIT DELETE ROWS。 了解更多。
Now the thing is, a DDL statement in Oracle issues two commits, one before and one after the statement in question. So a DDL statement is a complete, discrete transaction. Hence this ...
现在的问题是，Oracle 中的 DDL 语句发出两次提交，一次在相关语句之前，一次在其之后。所以 DDL 语句是一个完整的、离散的事务。因此这...
CREATE GLOBAL TEMPORARY TABLE tempTable AS
SELECT * FROM realTable;
... is a transaction and, as it doesn't specify the ON COMMIT clause, it will apply the default which is DELETE ROWS. So an empty table is the expected behaviour.
... 是一个事务，因为它没有指定 ON COMMIT 子句，它将应用默认值 DELETE ROWS。所以一个空表是预期的行为。
The solution is simple: specify the ON COMMIT statement with session-level retention:
解决方案很简单：指定具有会话级保留的 ON COMMIT 语句：
SQL> select count(*) from t23;
COUNT(*)
----------
11
SQL> create global temporary table gtt23
2 as select * from t23
3 /
Table created.
SQL> select count(*) from gtt23;
COUNT(*)
----------
0
SQL> drop table gtt23;
Table dropped.
SQL> create global temporary table gtt23
2 on commit preserve rows
3 as select * from t23
4 /
Table created.
SQL> select count(*) from gtt23;
COUNT(*)
----------
11
SQL>
Generally, I think that a policy of CREATE GLOBAL TEMPORARY TABLE using SELECT * FROM indicates a misunderstanding of the construct. GTTs in Oracle are permanent data structures; only the records are temporary. They are not disposable objects like temporary tables in T-SQL. If that's the sort of thing you want, you should probably be using PL/SQL collections instead. Find out more.
通常，我认为使用 SELECT * FROM CREATE GLOBAL TEMPORARY TABLE 的策略表明对构造的误解。Oracle 中的 GTT 是永久性数据结构；只有记录是临时的。它们不是像 T-SQL 中的临时表那样的一次性对象。如果这是您想要的类型，您可能应该改用 PL/SQL 集合。了解更多。
回答by Justin Cave
Global temporary tables can have either transaction-level scope or session-level scope. The default is to have transaction-level scope which means that the data disappears after the transaction completes. If you do a CREATE TABLE AS SELECTto create your global temporary table, the data will be inserted but, since CREATEis DDL, the data will be removed as soon as the statement completes.
全局临时表可以具有事务级作用域或会话级作用域。默认是具有事务级作用域，这意味着数据在事务完成后消失。如果您执行 aCREATE TABLE AS SELECT创建全局临时表，数据将被插入，但由于CREATE是 DDL，一旦语句完成，数据将被删除。
One option would be to create the structure using a query that doesn't return any data
一种选择是使用不返回任何数据的查询创建结构
CREATE GLOBAL TEMPORARY TABLE tempTable AS
SELECT *
FROM realTable
WHERE 1=0;
then insert the data
然后插入数据
INSERT INTO tempTable
SELECT *
FROM realTable;
Of course, given how infrequently global temporary tables are used in Oracle (particularly in comparison to other databases), I'd want to be very certain that you really need to create a temporary table from a permanent table in the first place.
当然，考虑到 Oracle 中很少使用全局临时表（特别是与其他数据库相比），我想非常确定您首先确实需要从永久表创建一个临时表。
