如何削减oracle的回滚空间

--查看回滚空间文件大小

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;

--1.确认文件
select file_name,bytes/1024/1024 from dba_data_files
 where tablespace_name like 'UNDOTBS1';

--2.检查UNDO Segment状态
select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
 from v$rollstat order by rssize;

--3.创建新的UNDO表空间
create undo tablespace undotbs2
datafile 'd:\oradata\eygle\undotbs2.dbf' size 10M;

--4.切换UNDO表空间为新的UNDO表空间
alter system set undo_tablespace=undotbs2 scope=both;

--5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE
select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
 from v$rollstat order by rssize;

--6.删除原UNDO表空间
 drop tablespace undotbs1 including contents;