创建和使用临时表

======================================================会话1============================================
在第一个会话创建临时表，插入数据


SQL> create global temporary table tmp_emps on commit preserve rows as 
  2  select * from employees where 1=2;


Table created.


SQL> select * from tmp_emps;


no rows selected


SQL> insert into tmp_emps select * from employees where department_id=30;


6 rows created.


SQL> commit;




====================================================会话2============================================
在第二个会话中查不到会话1中插入的数据，在会话2中临时表插入数据


SQL> select count(*) from tmp_emps;


  COUNT(*)
----------
         0


SQL> insert into tmp_emps select * from employees where department_id=50;


45 rows created.


SQL> commit;


Commit complete.


====================================================会话1=========================================


会话1中将表截断
SQL> truncate table tmp_emps;


Table truncated.
 
SQL> select * from tmp_emps;


no rows selected




====================================================会话2========================================


如果会话2不断开，则不影响会话2中temp表中的数据
SQL> select count(*) from tmp_emps;


  COUNT(*)
----------
        45


断开重连之后，会话2中临时表的数据将丢失
SQL> disconnect;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn hr/oracle
Connected.


SQL> select count(*) from tmp_emps;


  COUNT(*)
----------
         0

在会话2中删除表
SQL> drop table tmp_emps;


Table dropped.


SQL> select count(*) from tmp_emps;
select count(*) from tmp_emps
                     *
ERROR at line 1:
ORA-00942: table or view does not exist


====================================================会话2========================================
会话1即使不断开重连，也无法找到表
SQL> select * from tmp_emps;
select * from tmp_emps
              *
ERROR at line 1:
ORA-00942: table or view does not exist