解决ORA-01652(无法扩展表空间)问题

        最近几天旧系统发现ORA-01652（unable to extend temp segment by num in tablespace name）的问题，在临时增加数据文件大小后没几天又出现，无赖之下，上级让我这个业余的DBA查明原因。

        先查询临时表空间使用情况：

        select tablespace_name,
                    current_users,
                    total_blocks,
                    used_blocks,
                    free_blocks
           from v$sort_segment;

       

       可以看到表空间基本已被占用（FREE_BLOCKS的那些都是最近刚加的），虽然排序等操作会占用临时表空间，但是按理论临时表空间在操作结束后就会被释放的（被标记为FREE，空间未释放），所以为什么会一直被占用必须了解了，暂时想到这些天是不是有查询造成笛卡尔积之类的大操作且长时间未结束或者是有使用LOB的操作（旧系统因为设计问题，有很多LOB字段）且长时间未结束，所以马上查看这些天的AWR报表是否有长时间未结束的操作。

      AWR报表未显示有长时间的操作（请无视那些查询20、30s的操作，旧系统之所以成旧系统就是因为有太多坑，不得以重新开发一套系统）：

    

     于是好奇到底是什么操作一直霸占临时表空间， 于是查看占用临时表空间的SESSION：   

select DISTINCT se.username,
                se.sid,
                su.extents,
                su.blocks,
                su.blocks * to_number(rtrim(p.value)) / 1024 / 1024 as Space,
                tablespace,

                segtype,

                se.status,

                se.logon_time,
                se. last_call_et,
                to_char(sysdate - (se.last_call_et / (60 * 60 * 24)),
                        'yyyy-mm-dd hh24:mi:ss') last_work_time,
                sql_text
  from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
   and su.session_addr = se.saddr
   and s.hash_value = su.sqlhash

   and s.address = su.sqladdr;

        合计一下这些SESSION占用空间大小，跟前面查的差不多，根据STATUS判断这些SESSION大部分都不在活动状态了。一般来说状态是INACTIVE的要嘛是未COMMIT的，要嘛是业务系统连接池的长连接。根据LOGON_TIME和LAST_CALL_ET可以判断这些基本都是长连接，进一步查询这些SESSION的历史SQL，都有发现GROUP BY、ORDER BY等操作，可以确定是由于这些长连接长时间未释放导致临时表空间也没有释放。

        截止到这里，原因基本都明朗了，至于长连接一直没释放的原因是因为旧系统的奇葩设计导致更新发布时都不需要重启应用和中间件，而新系统由于有定期重启应用，所以未发生类似问题。

         至于解决办法，则可以通过创建JOB去KILL这些长连接，或者设置SMAS用户的IDLE_TIME或在SQLNET.ORA里新增expire_time。

        PS：相关来源：

       1.查询SESSION历史SQL

   select distinct session_id,
                y.sql_id,
                l.sql_text,
                l.elapsed_time / 1000000,
                x.CPU_TIME / 1000000,
                x.executions,
                x.CPU_TIME / x.executions / 1000000 per_cpu
     from v$active_session_history y, v$sql l, v$sqlarea x
  where y.sql_id = l.sql_id
      and l.hash_value = x.hash_value
      and session_id = 2831
    order by per_cpu desc;

    KILL SESSION脚本