在执行数据入库脚本时报错：

SQL state [99999]; error code [30036]; ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1’

报错的含义是，UNDO表空间不足，在UNDO表空间 'UNDOTBS1' 中分配额外的存储空间用来存放回滚数据时，空间不足。

UNDO表的作用

Undo 表存储了已提交和未提交事务所产生的旧数据版本，在需要回滚事务或实现并发控制时使用。每当对数据库进行DML操作时（例如插入、更新或删除记录），Oracle 就会在 undo 表中保存之前数据的副本，在需要的时候回滚。

1. 事务回滚： 当事务需要回滚时，Oracle 可以使用 undo 表中的数据来还原到事务开始之前的状态，确保数据的一致性和完整性。

2. 并发控制： 在并发访问数据库时，多个事务可能同时修改相同的数据。Undo 表允许数据库保持一致性，确保每个事务在读取和修改数据时都能看到正确的数据版本。

3. 读一致性： 当一个事务正在修改数据时，其他事务可能需要读取一致性的数据。Undo 表使得其他事务可以读取在当前事务修改之前的数据版本，从而实现读一致性。

4. 闪回查询： 使用闪回查询功能，可以利用 undo 表中的数据来恢复数据库中的特定时间点的数据状态，而无需从备份进行还原。

产生的原因主要以下原因：

• 有较大的事务量让Oracle undo自动扩展，产生过度占用磁盘空间的情况；

• 有较大事务没有收缩或没有提交所导制空间被占用；

• 长时间运行的查询/UNDO表空间大小设置不当/大型批处理操作/UNDO保留时间设置不当/事务活动频繁等

一般有两种方法解决

• 扩展UNDO表空间；可通过扩展数据文件表空间大小和增加数据文件两种

• 1.扩展数据文件大小：

select tablespace_name,bytes/1024/1024,file_name from dba_data_files; 　　　　--查看表空间及对应的数据文件大小 
alter database datafile '+DATA/rac/datafile/undotbs1.dbf' resize 30720M;　　　 

• 2.增加数据文件数量：

select tablespace_name,bytes/1024/1024,file_name from dba_data_files;　--查看表空间及对应的数据文件大小 
alter tablespace undotbs1 add datafile '+DATA/rac/datafile/undotbs1_2.dbf' size 30G;   --增加数据文件

• 新建一UNDO表空间，切换默认UNDO表空间到新建表空间中，删除旧的UNDO表空间

  • 具体看处理过程：

处理过程

查看Undo库文件：

select name from v$tablespace;

查看默认回滚表空间名：这里显示的UNDOTBS1

SELECT value AS default_undo_tablespace FROM v$parameter WHERE name = 'undo_tablespace'; 

查看数据库UNDO表空间数据文件在服务器存放位置

SELECT tablespace_name, ROUND(bytes / (1024 * 1024 * 1024), 2) AS size_gb,file_name FROM dba_data_files;

SELECT d.tablespace_name,
       ROUND((NVL(a.total, 0) - NVL(f.free, 0)) / 1024 / 1024, 2) AS used_undo_space_mb,
       ROUND(NVL(f.free, 0) / 1024 / 1024, 2) AS free_undo_space_mb,
       ROUND(NVL(a.total, 0) / 1024 / 1024, 2) AS total_undo_space_mb,
       ROUND(NVL(a.total, 0) / 1024 / 1024 - NVL(f.free, 0) / 1024 / 1024, 2) AS remaining_undo_space_mb
  FROM dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND d.contents = 'UNDO';

查看服务器发现占用是32G，在Oracle中表空间数据文件最大是32G，也就是说明Undo的表空间已经扩充到最大了

查看看正在使用回滚段资源的用户：防止有其他用户使用时删除文件，确认不影响生产

 SELECT s.username,
  s.program,
  rs.segment_name AS rollback_segment
 FROM v$session s
 JOIN v$transaction t ON s.saddr = t.ses_addr
 JOIN dba_rollback_segs rs ON t.xidusn = rs.segment_id
 ORDER BY rs.segment_name;```</pre>

• username：正在使用回滚段资源的用户的用户名。

• program：正在使用回滚段资源的会话所关联的程序或应用程序的名称。

• rollback_segment：回滚段的名称，即正在被会话使用的回滚段的标识。

查看UNDOTBS1的状态是否有ONLINE值，如果返回ONLINE说明还在使用，如果返回空或OFFLINE说明是没有在使用的：确认没有数据需要回滚后执行后续操作

select t.segment_name, t.tablespace_name, t.segment_id, t.status from dba_rollback_segs t where t.tablespace_name='UNDOTBS1' and t.status='ONLINE';

创建新的UNDO表空间，并设置自动扩展参数

create undo tablespace undotbs2 datafile '/opt/data/oracle/oradata/ORCLDB/pdb/UNDOTBS2.dbf' size 1000M;

切换UNDO表空间为新的UNDO表空间

alter system set undo_tablespace=undotbs2 scope=both;

再次查看默认的undo表空间

SELECT value AS default_undo_tablespace FROM v$parameter WHERE name = 'undo_tablespace';`

等待原UNDO表空间所有 UNDOTBS1 status 变成 OFFLINE

select t.segment_name, t.tablespace_name, t.segment_id, t.status from dba_rollback_segs t where t.tablespace_name='UNDOTBS1' and t.status='ONLINE';

删除名为 undotbs1 的表空间，同时删除该表空间中包含的所有内容和数据文件。注：删除操作需谨慎

drop tablespace undotbs1 including contents and datafiles;

问题解决

后续问题更新 :: undo表空间存在累加情况，导致占用过大空间

查看运行中的事务

select s.sid,s.serial#,s.sql_id,v.usn,segment_name,r.status, v.rssize/1024/1024 mb 
From dba_rollback_segs r, v$rollstat v,v$transaction t,v$session s Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr order by mb desc ;

可以看到SQL_ID

image.png

根据SQL_ID查询执行的事务

select sql_text from v$sql where sql_id='900t00u5aybts';

可以看到在执行DELETE 操作 删除时间超过30天的数据：这个的问题在于delete操作之后没有自动提交，导致数据一直存在于回滚空间，这里可以停掉这个任务根据业务需求使用其他的语句清理过期数据

image.png

ALTER SYSTEM KILL SESSION '1826,9829';

查看status状态

SELECT s.sid, s.serial#, s.username, s.osuser, s.status, t.start_time
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr AND t.xidusn > 0 AND s.sql_id = '900t00u5aybts';

可以看到已经在KILLED了

image.png