1、创建新的表空间

create undo tablespace undo_new datafile '/u01/oracle/oradata/yscsfhx/newundotbs01.dbf' size 5m;

View Code

2、切换到新的Undo表空间上 

alter system set undo_tablespace=undo_new scope=both; 

View Code

3、删除原来的undo 表空间，如果删不掉，可以重启库后再删。 

drop tablespace UNDOTBS1 including contents and datafiles;

View Code

3.1、执行 df -Th 发现磁盘空间并没有释放（也可以通过lsof命令查看），需重启数据库

lsof | grep deleted

View Code

startup force

View Code

4、再次查看Undo表空间

select b.FILE_NAME, b.TABLESPACE_NAME, b.blocks, b.STATUS, b.AUTOEXTENSIBLE
  from dba_data_files b
 where b.TABLESPACE_NAME like '%UNDO%';

View Code