ORA-30036:无法按8扩展段(在还原表空间‘UNDOTBS1‘中)

前言：工作中使用Oracle经常会遇见各种问题，不是dba干了dba的活，运维也做，在这里统一归档问题原因及处理方法，供以后查阅。

1.什么是还原表空间？
还原表空间即Undo表空间，是Oracle特有的概念，Undo表空间会自动分配Undo段，用来保存事务中DML（ Insert、Update或Delete）语句的Undo数据。在Oracle9i前，管理Undo数据只能使用Rollback Segment。从Oracle9i开始，管理Undo数据不仅可以使用回滚段，还可以使用Undo表空间。而由于管理里规划回滚段太过复杂，Oracle10g已经弃用回滚段，仅使用Undo表空间来管理Undo数据。

2.什么是Undo数据？
Undo数据也称回滚数据，当执行DML语句时，事务操作过程中的数据被称为Undo数据，主要有两个作用：

**1：确保事务一致性：**如果事务发生错误或者用户想要取消数据库操作，则可以通过Rollback回到修改前的值。
**2：提供一致性读：**如表T有100条记录，用户A在表T执行了语句删除掉10条记录，尚未提交，此时用户B执行查询语句，将返回100条记录而不是90条。

3.解决方法
首先我们查看当前实例使用的Undo表空间：

show parameter undo

SQL> show parameter undo;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
1
2
3
4
5
6
7

可以看到，使用的Undo表空间为“UNDOTBS1”，与报错内容中的名字一致。接下来查看数据库可用的Undo表空间：
SELECT tablespace_name FROM dba_tablespaces WHERE contents=‘UNDO’;

SQL> SELECT tablespace_name FROM dba_tablespaces WHERE contents='UNDO';
 
TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
1
2
3
4
5
6

结果显示有两个Undo表空间可用，分别为UNDOTBS1、UNDOTBS2。我们来看看这两个表空间的使用情况：
select b.tablespace_name as “表空间”,
b.file_name as “物理文件名”,
b.bytes / 1024 / 1024 as “当前大小(M)”,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 as “已使用(M)”,
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) as “使用率(%)”,
case b.autoextensible
when ‘YES’ then ‘是’
else ‘否’
end as “是否自增”,
b.maxbytes / 1024 / 1024 as “自增最大容量(M)”
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
and a.tablespace_name in (‘UNDOTBS1’, ‘UNDOTBS2’)
group by b.tablespace_name, b.file_name, b.bytes, b.autoextensible, b.maxbytes
order by b.tablespace_name;

------查看表空间使用情况
select b.tablespace_name as "表空间",
       b.file_name as "物理文件名",
       b.bytes / 1024 / 1024 as "当前大小(M)",
       (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 as "已使用(M)",
       substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) as "使用率(%)",
       case b.autoextensible
         when 'YES' then '是'
         else '否'
       end as "是否自增",
       b.maxbytes / 1024 / 1024 as "自增最大容量(M)"
  from dba_free_space a, dba_data_files b
 where a.file_id = b.file_id
   and a.tablespace_name in ('UNDOTBS1', 'UNDOTBS2')
 group by b.tablespace_name, b.file_name, b.bytes, b.autoextensible, b.maxbytes
 order by b.tablespace_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

表空间 物理文件名 当前大小(M) 已使用(M) 使用率(%) 是否自增 自增最大容量(M)
UNDOTBS1 +DATADG/esbmssdb/datafile/undotbs1.2.dbf 8192 8140 99.36 否 0
UNDOTBS1 +DATADG/esbmssdb/datafile/undotbs1.3.dbf 90 89 98.88 否 0
UNDOTBS2 +DATADG/esbmssdb/datafile/undotbs2.2.dbf 8192 96.3125 1.175 否 0
UNDOTBS2 +DATADG/esbmssdb/datafile/undotbs2.3.dbf 5725 212.125 3.705 否 0

可以看到，UNDOTBS1有两个数据文件来存储Undo数据，使用率都到达了90%以上，所剩空间已不足。解决的办法有三个：
1.为表空间增加数据文件

alter tablespace UNDOTBS1
      add datafile '+DATADG/esbmssdb/datafile/undotbs1.4.dbf' --数据文件名
      size 100M --初始大小
      autoextend on next 1M maxsize 8192M; --自增，每次增加1M，最大为8192M
1
2
3
4

2.设置文件自动扩展

alter database 
      datafile '+DATADG/esbmssdb/datafile/undotbs1.3.dbf' 
      autoextend on next 1M maxsize 8192M;
1
2
3

3.切换Undo表空间

alter system set undo_tablespace = UNDOTBS2;
1

PS：如果数据库中只有一个可用的Undo表空间，则使用前两种方法。