undo100% 重建undo

最新推荐文章于 2022-11-11 09:10:53 发布

pool_Sneaker

最新推荐文章于 2022-11-11 09:10:53 发布

阅读量239

点赞数

分类专栏： oracle

本文链接：https://blog.youkuaiyun.com/evan_cao_it/article/details/89315344

版权

oracle 专栏收录该内容

14 篇文章

订阅专栏

当Oracle数据库的Undo表空间达到100%时，可能需要进行重建以优化性能。本文详细介绍了重建过程，包括创建新的表空间，切换默认表空间，确认并使旧表空间离线，删除旧表空间及其操作系统文件，最后恢复原Undo表空间。该操作需谨慎，确保所有事务已提交且无其他实例使用旧表空间。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原因：

undo表空间已分配60G，还是报100%；虽然没影响，还是通过设置为自动扩展.
但是对于自动扩展的文件还有监控来判断是否还有可分配的空间。所以需要重建。

步骤：

1.创建默认表空间

create UNDO tablespace UNDOTBS3 DATAFILE ‘/u02/oradata/xxxx/undotbs03.dbf’ size 5G autoextend on maxsize 30G ;

2.切换默认表空间

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS3 scope=both;

手册特别提醒了，需要注意：

If the parameter value for UNDO TABLESPACE is set to ‘’ (two single quotes), then the current undo tablespace is switched out and the next available undo tablespace is switched in. Use this statement with care because there may be no undo tablespace available

3.确认所有的 seg都是offline

select status,count( * ) from dba_rollback_segs where tablespace_name=‘UNDOTBS1’
group by status;

需要确认所有事物已经提交，所有的都是 offline
An undo tablespace can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo tablespace cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo tablespace automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo tablespace is available for other instances (in an Oracle Real Application Cluster environment)

4.删除表空间

drop tablespace UNDOTBS1 INCLUDING CONTENTS ;

5.删除操作系统原有文件

rm -f undotbs01.dbf
rm -f undotbs02.dbf

6 重复上面步骤切换成undotbs1

create UNDO tablespace UNDOTBS1 DATAFILE ‘/u02/oradata/xxxx/undotbs01.dbf’ size 5G autoextend on maxsize 30G ;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1 scope=both;

select status,count( * ) from dba_rollback_segs where tablespace_name=‘UNDOTBS3’
group by status;
drop tablespace UNDOTBS3 INCLUDING CONTENTS ;