Oracle 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段

逃离我推掉我的手 2022-07-26 11:28 522阅读 0赞

开发人员找我，Oracle11g下SQL语句报无法通过 128 (在表空间 TEMP 中) 扩展 temp 段。因为在开发环境上，初步判断是SQL的问题，把SQL中的WMSYS.WM\_CONCAT改为listagg,此SQL就没问题了，等了一会，开发人员再次找到我，说还有很多SQL报错。此时我判断现在是个系统的问题。之前写过一篇，[2016-1-8 ORA-1652: unable to extend temp segment by 128 in tablespace解决方案][2016-1-8 ORA-1652_ unable to extend temp segment by 128 in tablespace]，也是解决这个问题的，不过并不适用这个场景。此问题解决方法如下：

**1.用下列的SQL判断临时表空间适用情况**

select tablespace\_name,

max\_gb,  
       used\_gb,  
       round(100 \* used\_gb / max\_gb) pct\_used  
  from (select a.tablespace\_name tablespace\_name,  
               round((a.bytes\_alloc - nvl(b.bytes\_free, 0)) / power(2, 30),  
                     2) used\_gb,  
               round(a.maxbytes / power(2, 30), 2) max\_gb  
          from (select f.tablespace\_name,  
                       sum(f.bytes) bytes\_alloc,  
                       sum(decode(f.autoextensible,  
                                  'YES',  
                                  f.maxbytes,  
                                  'NO',  
                                  f.bytes)) maxbytes  
                  from dba\_data\_files f  
                 group by tablespace\_name) a,  
               (select f.tablespace\_name, sum(f.bytes) bytes\_free  
                  from dba\_free\_space f  
                 group by tablespace\_name) b  
         where a.tablespace\_name = b.tablespace\_name(+)  
        union all  
        select h.tablespace\_name tablespace\_name,  
               round(sum(nvl(p.bytes\_used, 0)) / power(2, 30), 2) used\_gb,  
               round(sum(decode(f.autoextensible,  
                                'YES',  
                                f.maxbytes,  
                                'NO',  
                                f.bytes)) / power(2, 30),  
                     2) max\_gb  
          from v$temp\_space\_header h, v$temp\_extent\_pool p, dba\_temp\_files f  
         where p.file\_id(+) = h.file\_id  
           and p.tablespace\_name(+) = h.tablespace\_name  
           and f.file\_id = h.file\_id  
           and f.tablespace\_name = h.tablespace\_name  
         group by h.tablespace\_name)  
order by 4;

**2.可以看到正在运行的会话使用临时表空间的情况，不过这里的sql\_id并不是当前正在执行的SQL。**

select username,session\_addr,sql\_id,contents,segtype,blocks\*8/1024/1024 gb     
from v$sort\_usage order by blocks desc;

**3.查看正在运行的会话打开的游标的SQL，下列查询。**

select sid from v$session s where saddr='0000000292038458';

select sql\_id,s.sql\_fulltext,sorts,rows\_processed/executions from v$sql s  
    where parsing\_schema\_name='CFMS\_TEST' and executions>0 and sorts>0    
    and sql\_id in (select sql\_id from v$open\_cursor where sid=6)

order by 3;

总结：临时表空间回收的前提是：**在SQL对应的游标没关闭的情况下，数据库不会去释放临时段**。而我们使用的weblogic的连接池中的连接是长连接，游标一直是打开的状态。解决问题的办法就是把weblogic重启一下，询问过开发组，说有差不多一年没有重启系统了。

[2016-1-8 ORA-1652_ unable to extend temp segment by 128 in tablespace]: http://blog.csdn.net/stevendbaguo/article/details/50484505