oracle临时表空间不释放问题

梓舒4692

一、现象
应用报错如下：
ORA-01652: 无法通过 1280 (在表空间 TEMP 中) 扩展 temp 段
该错误是因为TEMP临时表空间已占满，无法继续分配。

二、原因分析过程
1.怀疑pga太小，导致占用临时表空间
经查看，pga设置为1g，排除该原因

2.某服务调用频率异常
该服务有定时器刷新，也可以通过alt+r手动刷新服务，通过服务端日志发现，该服务调用频率异常，正常情况下每10秒调用一次，故障发生期间，出现每秒最多调用4次的情况。
进行模拟实验，手工高频率刷新服务（每秒调用服务20次），发现临时表空间并未被长时间占用，排除该原因。

3.外系统查询sql语句问题
我们将系统中的一个表的查询权限开放给了另外一个系统
select * from mytable t where rownum <= 1
如果外系统停了一段时间，mytable中就会积累很多数据（比如1万条数据），然后外系统恢复之后查询mytable（如果有数据就连续查询，如果没有数据，每2秒查询一次），每次只查一条数据，至少需要调用一万次，但是每次查询都无法使用索引。
经过和外系统同事沟通，并查看AWR报告，没有发现数据大量积累的情况，排除该原因。

4.lob字段问题
通过以下sql语句可以查看什么操作占用临时表空间，但是只有问题出现时，才能查询到数据。一次偶然的机会，查询到了数据。

SELECT TMP_TBS.TABLESPACE_NAME,
       SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
       SUM(USED_TOT.USED_MB) USED_MB,
       SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) TMP_TBS,
       (SELECT TMP_USED.TABLESPACE,
               SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
          FROM V$SORT_USAGE TMP_USED,
               (SELECT VALUE DB_BLOCK_SIZE
                  FROM V$PARAMETER
                 WHERE NAME = 'db_block_size') PARA
         GROUP BY TMP_USED.TABLESPACE) USED_TOT
where TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
GROUP BY TMP_TBS.TABLESPACE_NAME;
----------------------------------------------
TABLESPACE_NAMETOTAL_MBUSED_MBUSED_PERSENT
TEMP1001010

上面的结果中（测试环境模拟），已占用的临时表空间为10MB，而且长时间不释放，通过以下语句可以看到详细的占用情况

select * from v$sort_usage
-----------------------------------------------
USERNAMEUSERSESSION_ADDRSESSION_NUMSQLADDRSQLHASHSQL_IDTABLESPACECONTENTSSEGTYPESEGFILE#SEGBLK#EXTENTSBLOCKSSEGRFNO#
USERUSER07000000473F73C814448070000002C44494014381380012b9476javhgnjTEMPTEMPORARYLOB_DATA2026409112801

其中的BLOCKS为占用的数据块数，单位为8KB，1280*8KB=10MB，和第一条查询语句的结果相符。其中SQLADDR表示具体sql语句的地址。通过以下语句可以查看具体sql语句。

  select sql_text ,address from v$sql s where address='070000002C444940';
-----------------------------------------------
SQL_TEXTADDRESS
insert into mytable(AHM_FLTNO, AHM_DATE, AHM_TYPE_AD, AHM_TYPE_ID, AHM_MSG_TYPE, AHM_MSG_SUBTYPE, AHM_ORIG_AIRPORT, AHM_DEST_AIRPORT, AHM_CONTENT, AHM_CONTENT_EXT, AHM_CONTENT_DISPLAY, AHM_SENDER_MACHINE, AHM_SENDER, AHM_SENDER_GRP, AHM_RCVER_MACHINE, AHM_RCVER_GRP, AHM_RCVER, AHM_STATE, AHM_DATATYPE, AHM_PRIORITY, AHM_TIME, AHM_OPTM, AHM_EXPIRE, AHM_VERSION, AHM_ID) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25)070000002C444940


确定sql语句后，通过查询应用中的代码发现，mytable表的插入操作使用hibernate，其中AHM_CONTENT_EXT字段使用了clob类型，在hibernate中操作clob类型，需要使用自定义类型，将String转换成clob。
在该自定义类型中，使用以下语句创建了临时clob对象，但是没有释放。
CLOB tempClob = CLOB.createTemporary(ConnectionHelp.getConnection(conn), true, CLOB.DURATION_SESSION);

三、解决方法
应用使用hibernate版本为3.1.3，在hibernate新版本中已经实现了该类，使用Hibernate-3.3.2.GA实现的StringClobType.java
http://www.docjar.com/html/api/org/hibernate/type/StringClobType.java.html


四、分析过程中的疑问
1.刚开始发现问题时，我们通过以下语句进行查询
SELECT se.username,
       se.sid,
       se.serial#,
       se.sql_address,
       se.machine,
       se.program,
       su.sqladdr,
       su.tablespace,
       su.segtype,
       su.contents
  FROM v$session se, v$sort_usage su
WHERE se.saddr = su.session_addr;

发现查询到的sql_address对应的sql为
select 1 from dual
正常情况，以上语句不会占用临时表空间，为什么查询出来的是这个语句。
经过分析，我们可以发现，这个语句是TongWeb用来定时探测数据库状态的语句。上面的语句是基于session作为关联条件的，也就是说，在出现问题的那个时间点，用上面的语句查询，是可以查询到真实的sql语句，但是在tongweb对数据库探测后，该session的当前sql语句已经变成了select 1 from dual。
v$session有另外一个字段prev_sql_addr，表示上一条sql语句的地址，但也不能保证一定是问题语句对应的地址

2.有些数据库（测试中使用的oracle 9i）一个消息占用1MB，有些数据库（测试中使用oracle 10g）一个消息占用10MB
该问题与临时表空间INITIAL_EXTENT设置有关，使用以下语句可以查看具体设置：
select BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,EXTENT_MANAGEMENT,ALLOCATION_TYPE from dba_tablespaces where tablespace_name='TEMP';

每条消息占用10MB左右（oracle 10g）
---------------------------------------
BLOCK_SIZEINITIAL_EXTENTNEXT_EXTENTEXTENT_MANAGEMENTALLOCATION_TYPE
81921048576010485760LOCALUNIFORM

每条消息占用1MB左右（oracle 9i）
---------------------------------------
BLOCK_SIZEINITIAL_EXTENTNEXT_EXTENTEXTENT_MANAGEMENTALLOCATION_TYPE
819210485761048576LOCALUNIFORM


3.占用临时表空间到80MB就不再上升   
测试时临时表空间设置为100MB，发送消息后，临时表空间被占用逐渐增多，但是占用到80MB就不再上升了。
该现象与应用服务器（WAS或TongWeb）数据库连接池设置有关，以测试时使用的TongWeb为例，配置如下：

最小连接数：8
空闲超时：300秒

也就是说，在并发量较小，连接数小于等于8时，最多占用80MB临时表空间。
经过测试，修改“最小连接数”为15后，占用临时表空间情况会持续上升，突破80MB。

4.在应用中调用freeTemporary后，clob占用的临时表空间不释放
经查是oracle的bug，5723140。
在10.2.0.4中需要使用以下语句解决该问题
alter session set events '60025 trace name context forever';
经过测试，以上修改对存储过程中使用lob对象有效，但是不清楚在java中怎么进行相应的设置。

5.有时一条记录占用30MB
多数只占用10MB，偶尔占用30MB，没有重现（数据量都不大，不应该超过10MB）。

五、参考文章
1.Oracle常用性能监控语句解析
http://www.cnblogs.com/preftest/archive/2010/11/14/1876856.html

2.查看oracle 系统临时表空间、undo表空间、SGA和PGA大小
http://blog.csdn.net/xueyepiaoling/article/details/6187842

3.临时表空间使用情况
http://space.itpub.net/?uid-13605188-action-viewspace-itemid-678109

4.Temporary LOB导致临时表空间暴满
http://pingshx.itpub.net/post/39434/484280

5.Oracle Database List of Bugs Fixed 10g Release 2 (10.2.0.4) Patch Set 3
http://www.eygle.com/Notes/10204_buglist.htm

六、其他相关sql语句
1.查看临时表空间数据文件
Select file#,status,name from v$tempfile

2.修改临时表空间大小
alter database tempfile '/app/oradata2/oraapp/newapp_temp_01.dbf' resize 150M

3.修改表空间大小
alter database datafile 'xx.dbf' resize xxxx