UNDOTBS01.DBF文件很大

来源：互联网发布：ubuntu卸载安装的qq 编辑：程序博客网时间：2024/04/19 19:08

UNDOTBS01.DBF文件很大

突然发现一硬盘分区空间少了很多，可用空间从40个G降到了10多个G，一看原来UNDOTBS01.DBF竟有31个G！

（1）以sysdba身份登陆oracle
（2）
-- 创建UNDO TABLESPACE
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'D:/ORACLE/ORADATA/ORCL/UNDOTBS02.DBF' SIZE 100M REUSE AUTOEXTEND ON;
-- 设置新表空间为UNDO_TABLESPACE
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
-- 删除旧表空间
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS;
--查看所有表空间
SELECT * FROM DBA_TABLESPACES;

完成后删除D:/ORACLE/ORADATA/ORCL/UNDOTBS01.DBF，也许此时删不掉，需要shutdown数据库后再删除.
然后进行出下操作：

（3）
-- 创建UNDO TABLESPACE
CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE 'D:/ORACLE/ORADATA/ORCL/UNDOTBS01.DBF' SIZE 100M REUSE AUTOEXTEND ON;
-- 设置新表空间为UNDO_TABLESPACE
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1;
-- 删除旧表空间
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS;
--查看所有表空间
SELECT * FROM DBA_TABLESPACES;

完成后删除D:/ORACLE/ORADATA/ORCL/UNDOTBS02.DBF，如删不掉需要shutdown数据库.

PS：关于回滚段
system undo segment is used by system maintenance, like some int ernal dml needed by recursive sql, ddl.
undo segment in undo tablespace is used by user's dml. They can and they must be used concurrently