- 查看表空间占用量
select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files group by tablespace_name
union all
select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_temp_files group by tablespace_name order by GB;通常情况下,undo表空间占用量比较高,例(UNDOTBS1)
- 确认表空间UNDOTBS占用量
select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';
- 检查UNDO Segment状态
select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
- 创建新的UNDO表空间
create undo tablespace undotbs2;
- 切换UNDO表空间为新的UNDO表空间
alter system set undo_tablespace=undotbs2 scope=both;
- 等待原UNDO表空间所有UNDO SEGMENT OFFLINE
select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
- 删除原UNDO表空间
drop tablespace undotbs1 including contents;
如果要级联删除系统文件,可使用drop tablespace undotbs1 including CONTENTS and datafiles;