Oracle - ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

一、原因
    有較大的事務量讓 Oracle Undo 自動擴充套件，產生過度佔用磁碟空間的情況
    有較大事務沒有收縮或提交，導致沒有足夠大的空間來容納新事務的 Undo 資料

二、分析
    檢視當前例程正在使用的UNDO表空間

SQL> Show parameter undo_tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS1

    檢視錶空間已經已分配磁碟的空間的使用情況

SQL> 
SELECT 
    A.TABLESPACE_NAME, A.BYTES/1024/1024 "DISK_SIZE(MB)", (A.BYTES-B.BYTES)/1024/1024 "USED_SIZE(MB)", 
    B.BYTES/1024/1024 "FREE_SIZE(MB)", ROUND(((A.BYTES-B.BYTES)/A.BYTES)*100,2) "USED_RATE(%)" 
FROM 
    (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, 
    (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES, MAX(BYTES) LARGEST FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B 
WHERE 
    A.TABLESPACE_NAME=B.TABLESPACE_NAME;
    
TABLESPACE_NAME                DISK_SIZE(MB) USED_SIZE(MB) FREE_SIZE(MB) USED_RATE(%)
------------------------------ ------------- ------------- ------------- ------------
SYSTEM                                  9452      741.1875     8710.8125         7.84
AUDIT                                 204800        179401         25399         87.6
USERS                                  11024     3020.1875     8003.8125         27.4
UNDOTBS2                          94207.9844    1888.04688    92319.9375            2
SYSAUX                                  9802     1971.4375     7830.5625        20.11
UNDOTBS1                          94207.9844    15033.8594     79174.125        15.96
XLGDATA                           560127.969    540417.406    19710.5625        96.48         

    檢視當前Undo表空間的所有資料檔案佔用的磁碟大小、是否自動擴充套件、擴充套件的最大值等資訊

SQL> 
SELECT 
    FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, INCREMENT_BY, 
    BYTES/1024/1024/1024 "DISK_SIZE(G)", MAXBYTES/1024/1024/1024 "MAX_SIZE(G)" 
FROM 
    DBA_DATA_FILES 
WHERE 
    TABLESPACE_NAME='UNDOTBS1'; 
    
FILE_NAME                                                TABLESPACE_NAME      AUT  INCREMENT_BY DISK_SIZE(G) MAX_SIZE(G)
-------------------------------------------------------- -------------------- ---- ------------ ------------ -----------
/usr/oracle/oradata/MyOrclDb/undotbs1.271.894453849      UNDOTBS2             YES          6400   31.9999847  31.9999847
/usr/oracle/oradata/MyOrclDb/undotbs1.303.938602825      UNDOTBS2             NO              0           30           0
/usr/oracle/oradata/MyOrclDb/undotbs1.304.938602889      UNDOTBS2             NO              0           30           0