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

一、原因

    意思是指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