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编程艺术深入理解
数据库
体系结构(第三版)》
基于会话的临时表和基于事务的临时表浅析
最新推荐文章于 2024-09-10 06:10:27 发布