生产库回收undo表空间大小

--新建一个新的UNDO表空间
create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 2000M  autoextend on next 100M;
--切换回滚表空间，不会影响回滚数据:
alter system set undo_tablespace=’UNDOTBS2′ scope=both;
--查看系统默认undo表空间
show parameter undo;
--确认原回滚表空间是否已经offline:
select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;  --undo1 都offline
--删除原undo表空间及数据文件：
drop tablespace undotbs1 including contents and datafiles;

 

select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME         SEGMENT_NAME        STATUS
------------------------------ ------------------------------ ----------------
SYSTEM          SYSTEM         ONLINE
UNDOTBS2         _SYSSMU139_3514333428$       ONLINE
UNDOTBS2         _SYSSMU138_2448299858$       ONLINE
UNDOTBS2         _SYSSMU137_1165700344$       ONLINE
UNDOTBS2         _SYSSMU136_1319068845$       ONLINE
UNDOTBS2         _SYSSMU135_2267349081$       ONLINE
UNDOTBS2         _SYSSMU134_521662916$       ONLINE
UNDOTBS2         _SYSSMU133_3396602728$       ONLINE
UNDOTBS2         _SYSSMU132_4270171473$       ONLINE
UNDOTBS2         _SYSSMU131_2047393118$       ONLINE
UNDOTBS2         _SYSSMU130_2632506662$       ONLINE
UNDOTBS2         _SYSSMU129_1954850242$       ONLINE
UNDOTBS2         _SYSSMU128_2228536284$       ONLINE
UNDOTBS2         _SYSSMU127_1381675395$       ONLINE
UNDOTBS2         _SYSSMU126_2918684817$       ONLINE
UNDOTBS2         _SYSSMU125_1252524545$       ONLINE
UNDOTBS2         _SYSSMU124_427434368$       ONLINE
UNDOTBS2         _SYSSMU123_4291642484$       ONLINE
UNDOTBS2         _SYSSMU122_4099218469$       ONLINE
UNDOTBS2         _SYSSMU121_3131704156$       ONLINE
UNDOTBS2         _SYSSMU120_4197949147$       ONLINE
UNDOTBS2         _SYSSMU119_3781728064$       ONLINE
UNDOTBS2         _SYSSMU118_234547938$       ONLINE

23 rows selected.

SYS@orcl>SYS@orcl>