ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

  有时候会因为超大的事务导致undo表空间太大，需要收缩一下。

create undo tablespace UNDOTBS2 datafile 'F:\APP\ADMINISTRATOR\DATA\ORA11\UNDOTBS02.DBF' size 100m;

alter system set undo_tablespace=UNDOTBS2;
--下面报错ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
alter database datafile 'F:\APP\ADMINISTRATOR\DATA\ORA11\UNDOTBS01.DBF' offline;
drop tablespace UNDOTBS1 including contents and datafiles;
--查看还有事务
select * from dba_rollback_segs
where tablespace_name='UNDOTBS1' and status = 'ONLINE';
--找到session
select SID, substr(username, 1, 10) username, serial#, segment_name
   from v$transaction, dba_rollback_segs, v$session
  where saddr = ses_addr
    and xidusn = segment_id;
--kill session后再执行删除旧undo文件语句
 alter system kill session 'SID,SERIAL#';