检查数据库表空间占用空间情况:
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;
确认文件:
select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'undo';
检查undo segment状态:
select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
创建新的undo表空间:
create undo tablespace undo1 datafile '/usr/lib/oracle/xe/oradata/xe/undo1.dbf' size 100m reuse autoextend on next 50m maxsize unlimited;
切换undo表空间为新的undo表空间:
alter system set undo_tablespace=undo1 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 undo including contents;
确认删除是否成功:
select name from v$tablespace;
删除文件
$ rm -f /usr/lib/oracle/xe/oradata/xe/undo.dbf
阅读(4686) | 评论(0) | 转发(0) |