Oracle 11g - 临时表空间段无法释放（Oracle bug？）

Question

我正在使用 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production，并且我在 TOAD 12.6.0.53 中执行以下查询时遇到了问题（这不是一个典型的查询，仅用于演示）：

with rownums 
as (select distinct 
    rownum rn 
      from dual 
   connect by level <=1000000 
) 
select rn from rownums 
 union all
select rn from rownums;

--Edit: this Statement ist NOT showing the behaviour:
begin
    for cRow in (with rownums 
                as (select distinct 
                    rownum rn 
                      from dual 
                   connect by level <=1000000 
                ) 
                select rn from rownums 
                 union all
                select rn from rownums) 
    loop
        return;
    end loop;
end;

执行此查询后，我可以使用以下查询检查临时表空间的使用情况：

SELECT S.sid, S.serial#, SUM (T.blocks) * TBS.block_size / 1024 / 1024 used_mb, T.tablespace
  FROM v$sort_usage T, v$session S, dba_tablespaces TBS
 WHERE T.session_addr = S.saddr
   AND T.tablespace = TBS.tablespace_name
   and s.sid = sys_context('userenv','sid')
 GROUP BY S.sid, S.serial#, TBS.block_size, T.tablespace;

结果是，上述测试查询的每次执行都需要约12MB的临时表空间，直到我断开会话才能释放。如果我这样做太频繁，就会导致ORA-1652: 无法扩展临时段。

我做错了什么？或者这可能是Oracle的一个错误吗？

- bernhard.weingartner

---

我在使用PL/SQL块时没有看到这种情况，而在使用普通SQL时有较小的影响。你的临时表空间真的是临时的吗？还是它正在使用永久段 - dba_tablespaces.contents显示了什么？（如果您有访问权限，则MOS笔记1039341.6可能是相关的，因为我可以看到v $ sortsegments.user_blocks再次增长，仅使用普通SQL）。 - Alex Poole

实际上，我在SQL*Plus中使用普通的SQL版本也没有看到它；但是在SQL Developer中，只有在结果集中存在尚未获取和显示的行时才会看到它 - 因此查询仍处于活动状态。我猜Toad也在做类似的事情？ - Alex Poole

感谢您的反馈，关于PL/SQL块的问题是我的错误，它并没有展现出这种行为。我已经检查了dba_tablespaces.contents - 它是"TEMPORARY"而不是"PERMANENT"。 - bernhard.weingartner

我也可以从一个简单的Java程序中复现这个问题（SQL Developer和Toad都是基于JDBC运行的）；将您的查询执行到结果集中会占用临时空间，除非我循环遍历整个结果集，否则它不会被释放。关闭结果集或预处理语句并不会释放临时段，它们仅在while（rSet.next（））{...}循环完成后才被释放。这看起来像是一个错误，但我在 MOS 中看不到任何明显的匹配项 - 18098207 可能相关，但很难确定。有趣的是，如果这可以在其他版本中重现。 - Alex Poole

如果您拥有MOS访问权限，这看起来像是20401612，它与20477541（不可见）相关联，可能还涉及18004006。不过我认为您需要提出服务请求以获取更多信息。 - Alex Poole

Answer 1

TEMP表空间的磁盘空间有限。当您运行查询时，它会越来越多地使用TEMP空间，直到达到其限制并出现错误无法扩展TEMP，因此可能有两个解决方案：

• 使用EM增加TEMP空间更好

• 优化使用较少TEMP的查询

- Ahmad Raza