一、日常工作中出现的“灵异事件”
利用BI工具在进行整体流程跑批的时候，一段时间就会报错：临时表空间不足
生产环境中为了保证系统的稳定，一般不采取重启数据库的方式。所以扩充表空间或者重建临时表空间，就成了首选。
之前一直用ALTER TABLESPACE 临时表空间名 ADD tempFILE '/u01/app/oradata/orcl/XXX.dbf' SIZE 2G AUTOEXTEND on ;
增加临时表空间文件来扩容，从而解决表空间不足的问题。
这样的方式治标不治本，因为隔一段时间就会出现临时表空间不足的情况。当时临时表空间已经扩展到190个G了，但是依然会出现表空间不足的情况。
SELECT ROUND((F.BYTES_FREE + F.BYTES_USED)/1024/1024/1024, 2)AS "TOTAL(GB)",ROUND(((F.BYTES_FREE + F.BYTES_USED) - NVL(P.BYTES_USED, 0))/1024/1024/1024,2) AS "FREE(GB)",D.FILE_NAME AS "TEMP_FILE",ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2)AS "USED(GB)" ,ROUND((F.BYTES_USED + F.BYTES_FREE)/1024/1024/1024, 2)AS "TOTAL(GB)",ROUND(((F.BYTES_USED + F.BYTES_FREE) - NVL(P.BYTES_USED, 0))/1024/1024/1024, 2) AS "FREE(GB)" ,ROUND(NVL(P.BYTES_USED, 0)/1024/1024/1024, 2)AS "USED(GB)"FROM SYS.V_$TEMP_SPACE_HEADER F ,DBA_TEMP_FILES D ,SYS.V_$TEMP_EXTENT_POOL PWHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAMEAND F.FILE_ID(+) = D.FILE_IDAND P.FILE_ID(+) = D.FILE_ID;
二、问题溯源
通过上面的SQL查询临时表空间文件，发现一共七个扩容文件，全部被占用，使用率近乎100%
一般来说程序在进行排序、创建或者重建索引、distinct、group by的时候都要使用表空间，但是程序运行结束之后，会释放掉这些资源，标记为free状态。
所以需要寻找到底是哪个SQL一直使用临时表空间，而且没有释放。在查看之前，先确认当前系统是否还有程序在跑批，排除这些意外因素。
SELECT se.username,se.sid,se.serial#,se.SQL_ID,se.sql_address,se.machine,sa.SQL_TEXT,se.program,su.tablespace,su.segtype,su.contentsFROM v$session se,v$sort_usage su,v$sqlarea saWHERE se.saddr=su.session_addr and se.SQL_ID=sa.SQL_ID
通过这段SQL中的SQL_TEXT字段可以看到占用临时表空间的执行SQL.全都是同一个SQL，其中用到了with语法，创建了一个临时表，这部分资源，在程序结束之后，一直没有释放掉。
之前也用过with语法，当SQL执行结束之后，临时表空间会被正常释放掉，这里一直被占用，很可能是别的原因。通过查看program字段，我发现这些全都是JDBC thin。
至此问题已经全部整理清楚：这是一个ORCLE的bug(Bug 5723140 – Temp LOB space not released after commit [ID 5723140.8])。
从10.2.0.4开始虽然已经修复了该bug,但是默认情况下，为了更加高效的利用temp,在session未断开前,不自动释放temp 空间。
三、解决方法
找到原因之后就好办了。我当时的应急办法是手工杀掉这些进程
Alter system kill session 'sid,serial#';
等清理结束之后，我需要一个可以让系统自己来关闭这些会话的程序。当时看网上有从java角度，写释放资源的，但是我们当时用的是外包公司的产品，为了这个事情，单独打个补丁，显得有些小题大作。
后来我想用shell来定期处理，先获取会话的sid和serial#,再执行杀掉会话的SQL，这个需要获取oracle的管理员权限，放到生产上的话，风险比较大。
所以最后决定用触发器来解决这个事情，通过logon触发器，在指定用户登录后，对会话设置event 60025事件来达到不断开会话的情况下及时释放临时段。
create or replace trigger kill_with after logon on schemabeginexecute immediate 'alter session set events ''60025 trace name context forever''';end;
至此，通过jdbc连接oracle而产生的临时表空间占用问题就全部解决了。
