重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: Oracle 临时表空间报错：空间耗尽 [打印本页]

作者: Inkcup 时间: 2026-2-27 09:23
标题: Oracle 临时表空间报错：空间耗尽
当 Oracle 临时表空间（由临时文件组成）空间耗尽时，任何需要排序、哈希连接、全局临时表操作或索引重建的 SQL 语句都会直接失败。

一、具体的错误信息

你会看到类似以下的报错：

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

128 表示尝试扩展的块数量

TEMP 是临时表空间的名称

应用程序端通常显示：

ORA-01652: 无法通过 128（在表空间 TEMP 中）扩展 temp 段

二、哪些操作会消耗临时空间？

ORDER BY、GROUP BY、DISTINCT

UNION、INTERSECT、MINUS

哈希连接（HASH JOIN）

索引创建或重建

全局临时表的数据操作

MERGE 语句

WITH 子句（CTE，公共表表达式）的大量数据处理

关键点：即使你的业务逻辑中没有这些操作，数据库自动统计信息收集作业也可能消耗临时空间。

三、临时文件“剩余空间很少”但不报错的情况

如果临时文件设置了自动扩展（AUTOEXTEND ON），且磁盘仍有空间，Oracle会持续扩展临时文件，不会报错。

但是存在风险：

磁盘写满：临时文件持续增长可能耗尽文件系统空间，导致数据库挂起或崩溃。

达到上限：如果设置了MAXSIZE，达到上限后依然会报ORA-01652。

四、快速诊断命令
1. 查看当前临时表空间使用率
sql

SELECT * FROM DBA_TEMP_FREE_SPACE;

2. 查看临时文件是否自动扩展
sql

SELECT FILE_NAME, BYTES/1024/1024 SIZE_MB,
   AUTOEXTENSIBLE, MAXBYTES/1024/1024 MAXSIZE_MB
FROM DBA_TEMP_FILES;

3. 定位正在消耗临时空间的会话
sql

SELECT SID, SERIAL#, USERNAME, STATUS,
   (BLOCKS*8/1024) TEMP_USAGE_MB, SQL_ID
FROM V$SORT_USAGE;

五、解决方案
场景处理方法
临时文件未开自动扩展 ALTER DATABASE TEMPFILE '...' AUTOEXTEND ON NEXT 1G MAXSIZE 32G;
自动扩展已开但磁盘空间不足清理磁盘、添加新的临时文件到其他挂载点、移动现有临时文件
单个查询异常消耗临时空间通过上述SQL找到对应SQL_ID，联系开发优化（例如缺少索引导致笛卡尔积哈希连接）
临时表空间长期不足增加临时文件数量或调整临时表空间整体大小

紧急扩充分示例：
sql

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORCL/temp02.dbf' SIZE 2G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

六、需要注意的一点

不要尝试收缩（Shrink）正在使用的临时文件。
临时文件的收缩需要重启数据库或在没有任何临时空间使用的情况下操作，且收缩后如果需求突增依然会报错。建议做法是保留合理的空闲空间，而非频繁收缩。
====================
临时表空间的“自动清理”是指：

当一个会话结束，或SQL语句执行完毕，该会话占用的临时段（temp segment）会立即标记为可重用

物理空间不会释放回操作系统，文件大小不会缩小。
====================
临时表空间“用不完”的原因

很多DBA发现临时文件持续增长，是因为：

长连接持续存在（应用连接池）

      会话不结束 → 会话持有的临时空间不释放

      每个会话都可能保留一部分临时空间

大查询频繁执行

      第一次扩展到100GB

      以后每次都在100GB水位上下波动

      文件永远不会缩回去

多个并发会话

      临时表空间的高水位标记（High Watermark）会不断推高

====================
什么时候物理空间会真正释放？

只有两种情况临时文件会缩小：
1. 手动收缩（需要特定条件）
sql

-- 方法一：调整大小（必须在无活跃使用时）
ALTER DATABASE TEMPFILE '...' RESIZE 10G;

-- 方法二：启用自动收缩（谨慎使用）
ALTER DATABASE TEMPFILE '...' AUTOEXTEND ON NEXT 1G MAXSIZE 32G;
ALTER DATABASE TEMPFILE '...' SHRINK SPACE;  -- 19c起支持

2. 删除重建
sql

ALTER TABLESPACE TEMP DROP TEMPFILE '...';
ALTER TABLESPACE TEMP ADD TEMPFILE '...' SIZE 10G AUTOEXTEND ON;

====================
不需要关注文件大小本身，需要关注的是：

磁盘剩余空间是否足够（防止自动扩展失败）

是否有异常的、不释放临时空间的会话
=================
检查临时表空间实际使用情况的sql语句：

select  total.ts tablespace,
      total_t.mb total_mb,
      NVL(total.mb - free.mb,total.mb) used_mb,
      total_t.mb-(NVL(total.mb - free.mb,total.mb))  free_mb,
      DECODE(total_t.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total_t.mb)*100,2),100)) pct_used,
      CASE WHEN (total_t.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
      ELSE '['|| DECODE((total_t.mb-(NVL(total.mb - free.mb,total.mb))),
                           null,'XXXXXXXXXXXXXXXXXXXX',
                           NVL(RPAD(LPAD('X',trunc((100-ROUND( (total_t.mb-(NVL(total.mb - free.mb,total.mb)))/(total_t.mb) * 100, 2))/5),'X'),20,'-'),
            '--------------------'))||']'
      END as GRAPH
from
      (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
      (select tablespace_name ts, sum(decode(autoextensible,'YES',maxbytes,bytes))/1024/1024 mb from dba_data_files group by tablespace_name) total_t,
      (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free
where total.ts=free.ts(+) and total_t.ts=total.ts
UNION ALL
SELECT D.tablespace_name,
            SPACE                         total_mb,
            used_space    used_mb ,
            SPACE - used_space as free_mb,
            Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)",
            '['||DECODE(SPACE - used_space,0,'XXXXXXXXXXXXXXXXXXXX',
                  NVL(RPAD(LPAD('X',(TRUNC(Round(Nvl(used_space, 0) / SPACE * 100, 2)/5)),'X'),20,'-'),'--------------------'))||']'
      FROM (SELECT tablespace_name,
                     Round(sum(decode(autoextensible,'YES',maxbytes,bytes)) / ( 1024 * 1024 ), 2) SPACE,
                     SUM(blocks)                         BLOCKS
            FROM dba_temp_files
            GROUP  BY tablespace_name) D,
            (SELECT tablespace,
                     Round(SUM(blocks * 8192) / ( 1024 * 1024 ), 2) USED_SPACE
            FROM v$sort_usage
            GROUP  BY tablespace) F
      WHERE  D.tablespace_name = F.tablespace(+)
order by 5 Desc;

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)