问题解决ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1‘

1.查询undotbs1是否存在

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         

2.根据undotbs1查询对应的数据文件位置

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/local/oracle/oradata/orcl/undotbs01.dbf		 UNDOTBS1	 YES	  640   .903320313	31.9999847

3.解决方法：
扩充表空间（注意：空间最大只能31G）

alter database datafile '/usr/local/oracle/oradata/orcl/undotbs01.dbf' resize 20480m ;

4.检查扩充后的空间

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/local/oracle/oradata/orcl/undotbs01.dbf								                       UNDOTBS1    YES	      640	    20	31.9999847