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
【版權聲明】