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

最新推荐文章于 2025-03-27 13:50:08 发布

袭冷

最新推荐文章于 2025-03-27 13:50:08 发布

阅读量1w

点赞数 1

分类专栏： DB 文章标签： Oracle ORA-01652

本文链接：https://blog.youkuaiyun.com/guoxilen/article/details/78613085

版权

DB 专栏收录该内容

52 篇文章

订阅专栏

当遇到ORA-01652错误时，意味着TEMP表空间不足以扩展临时段。可能的原因包括：TEMP表空间大小不足或无法自动扩展。解决方案通常涉及增加表空间大小或调整自动扩展设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、原因

意思是指TEMP表空间无法自动扩展TEMP段。这种问题一般有两种原因：一是临时表空间空间太小，二是不能自动扩展。

二、分析
查看TEMP表空间的数据文件个数，当前大小，是否自动扩展

SQL> 
SELECT 
    TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 "CURR_SIZE(MB)", MAXBYTES/1024/1024 "MAX_SIZE(MB)",AUTOEXTENSIBLE 
FROM 
    DBA_TEMP_FILES;

TABLESPACE_NAME           FILE_NAME                                            CURR_SIZE(MB)  MAX_SIZE(MB) AUT
------------------------- ---------------------------------------------------- ------------- ------------- ---
TEMP                      /usr/oracle/oradata/MyOrclDb/temp.257.894453839         32767.9844    32767.9844 YES
TEMP                      /usr/oracle/oradata/MyOrclDb/temp.288.909606971              30720             0 NO
TEMP                      /usr/oracle/oradata/MyOrclDb/temp.289.909606981              30720             0 NO

查看TEMP临时表空间使用状况，剩余空间的大小
通过DBA_TEMP_FREE_SPACE表查询

SELECT 
    TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024 AS "TABLESPACE_SIZE(MB)", 
    ALLOCATED_SPACE/1024/1024 AS "ALLOCATED_SIZE(MB)", FREE_SPACE/1024/1024 AS "FREE_SIZE(MB)", 
    round(((TABLESPACE_SIZE - FREE_SPACE)/TABLESPACE_SIZE)*100,2) AS "USED_RATE(%)"
FROM 
    DBA_TEMP_FREE_SPACE;
    
TABLESPACE_NAME          TABLESPACE_SIZE(MB) ALLOCATED_SIZE(MB) FREE_SIZE(MB) USED_RATE(%)
------------------------ ------------------- ------------------ ------------- ------------
TEMP                                    5350               5350          4351        18.67

通过SORT_SEGMENT和V$TEMPFILE查询

SELECT 
    SEG.TABLESPACE_NAME, TPSF.TOTAL_BYTES/1024/1024 "SPACE_SIZE(MB)", 
    SUM(SEG.USED_BLOCKS * TPSF.BLOCK_SIZE)/1024/1024 "USED_SIZE(MB)", 
    (TPSF.TOTAL_BYTES - SUM(SEG.USED_BLOCKS * TPSF.BLOCK_SIZE))/1024/1024 "FREE_SIZE(MB)", 
    round((SUM(SEG.USED_BLOCKS * TPSF.BLOCK_SIZE)/TPSF.TOTAL_BYTES)*100,2) as "USED_TATE(%)" 
FROM 
    V$SORT_SEGMENT SEG, 
    (SELECT 
        TBS.NAME, TPF.BLOCK_SIZE, SUM(TPF.BYTES) AS TOTAL_BYTES 
    FROM 
        V$TABLESPACE TBS, V$TEMPFILE TPF 
    WHERE 
        TBS.TS# = TPF.TS# 
    GROUP BY 
        TBS.NAME, TPF.BLOCK_SIZE 
    ) TPSF 
WHERE 
    SEG.TABLESPACE_NAME = TPSF.NAME 
GROUP BY 
    SEG.TABLESPACE_NAME, TPSF.TOTAL_BYTES; 
    
TABLESPACE_NAME             SPACE_SIZE(MB) USED_SIZE(MB) FREE_SIZE(MB) USED_TATE(%)
--------------------------- -------------- ------------- ------------- ------------
TEMP                                  5350           998          4352        18.65