Oracle释放过度使用的Undo表空间

以下过程：创建临时回滚表空间undotbs1t--代替undotbs1

--重新创建UNDO表空间

create undo tablespace undotbs1t datafile 'E:\HS01\udo\undotbs1t.ORA' size 4096m;

ALTER DATABASE dataFILE 'E:\HS01\udo\undotbs1t.ORA' AUTOEXTEND OFF;

--动态更改spfile配置文件，用临时undo表空间UNDOTBS0代替原undotbs1

alter system set undo_tablespace=undotbs1t scope=both;

--查看回滚表空间参数

show parameter undo

-- 删除原有的UNDO表空间；可能需要重启数据库，再删除文件undotbs11.ORA

drop tablespace UNDOTBS1 including contents;

+++

重启后再重复以上步骤，undotbs1--代替undotbs0

undotbs1t.ora-->undotbs11.ora

--重新创建UND1表空间

create undo tablespace undotbs1 datafile 'E:\HS01\udo\undotbs11.ORA' size 4096m;

ALTER DATABASE dataFILE 'E:\HS01\udo\undotbs11.ORA' AUTOEXTEND OFF;

--动态更改spfile配置文件，用临时undo表空间UNDOTBS0代替原undotbs1

alter system set undo_tablespace=undotbs1 scope=both;

-- 删除临时的UNDO表空间；可能需要重启数据库，再删除文件undotbs1t.ORA

drop tablespace UNDOTBS1t including contents;

ALTER TABLESPACE undotbs1 ADD DATAFILE 'E:\HS01\udo\undotbs12.ORA' SIZE 4096M;

+++

解决错误：ORA-30013: 还原表空间 'UNDOTBS1T' 当前正在使用中

错误：

SQL> drop tablespace UNDOTBS1t including contents;

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

ORA-30013: 还原表空间 'UNDOTBS1T' 当前正在使用中

alter database datafile 'F:\APP\ADMINISTRATOR\DATA\ORA11\UNDOTBS01.DBF' offline;

drop tablespace UNDOTBS1 including contents and datafiles;

--查看还有事务占用临时表空间

select *--SEGMENT_NAME,TABLESPACE_NAME,STATUS 

from dba_rollback_segs d

where d.tablespace_name='UNDOTBS1T' AND D.status='ONLINE'

--找到占用临时表空间session

select SID, serial#, substr(username, 1, 10) username,  segment_name,b.segment_id,b.tablespace_name,b.status

from v$transaction A, dba_rollback_segs B, v$session C

where c.SADDR = A.SES_ADDR

and A.XIDUSN = b.segment_id

and b.segment_id in 

  (select d.segment_id

  from dba_rollback_segs d

  where d.tablespace_name='UNDOTBS1T' AND D.status='ONLINE')

--kill session后再执行删除旧undo文件语句

alter system kill session 'SID,SERIAL#';

参考：https://blog.csdn.net/stevendbaguo/article/details/75434693