UNDO TABLESPACE切换后，旧的长时间没有完全OFFLINE，案列：

案列：

      由于生产库impdp导入一个分区表，期间出了问题，终止了导入，但是undo tablespace增长飞快增长到300G了，且经过两天才降下来，但是undo tablespace如果是自动扩展，只会扩展，当事务都结束了它自己也不会收缩的，所以只能手工去收缩。

      于是创建一组新的undo tablespace，命名为undotbs2

alter system set undo_tablespace=undotbs2;

在线切换了undo tablespace;

 

     手工切换完了但是还不能立即将旧的undo tablespace（undotbs1）执行offline操作，因为在dba_rollback_segs中undotbs1还有15个undo segment是ONLINE状态，只能等全部OFFLINE了才能执行undotbs offline操作。等这些undo segment离线等了好几天也没有全部OFFLINE，如下查询还是存在15个undo segment没有OFFLINE：

select * from dba_rollback_segs where tablespace_name='UNDOTBS1' and status='ONLINE';

 

而且alter日志报如下日志：

Mon Jul 13 16:55:43 2014
[20481] **** active transactions found in undo Tablespace 2 - moved to Pending Switch-Out state.

 

最后想去找找现在旧的undo tablespace到底是在干什么，为什么一直不offline，带着这个目的，写了条sql去查，如下：

SELECT r.NAME,

       s.USERNAME,

       h.tablespace_name,

       s.sid,

       s.serial# Serial,

       s.username,

       s.machine,

       t.start_time,

       t.status,

       t.used_ublk,

       substr(s.program, 1 , 30) "operate",

       s.prev_SQL_ID,s.STATUS,i.sql_text

  FROM v$session         s,

       v$transaction     t,

       v$rollname        r,

       v$rollstat        g,

       dba_rollback_segs h,

       v$sqlarea i

 WHERE t.addr = s.taddr

   AND t.xidusn = r.usn

   AND r.usn = g.usn

   and r.name = h.segment_name

   AND s.PREV_SQL_ID=i.SQL_ID

 ORDER BY 3 asc;

 

不查不知道一查吓一跳，对应得UNDOTBS1的session在v$session中全部是INACTIVE状态，鉴于此是INACTIVE状态，决定删除所有对于UNDOTBS1的session并且状态为INACTIVE的，写了如下批量kill session:

 

SELECT 'alter system kill session '''||

       s.sid|| ','||

       s.serial#|| ''''||';'

  FROM v$session         s,

       v$transaction     t,

       v$rollname        r,

       v$rollstat        g,

       dba_rollback_segs h,

       v$sqlarea i

 WHERE t.addr = s.taddr

   AND t.xidusn = r.usn

   AND r.usn = g.usn

   and r.name = h.segment_name

   AND s.PREV_SQL_ID=i.SQL_ID and h.tablespace_name='UNDOTBS1' and s.status='INACTIVE' ;

得到查询结果，复制下来批量执行;

 

再次去查询dba_segments

select * from dba_rollback_segs where tablespace_name='UNDOTBS1' and status='ONLINE';

已经查询不到结果了，说明已经全部OFFLINE了。

 

alter日志也报了successfully如下：

Mon Jul 13 16:58:13 2014
[20481] Undo Tablespace 2 successfully switched out.

 

到此UNDOTBS1可以执行offline操作了，然后删除UNDOTBS1表空间及数据文件，腾出可用空间。