--构造基于SESSION的全局临时表(退出session该表记录就会自动清空)
drop table l_session;
create global temporary table l_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 l_transaction;
create global temporary table l_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 l_transaction
into l_session
select * from dba_objects; (个人解释:insert all默认自动提交,所以查询数量为0)
select session_cnt,transaction_cnt from (select count(*) session_cnt from l_session),
(select count(*) transaction_cnt from l_transaction);
commit;
select session_cnt,transaction_cnt from (select count(*) session_cnt from l_session),
(select count(*) transaction_cnt from l_transaction);
disconnect
connect scott/123
select session_cnt,transaction_cnt from (select count(*) session_cnt from l_session),
(select count(*) transaction_cnt from l_transaction);