oracle 收缩undo表空间

新建UNDOTBS2表空间:

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SIXF\UNDOTBS02.DBF' SIZE 200M AUTOEXTEND OFF
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

切换到新建的UNOD表空间上来:

alter system set undo_tablespace=undotbs2 scope=both;

将原来的UNDOTBS1表空间,置为脱机:

alter tablespace undotbs1 offline;

删除原来的UNDO表空间:

drop tablespace undotbs1 
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;

此时原来UNDOTBS1表空间所对应的操作系统文件UNDOTBS01.DBF并没有释放,而且系统也不允许手工删除

重启数据库

shutdown immediate
startup