临时表空间未释放问题

众所周知Oracle临时表空间主要是用于数据库较大的临时排序用，在PGA分配的工作区空间不足以容纳排序数据时使用临时表空间。在用户执行的排序相关的语句执行完毕后临时段回收，相应的临时空间占用也会自动释放。但针对LOB所产生的临时段的释放不会遵从些机制。

使用率过高带来的影响

临时表空间使用率过高带来的影响：

由于一般业务中都会存在大量排序的sql(如distinct、order by、group by、union、union all、hash join等)，所以一旦临时表空间长时间使用率过高将导致核心sql因ORA-1652错误而终止，最终导致业务处理失败，如果是核心业务那么这个影响是巨大的。

遇到的问题

某核心OLTP生产系统最近发现临时表空间占用高达90%多，且持续监控发现一直不释放，而且还在缓慢增长

原因查找

106G的临时表空间使用了102G，这么大的临时表空间使用率在一个正常的OLTP系统中是很罕见的。
1
首先查看活动会话数，如下显示活动会话数总共27个，是很正常的，可以肯定临时表空间占用较高的原因并非由大量需要排序的异常会话引起，常见的这个原因可以排除。

2
定位哪些进程对应的哪些语句占用了较多临时表空间，经检查发现如下：

从上面可以看出有10个进程占用了大约100多G的临时表空间，且显示为TEMPORARY LOB_DATA，说明正是这10个进程导致的临时表空间被占满且不释放，经连续多次观察这10个进程执行的为sql_id为d12s8kpkp85zq的同一个sql。该sql语句如下：

1529994300320929

这个sql查询的是所有字段，且这个表有个字段正是LOB型，从以下图中可以看出该字段存储应该是xml文件解析的内容：

1529994311916352

3
确定这些语句对应的会话，明确来源

从临时LOB段的使用情况来看，主要由10个会话占用了临时表空间，且这10个会话均是LOB缓存占用了较大的空间。通过持续查看这10个会话的session信息，可以看出这10个会话执行的正是第一步定位的语句，且这10个会话均是由IP地址为xxxx的应用主机的ocs_rentfee_imp这个程序发起，会话sid一直不变，与应用开发商确认这个程序是C程序写的。

通过以上的分析不难看出引起临时表空间使用率超高且不释放的来源是应用的C程序使用长连接模式连接到数据库对数据库一张含有clob字段表进行按条件排序查询导致。

4
为何由应用调用的使用的临时LOB缓存未释放
Oracle在对包含lob字段进行读取、修改或写入时均使用临时表空间TEMPORARY LOB_DATA段以缓存形式来存储这些临时数据。从以下Oracle对LOB临时段的解释中可以看出如果是通过过应用程序来访问LOB字段时Oracle并不会显式地去关闭或清理临时LOB，这是由于Oracle作为服务端并不知道客户端应用程序什么时候结束对LOB临时段的使用，何时应该清理是由应用程序确定的。

那么客户端应用应该如何去清理这些临时LOB以释放其占用的临时表空间呢？

既然清理由应用程序确定，那么必然是程序开发语言是有这样释放lob缓存的代码的，比如对于Java程序有java.sql.NClob.free()这个方法来释放lob，对于其它通过调用 Oracle的API接口来实现的应用，oracle给出了相应如下的方法来释放Lob：

解决措施

1.应用程序更改相应模块的连接方式为短连接(例如对于由应用程序在调用完毕后执行关闭jdbc连接)
2.应用程序在调用相应的程序方法在对LOB引用完毕后主动关闭(此方法为最佳方法，但往往涉及到一些核心代码，开发商通常不愿意去更改这些代码，更改实施难度较大)

3.相应的应用模块定期重启(此方法操作简单，但如果相应的应用模块为核心业务时，往往需要有效的停机窗口)

4.数据库级别设置事件打开清理开关：

alter session set events ‘60025 trace name context forever’;

此方法限制条件为：

10.2.0.4版本及以上且只有当数据库中所有缓存的临时段和非缓存的临时段均为0时才执行清理，如果数据库中事务较繁忙且始终存在cache temp lob and no-cache temp lobs均不为0时此方法将失效。就目前而言生产库上的这个lob缓存由于是长连接，所以cache tmp lob始终不为0，所以此方法不可行。

如果生产中遇到类似问题，可以考虑以下方法，寻找最适合自己的方法，本次由于这个业务模块是非核心模块，在晚上业务空闲时应用厂商对相应模块的应用进行了重启，重启完成后Temp表空间随之也释放了。

引申问题

既然之前说到通过应用程序来引用lob数据时oracle不会也不应该去负责清理这些不再使用的lob段，那么还有通过pl/sql或sql直接对lob字段的访问这种情况的存在，那么对于这些访问方式oracle又是如何清理释放临时lob段的呢？
通过oracle声明我们可以看出，这些方式oracle会在访问下一行数据时显示释放这些lob缓存。

备注：以上Oracle信息的引用来源于

Best Practises for XMLType Temporary LOB Usage (文档 ID 1955135.1)

How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (文档 ID 802897.1)