一、日常工作中出现的“灵异事件”

利用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 P
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
  AND F.FILE_ID(+) = D.FILE_ID
  AND 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.contents
  FROM v$session se,
       v$sort_usage su,
       v$sqlarea sa
WHERE 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 schema
begin
execute immediate 'alter session set events ''60025 trace name context forever''';
end;

至此，通过jdbc连接oracle而产生的临时表空间占用问题就全部解决了。