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

淩亂°似流年 2022-06-12 08:19 939阅读 0赞

有时候会因为超大的事务导致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#‘;

发表评论

表情：
评论列表 （有 0 条评论，939人围观）

还没有评论，来说两句吧...

相关阅读