记UNDOTBS1表空间过大导致磁盘空间不足的问题

一、背景

oracle19c的undotbs1表空间共20G，占用达到80%，于是于上月扩增30G，本月观察又达到了50%，翻看了其他项目的表空间，发现基本占用都在5%以下。
于是寻求回收UNDOTBS表空间的方法。

二、方法一

网上冲浪2小时，所有的方法都是创建新的UNDO表空间替换原来的UNDO表空间，并删除原表空间的物理文件，由于项目客户的特殊性，该策略无法采纳。

三、思考

为何其他客户项目没有出现这样的情况，所有的程序都保持的一致，而其他项目的Oracle版本是12C，难道是版本差异？既然其他项目的UNDO表空间持续较低，证明Oracle一定是有一个有效的清除机制！

四、清除机制

（1）undo_retention 机制

该机制有一个时间，默认为900秒，在900秒内运行完的事务会清理掉，这样就会有效的使UNDO表空间不会一直增长。

select tablespace_name, status, sum(bytes/1024/1024) "MB"
from dba_undo_extents
group by tablespace_name, status
order by 1, 2;

不同状态的含义：

ACTIVE ：有活动事务在使用 Undo，这部分空间属于Session正在使用的空间；

UNEXPIRED ：事务提交并且没到undo_retention设置时间之前，这些Undo Block还没有过期，但是已经没有活动事务在使用了，在超过undo_retention设置时间之后，这部分空间会变成EXPIRED状态；

EXPIRED ：事务提交并且到undo_retention设置时间之后，这些Undo Block已经过期了，这部分空间是可以重用的，属于未使用空间；

执行语句发现，处于 UNEXPIRED 的空间占比非常大，这本该是Oracle自动清理的，但为何没有清理，而让表空间持续增长呢？

原来是有个隐藏参数是 _undo_autotune 。

（2）_undo_autotune 机制

_undo_autotune会自动调整undo_retention的特性，其目的是为了减少ORA-01555错误出现的概率，Oracle会忽略UNDO_RETENTION参数设置的阀值，而是根据UNDO表空间的大小和使用率来自动调整UNDO信息的保留时间。会造成的影响是UNDO表空间的区（extent）中大部分都是未过期状态(unexpired)，这就会导致数据库在给事务分配UNDO块时，会优先使用UNDO表空间的的空闲空间分配，而不是覆盖已经分配的空间，这使得UNDO表空间的使用率保持在一个较高的水平。

Oracle数据库在为事务进行分配UNDO块时，会按照这样的算法和流程：
1． 如果当前区（extent）中还有空闲块，在需要空间时会继续使用本区（extent）中的空闲块。
2． 在当前区(extent)使用完后，如果下一个区（extent）是过期状态(expired)，那么就跳转到下一个区(extent)的第一个数据块。
3． 如果下一个区(extent)不是过期状态(expired)，就从UNDO表空间申请空间，如果UNDO表空间中存在空闲的空间，就分配新的区(extent)加入到undo segment，然后跳转到新区(extent)的第一个数据块。
4． 如果没有剩余空闲的区(extent),则会从OFFLINE状态的回滚段中窃取（STEAL）过期的区，加入当前的回滚段，并使用第一个数据块。
5． 如果OFFLINE状态的回滚段中没有过期的区，那么会从ONLINE状态的回滚段窃取（STEAL）过期的区加入当前的回滚段，并使用第一个数据块。
6． 如果UNDO表空间能够自动扩展，则会扩展UNDO表空间，并将新区加入到当前回滚段中。
7． 如果undo表空间数据文件不能扩展，调低10%的retention值，然后窃取（STEAL）在短保留时间的过期区，如果还未找到过期区，则继续以10%的速度减少回滚的保留时间。
8． 随机从其他OFFLINE状态的回滚段中窃取（STEAL）未过期的(unexpired)的区。
如果以上的尝试都失败，那么久会报ORA-30036错误。
从上面的步骤可以看出，事务会优先使用UNDO空闲空间、过期状态(expired)的UNDO区，然后会尝试扩展表空间的数据文件，只有在以上步骤都得不到获得UNDO表空间后，才会去使用未过期(unexpired)的UNDO区。

五、解决办法

alter system set “_undo_autotune” = false;  

一天之后，UNDOTBS已经降到了1%。