【基礎知識】基於事物的臨時表和基於會話的臨時表

    今天工作中有遇到關於臨時表的一些問題，現在總結如下：

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/，如需轉載，請註明出處，否則將追究法律責任。