【基础知识】基于事物的临时表和基于会话的临时表

    今天工作中有遇到关于临时表的一些问题，现在总结如下：

oracle@C01TEST03:/home/oracle>sqlplus mth/mth

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 22 16:37:26 2017

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

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

创建基于会话的临时表：

MTH@MTH> create global temporary table temp_table_session
  2  on commit preserve rows
  3  as
  4  select * from user_objects where 1=0;

Table created.

创建基于事物的临时表：
MTH@MTH> create global temporary table temp_table_transaction
  2  on commit delete rows
  3  as
  4  select * from user_objects where 1=0;

Table created.

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

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

分别向两个表中插入数据：

MTH@MTH> insert into temp_table_session select * from user_objects;

5 rows created.

MTH@MTH> insert into temp_table_transaction select * from user_objects;

5 rows created.

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

SESSION_CNT TRANSACTION_CNT
----------- ---------------
          5               5

提交数据：

MTH@MTH> commit;

Commit complete.

发现基于事物的临时表中数据被清除：
MTH@MTH> select session_cnt,transaction_cnt
  2  from
  3  (select count(*) session_cnt from temp_table_session),
  4  (select count(*) transaction_cnt from temp_table_transaction);

SESSION_CNT TRANSACTION_CNT
----------- ---------------
          5               0

断开会话，重新登入查询，发现基于会话的临时表中数据被清除：

MTH@MTH>
MTH@MTH> exit
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
oracle@C01TEST03:/home/oracle>sqlplus mth/mth

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 22 16:37:26 2017

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

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

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

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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30776559/viewspace-2141140/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30776559/viewspace-2141140/