基于会话的临时表和基于事务的临时表浅析

EODA@PROD1> set echo on
EODA@PROD1> 
EODA@PROD1> create global temporary table temp_table_session  --创建基于会话的临时表
  2    on commit preserve rows
  3    as
  4    select * from scott.emp where 1=0
  5  /

EODA@PROD1> 
EODA@PROD1> create global temporary table temp_table_transaction  --创建基于事务的临时表
  2    on commit delete rows
  3    as
  4    select * from scott.emp where 1=0
  5  /

EODA@PROD1> 
EODA@PROD1> insert into temp_table_session select * from scott.emp;

14 rows created.

EODA@PROD1> insert into temp_table_transaction select * from scott.emp;

14 rows created.

EODA@PROD1> 
EODA@PROD1> select session_cnt, transaction_cnt
  2  from ( select count(*) session_cnt from temp_table_session ),
  3  ( select count(*) transaction_cnt from temp_table_transaction );

SESSION_CNT TRANSACTION_CNT
----------- ---------------
	 14		 14

EODA@PROD1> 
EODA@PROD1> commit;  --提交

Commit complete.

EODA@PROD1> 
EODA@PROD1> select session_cnt, transaction_cnt
  2  	 from ( select count(*) session_cnt from temp_table_session ),
  3  	      ( select count(*) transaction_cnt from temp_table_transaction );  --基于事务的表会清空

SESSION_CNT TRANSACTION_CNT
----------- ---------------
	 14		  0

EODA@PROD1> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ocm1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 27 00:45:43 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD1> conn eoda/foo
Connected.
EODA@PROD1> select session_cnt, transaction_cnt  --基于会话的表也被清空
from ( select count(*) session_cnt from temp_table_session ),
( select count(*) transaction_cnt from temp_table_transaction );  2    3  

SESSION_CNT TRANSACTION_CNT
----------- ---------------
	  0		  0
EODA@PROD1> 
EODA@PROD1> column table_name format a25
EODA@PROD1> column duration format a15
EODA@PROD1> select table_name, temporary, duration from user_tables;  --查看表状态

TABLE_NAME		  T DURATION
------------------------- - ---------------
TEMP_TABLE_SESSION	  Y SYS$SESSION
TEMP_TABLE_TRANSACTION	  Y SYS$TRANSACTION

--参考来源《Oracle编程艺术深入理解 数据库 体系结构（第三版）》