Oracle
Allen 2020-05-25 09:18:42 25480 0 0 0 0

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


Tag: Oracle维护 Undo OracleSql
我也要发一个   ·   返回首页   ·   返回[Oracle]   ·   前一个   ·   下一个
欢迎评论
未登录,
请先 [ 注册 ] or [ 登录 ]
(一分钟即可完成注册!)
返回首页     ·   返回[Oracle]   ·   返回顶部