Oracle之关于session和事务的全局临时表

--构造基于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);