Oracle用子查询创建临时表的问题总结

当需要创建临时表来暂时存储一个表的数据时，可以用子查询来实现，语法很简单：

SQL> create global temporary table temp1  as select * from table1;

通过查询临时表temp1，发现只是创建了永久表的一个结构，而并没有将数据复制进去。

我改用永久表来试验子查询的功能，语句如下：

SQL> create table test as select * from table1;

永久表数据复制了，但是临时表没有数据。

原因是Oracle中临时表有两种：
会话级临时表和事务级临时表

1 会话级临时表

• 创建会话临时表

SQL> create global temporary table tmp_1 (user_id int, user_name varchar2(20)) on commit preserve rows;
# on commit preserve rows表示 当事务提交时 保留数据

• 向临时表中插入数据

SQL> insert into table tmp_1(user_id,user_name) values(1,'张三');
SQL> insert into table tmp_1(user_id,user_name) values(2,'王二');
SQL> commit;

• 查询

SQL> select * from tmp_1;

现在表tmp_1是有数据的。

• 重新打开 一个sql窗口 再次查询 则无数据了

说明此插入的数据，只为本窗口会话存在

2 事务级临时表

• 创建事务临时表

SQL> create global temporary table tmp_2 (user_id int,user_name varchar2(20)) on commit delete rows;
# on commit delete rows表示 当事务提交时 删除数据 

• 向临时表中插入数据

SQL> insert into tmp_2(user_id,user_name) values(1,'张三');
SQL> insert into tmp_2(user_id,user_name) values(2,'王二');

• 查询

SQL> select * from tmp_2;

现在表tmp_2是有数据的。

• 提交下语句，或者回滚事务，再次查询就没有数据了。
  因为事务临时表，就是在事务提交时，（commit;
  rollback;）就数据清空了。

–查看临时表的表空间，其实他们的表空间为空

 SQL> select table_name, tablespace_name    
 from user_tables 
   where table_name ='TMP_1'   
         or table_name = 'TMP_2';

3 小结

• on commit delete row
  默认选项，在commit的时候将数据删除
• on commit preserve row
  在commit的时候将数据保留，会话结束后自动删除。

在语法上,会话临时表采用on commit preserve rows,而事物临时表采用on commit delete rows;在用途上，会话级临时表只会在会话结束时，临时表中的数据才会被截断。而事物临时表则不管是在事物提交（commit）、事物回滚(rollback)或会话结束,临时表中的数据都会被截断。

由于第一种是默认值，我的命令里面没加选项默认为commit后删除数据。所以在我使用Select查询的时候，发现临时表里没有数据。究其原因我们可以归纳为Create table是DDL语句，在触发后，Oracle会隐式的提交给事务处理，因此刚刚插入临时表的数据就被自动删除了。我们把之前的SQL语句改成如下：

SQL> create global temporary table temp2  on commit preserve rows as select * from table1;

再用Select查询temp2，我们如愿的查到了数据。
但是问题又来了，通过这种方式创建的表，数据复制成功，但是会话结束后，不能删除它，怎么样才能在创建之后又可以马上删除呢?我们在Drop table前先 truncate table table_name。语法如下：

SQL> truncate table temp2;
SQL> drop table temp2;