oracle手工释放undo,Linux下释放 Oracle 数据库 UNDO 表空间

检查数据库表空间占用空间情况:

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

阅读(4133) | 评论(0) | 转发(0) |