全局临时表有两种模式,一种是基于会话级别的,会话与会话之间的数据是隔离的,当会话段掉,数据将清空,另一种基于事务级别的,当执行commit或者rollback 数据将清除,全局临时表使用的是临时表空间的位置。
全局临时表不会记录日志,但对数据而言不是安全的。
-构造基于SESSION的全局临时表(退出session该表记录就会自动清空)
drop table ljb_tmp_session;
create global temporary table ljb_tmp_session on commit preserve rows as select * from dba_objects where 1=2; --基于session的临时表
select table_name,temporary,duration from user_tables where table_name='LJB_TMP_SESSION';
--构造基于事务的全局临时表(commit提交后,不等退出session,在该表记录就会自动清空)
drop table ljb_tmp_transaction;
create global temporary table ljb_tmp_transaction on commit delete rows as select * from dba_objects where 1=2; --基于事务的临时表
select table_name, temporary, DURATION from user_tables where table_name='LJB_TMP_TRANSACTION';
insert all
into ljb_tmp_transaction
into ljb_tmp_session
select * from dba_objects;
select session_cnt,transaction_cnt from (select count(*) session_cnt from ljb_tmp_session),
(select count(*) transaction_cnt from ljb_tmp_transaction);
commit;
select session_cnt,transaction_cnt from (select count(*) session_cnt from ljb_tmp_session),
(select count(*) transaction_cnt from ljb_tmp_transaction);
disconnect
connect ljb/ljb
select session_cnt,transaction_cnt from (select count(*) session_cnt from ljb_tmp_session),
(select count(*) transaction_cnt from ljb_tmp_transaction);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30345407/viewspace-2150365/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30345407/viewspace-2150365/