全局临时表的妙用

有一天，客户生产系统日志归档量暴增，undo表空间使用率也居高不下。

经过查看dba视图和AWR报告，诊断分析排查后发现，原来是delete语句 在一小时内被执行了几十W 次，和业务侧确认后暂停了该程序。

后续交流沟通后发现该 表是一张中间表，会先把中间的临时结果集放在这张表，业务用完这个临时集后需要 清除。

其实根本不需要通过delete的方式去删除清理临时表，而可以通过Oracle的全局临时表特性来实现该需求。

--构造基于SESSION的全局临时表（退出session该表记录就会自动清空）

drop table zmh_session;
create global temporary table zmh_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='ZMH_SESSION';
ZMH_SESSION Y SYS$SESSION

--构造基于事务的全局临时表(commit提交后，不等退出session，在事务完成后就会自动清空）

drop table  zmh_transaction;
create global temporary table zmh_transaction on commit delete rows as select * from dba_objects where 1=2; --基于事务的临时表
select table_name, temporary, DURATION from user_tables  where table_name='ZMH_TRANSACTION';
ZMH_TRANSACTION Y SYS$TRANSACTION

插入数据：

insert into  zmh_session select * from dba_objects;
insert into  zmh_transaction select * from dba_objects;

SQL> select session_cnt,transaction_cnt from (select count(*) session_cnt from zmh_session),(select count(*) transaction_cnt from zmh_transaction);
      72496           72496

commit过后查询：

SQL> select session_cnt,transaction_cnt from (select count(*) session_cnt from zmh_session),(select count(*) transaction_cnt from zmh_transaction);
      72496               0

断开会话再查询：  

SQL> select session_cnt,transaction_cnt from (select count(*) session_cnt from ljb_tmp_session),(select count(*) transaction_cnt from ljb_tmp_transaction);
SESSION_CNT TRANSACTION_CNT
----------- ---------------
0               0