今天工作中有遇到关于临时表的一些问题,现在总结如下:
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/