oracle： Undo Tablespace 1 moved to Pending Switch-Out state

数据库后台alert文件报错

ORA-01555 caused by SQL statement below (SQL ID: bnkmzq919pn8w, Query Duration=0 sec, SCN: 0x0001.025cbc5b):
ORA-01555 caused by SQL statement below (SQL ID: bnkmzq919pn8w, Query Duration=0 sec, SCN: 0x0001.19c3676a):

UNDOTBS1表空间不足，添加新的UNDO表空间UNDOTBS3

因为UNDOTBS1中还有活动事务，在迁移的过程中会在alert文件中出现以下的信息

Mon Dec 12 10:18:27 2011
Undo Tablespace 1 moved to Pending Switch-Out state.
Mon Dec 12 10:20:58 2011
Undo Tablespace 1 moved to Pending Switch-Out state.
Mon Dec 12 10:23:29 2011
Undo Tablespace 1 moved to Pending Switch-Out state.
Mon Dec 12 10:26:00 2011
Undo Tablespace 1 moved to Pending Switch-Out state.
Mon Dec 12 10:28:31 2011
Undo Tablespace 1 moved to Pending Switch-Out state.

查询以下还有哪里回滚段在使用

SQL> select segment_name,owner,tablespace_name,status
  2  from dba_rollback_segs
  3  where tablespace_name = 'UNDOTBS1' and status = 'ONLINE';

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU7$                      PUBLIC UNDOTBS1
ONLINE

_SYSSMU21$                     PUBLIC UNDOTBS1
ONLINE

继续等待UNDO表空间迁移完成就可以了。

Mon Dec 12 10:31:03 2011
Undo Tablespace 1 successfully switched out.

当出现上述的信息的时候 说明UBDOTBS1已经成功迁移完毕。

也可以使用下面的语句查出是哪个用户在使用

select s.username,s.status,s.sid, 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;