前几天在帮开发人员往中间库迁移一批数据的时候，遇到UNDO表空间无法自动扩展的问题，告警日志如下：

Tue Jul 21 11:26:13 2015
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was resumed
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'
Tue Jul 21 11:27:21 2015
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was resumed
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'
Tue Jul 21 11:31:29 2015
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was resumed
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'
Tue Jul 21 11:33:52 2015
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was resumed
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'
Tue Jul 21 11:34:14 2015
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was resumed
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'

看到UNDO表空间不足，很自然的往UNDO表空间加了一个数据文件，并没有仔细看错误信息，在加文件的时候还遇到了数据文件已存在的问题。

sys@IVLDB(10.0.97.40)> select tablespace_name,file_name from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME			FILE_NAME
---------------			------------------------------------------
UNDOTBS1				/data/oracle/datafile/IVLDB/undotbs01.dbf

sys@IVLDB(10.0.97.40)> alter tablespace UNDOTBS1 add datafile '/data/oracle/datafile/IVLDB/undotbs02.dbf' size 5G autoextend on;
alter tablespace UNDOTBS1 add datafile '/data/oracle/datafile/IVLDB/undotbs02.dbf' size 5G autoextend on
*
ERROR at line 1:
ORA-01537: cannot add file '/data/oracle/datafile/IVLDB/undotbs02.dbf' - file already part of
database

去操作系统查看，发现的确有好多的UNDO相关的数据文件。

[oracle@SL010A-IVO03 trace]$ cd /data/oracle/datafile/IVLDB/
[oracle@SL010A-IVO03 IVLDB]$ ll undo*
-rw-r----- 1 oracle oinstall 34359730176 Jul 21 11:31 undotbs01.dbf
-rw-r----- 1 oracle oinstall 10737426432 Jul 21 11:31 undotbs02.dbf
-rw-r----- 1 oracle oinstall 21474844672 Jul 21 11:31 undotbs03.dbf
-rw-r----- 1 oracle oinstall 32212262912 Jul 21 11:40 undotbs04.dbf

啥情况，难道是遗留的？这个表空间明明只有一个数据文件，先加上在说。

sys@IVLDB(10.0.97.40)> alter tablespace UNDOTBS1 add datafile '/data/oracle/datafile/IVLDB/undotbs05.dbf' size 5G autoextend on;

Tablespace altered.

加完之后，突然想到，是不是数据库有多个UNDO表空间，查看下发现还真有好几个。

sys@IVLDB(10.0.97.40)> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
TEST
TS_WORK
TS_IDX
UNDOTB2
UNDOTB1

10 rows selected.

那么数据库用的是哪个呢？

sys@IVLDB(10.0.97.40)> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTB1

坑爹啦，数据库用的竟然不是默认的UNDO表空间，这是什么情况？数据库建3个undo表空间干什么？只能用一个呀，这是个单实例的数据库，猜测可能是添加UNDO表空间的人把UNDO和TEMP表空间搞混了，以为每个用户都可以使用不同的UNDO表空间。可是当前服务器的磁盘空间已经很紧张了，直接删掉无用的UNDO表空间。

sys@IVLDB(10.0.97.40)> drop tablespace UNDOTB2  including contents and datafiles;

Tablespace dropped.

sys@IVLDB(10.0.97.40)> alter tablespace UNDOTB1 add datafile '/data/oracle/datafile/IVLDB/undotbs02.dbf' size 5G autoextend on;

Tablespace altered.

sys@IVLDB(10.0.97.40)> drop tablespace  UNDOTBS1 including contents and datafiles;

Tablespace dropped.

sys@IVLDB(10.0.97.40)> select tablespace_name,file_name from dba_data_files where tablespace_name='UNDOTB1'; 

TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------
UNDOTB1                        /data/oracle/datafile/IVLDB/undotbs02.dbf
UNDOTB1                        /data/oracle/datafile/IVLDB/undotbs04.dbf

这个项目跟久了，奇葩问题无处不在。

本文固定链接: https://www.dbdream.com.cn/2015/07/undo%e8%a1%a8%e7%a9%ba%e9%97%b4%e6%97%a0%e6%b3%95%e6%89%a9%e5%b1%95%e5%8f%91%e7%8e%b0%e7%9a%84%e9%97%ae%e9%a2%98/ | 信春哥，系统稳，闭眼上线不回滚！