ORACLE 11G 回滚表空间undo满了处理方法

一.加大undo表空间：

　　扩展数据文件大小

show parameter undo                     　　　　　　　　 　　　　　　　　　　　　　　--查看默认回滚表空间名(需在命令行下执行)
select tablespace_name,bytes/1024/1024,file_name from dba_data_files;  　　　　--查看表空间及对应的数据文件大小
alter database datafile '+DATA/rac/datafile/undotbs1.dbf' resize 30720M;　　　 --修改数据文件大小

　　增加数据文件

show parameter undo                      　　　　　　　　  --查看默认回滚表空间名(需在命令行下执行)
select tablespace_name,bytes/1024/1024,file_name from dba_data_files;　--查看表空间及对应的数据文件大小
alter tablespace undotbs1 add datafile '+DATA/rac/datafile/undotbs1_2.dbf' size 30G;   --增加数据文件

 

二.切换undo表空间：

　　查看默认回滚表空间名(需在命令行下执行)

              

select name from v$datafile 

　　创建undotbs11并将默认回滚表空间切换到undotbs11

create undo tablespace undotbs11 datafile '+DATA/rac/datafile/undotbs11_1.dbf' size 30G;
alter system set undo_tablespace=undotbs11 scope=both;

　　查看是否已切换

show parameter undo

　　查看正在使用的回滚表空间

select t.segment_name, t.tablespace_name, t.segment_id, t.status from dba_rollback_segs t;

　　生成参数文件

create pfile from spfile;

　　查看UNDOTBS1是否还在使用，当UNDOTBS1无ONLINE时删除UNDOTBS1

select t.segment_name, t.tablespace_name, t.segment_id, t.status from dba_rollback_segs t where t.tablespace_name='UNDOTBS1' and t.status='ONLINE';
drop tablespace undotbs1 including contents and datafiles;

　　查看正在使用回滚段的用户：

select s.username, u.name
  from v$transaction t, v$rollstat r, v$rollname u, v$session s
 where s.taddr = t.addr
   and t.xidusn = r.usn
   and r.usn = u.usn
 order by s.username;

 

三.查看undo段中区的状态：

select tablespace_name, status, sum(bytes) / 1024 / 1024 MB
  from dba_undo_extents
 group by tablespace_name, status;

select TABLESPACE_NAME,STATUS,CONTENTS,SEGMENT_SPACE_MANAGEMENT,RETENTION from dba_tablespaces where CONTENTS='UNDO';

free：     区未分配给任何一个段

active：   已经被分配给段，并且这个段被事务所使用，且事务没有提交，不能覆盖。 （区被未提交的事务使用）   

unexpired：事务已经提交，但是区还在段中，还没有被覆盖且未达到undo_retention设定的时间。

　　　　   （nogurantee的情况下，原则上oracle尽量的不覆盖unexpired的区，但是如果undo空间压力及较大，oracle也会去覆盖。如果是guarantee，oracle强制保留retention时间内的内容，这时候free和expired空间不足的话，新事物将失败。）

expired：  oracle希望已经提交的事务对应的undo表空间中的undo段中的区再保留一段时间。保留的时间就是undo_retention。

　　　　    unexpired的区存在时间超过undo_retention设定的时间，状态就会变为expired。expired为可重用undo空间（如果undo表空间100%，但是expired的空间还有，就不要紧），原则上expired的区一般不会释放成free。

 

PS：生产中没有人会将UNDOTBS的retention设置成GUARANTEE这是很危险的。

 

与一般的用户表空间不同，undo表空间不能通过dba_free_spaces来确定实际的使用情况，undo表空间除了active状态的extent不能被覆盖外。其他状态的extent都是可以空间复用的。

如果active的extent总大小很大，说明系统中存在大事务。如果undo资源耗尽（ACTIVE接近undotbs的总大小），可能导致事务失败。