基于会话的全局临时表(on commit preserve rows)
基于事务的全局临时表(on commit delete rows)
set autotrace off;
(1)创建表
//创建临时会话表
create global temporary table tmp_session1 on commit preserve rows as select * from dba_objects where 1=0;
select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 100496
//创建事务临时表
create global temporary table tmp_trans1 on commit delete rows as select * from dba_objects where 1=0;
select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 21912
(2)插入数据
//向临时会话表插入数据
insert into tmp_session1 select * from dba_objects;
select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 512820
//向临时事务表插入数据
insert into tmp_trans1 select * from dba_objects;
select a.name,b.value from v$statname a join v$mystat b on a.statistic#=b.statistic# where a.name='redo size';
NAME VALUE
---------------------------------------------------------------- ----------
redo size 434236
测试：
//在事务上
select count(*) from tmp_trans;
COUNT(*)
----------
72539
commit;
select count(*) from tmp_trans;
COUNT(*)
----------
0
//在会话上
select count(*) from tmp_session1;
COUNT(*)
----------
72537
commit;
select count(*) from tmp_session1;
COUNT(*)
----------
72537
sql guoyf/guoyf
Connected.
Session altered.
LISN@orcl>select count(*) from tmp_session1;
select count(*) from tmp_session1;
COUNT(*)
----------
0
基于会话的临时表:
全局临时表特征：
(1)基于会话的全局临时表特征：基于会话的全局临时表则是session链接退出后，临时记录自动删除，无需手动去操作，针对不同session访问全局临时表，看到的结果是不同。
(2)基于事务的全局临时表在commit或者session链接退出后，临时表记录自动删除。
