今天在做数据迁移的时候发现磁盘空间完全被吃完，查看undo表空间竟然占了25G。这里记录重建undo表空间过程。
1、重建undo表空间undotbs2
SQL> create undo tablespace undotbs2 datafile 'D:\ora11g\undotbs2_01.dbf' size 1G autoextend on next 500M maxsize unlimited;
表空间已创建。
2、修改undo_tablespace参数，该参数必须设置
SQL> alter system set undo_tablespace=undotbs2 scope=both;
系统已更改。
查看切换后的状态：
SQL> show parameter undo_tablespace
UNDO_TABLESPACE
---------------
UNDOTBS2
3、查看原undo表空间UNDOTBS1各个undo段状态，当状态都为offline时则可以将该undo表空间删除
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU19_1323384053$ UNDOTBS1 OFFLINE
_SYSSMU18_2053914518$ UNDOTBS1 OFFLINE
_SYSSMU17_1486782901$ UNDOTBS1 OFFLINE
_SYSSMU16_65961640$ UNDOTBS1 OFFLINE
_SYSSMU15_2731980009$ UNDOTBS1 OFFLINE
_SYSSMU14_1272261740$ UNDOTBS1 OFFLINE
_SYSSMU13_3931036527$ UNDOTBS1 OFFLINE
_SYSSMU12_277474270$ UNDOTBS1 OFFLINE
_SYSSMU11_1127233206$ UNDOTBS1 OFFLINE
_SYSSMU10_2010384090$ UNDOTBS1 OFFLINE
_SYSSMU9_3385418537$ UNDOTBS1 OFFLINE
_SYSSMU8_960788264$ UNDOTBS1 OFFLINE
_SYSSMU7_2218399740$ UNDOTBS1 OFFLINE
_SYSSMU6_4013851069$ UNDOTBS1 OFFLINE
_SYSSMU5_14816259$ UNDOTBS1 OFFLINE
_SYSSMU4_3531847310$ UNDOTBS1 OFFLINE
_SYSSMU3_3898657637$ UNDOTBS1 ONLINE
_SYSSMU2_2881644447$ UNDOTBS1 OFFLINE
_SYSSMU1_1093178884$ UNDOTBS1 OFFLINE
_SYSSMU43_2526432190$ UNDOTBS2 ONLINE
_SYSSMU42_1073097992$ UNDOTBS2 ONLINE
_SYSSMU41_4185790622$ UNDOTBS2 ONLINE
_SYSSMU40_4075868025$ UNDOTBS2 ONLINE
_SYSSMU39_1225046965$ UNDOTBS2 ONLINE
_SYSSMU38_1416928319$ UNDOTBS2 ONLINE
_SYSSMU37_425218863$ UNDOTBS2 ONLINE
_SYSSMU36_2283506252$ UNDOTBS2 ONLINE
_SYSSMU35_3741949710$ UNDOTBS2 ONLINE
_SYSSMU34_3108701464$ UNDOTBS2 ONLINE
_SYSSMU33_1440052939$ UNDOTBS2 ONLINE
_SYSSMU31_867868996$ UNDOTBS2 ONLINE
_SYSSMU30_4206031811$ UNDOTBS2 ONLINE
_SYSSMU29_1581029439$ UNDOTBS2 ONLINE
_SYSSMU28_2713846431$ UNDOTBS2 ONLINE
_SYSSMU27_2818503999$ UNDOTBS2 ONLINE
_SYSSMU26_3492104676$ UNDOTBS2 ONLINE
_SYSSMU25_231788078$ UNDOTBS2 ONLINE
_SYSSMU24_494800911$ UNDOTBS2 ONLINE
_SYSSMU23_3011088732$ UNDOTBS2 ONLINE
_SYSSMU22_358374592$ UNDOTBS2 ONLINE
_SYSSMU21_3370208409$ UNDOTBS2 ONLINE
已选择42行。
这时看到回滚段_SYSSMU3_3898657637$依然处于online状态，需要等到状态变为offline时才可以drop掉表空间。
4、这时也可以查看下到底是哪个操作在占用该回滚段，如果不重要将该进程杀掉即可。
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
SQL> select r.name,
2 s.sid,
3 s.serial#,
4 s.username,
5 s.machine,
6 t.start_time,
7 t.status,
8 substr(s.program, 1, 15) operate
9 from v$session s, v$transaction t, v$rollname r, v$rollstat g
10 where t.addr = s.taddr
11 and t.xidusn = r.usn
12 and r.usn = g.usn and r.name='_SYSSMU3_3898657637$'
13 order by t.used_ublk desc;
NAME SID SERIAL# USERNAME MACHINE START_TIME STATUS OPERATE
------------------------------ ---------- ---------- --------------- ------------------------ --------------------- ---------------- ------------------
_SYSSMU3_3898657637$ 131 24783 SJQY_1130 PDOMAIN\WIN-M8G18I89RAK 12/09/12 13:53:08 ACTIVE sqlplus.exe
可以看到该online的回滚段是sqlplus.exe的一个会话，这个是我另一个连接该数据库的sqlplus窗口，将该窗口关掉再查看回滚段状态。
SQL> alter system kill session '131,24783';
5、各回滚段状态：
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU19_1323384053$ UNDOTBS1 OFFLINE
_SYSSMU18_2053914518$ UNDOTBS1 OFFLINE
_SYSSMU17_1486782901$ UNDOTBS1 OFFLINE
_SYSSMU16_65961640$ UNDOTBS1 OFFLINE
_SYSSMU15_2731980009$ UNDOTBS1 OFFLINE
_SYSSMU14_1272261740$ UNDOTBS1 OFFLINE
_SYSSMU13_3931036527$ UNDOTBS1 OFFLINE
_SYSSMU12_277474270$ UNDOTBS1 OFFLINE
_SYSSMU11_1127233206$ UNDOTBS1 OFFLINE
_SYSSMU10_2010384090$ UNDOTBS1 OFFLINE
_SYSSMU9_3385418537$ UNDOTBS1 OFFLINE
_SYSSMU8_960788264$ UNDOTBS1 OFFLINE
_SYSSMU7_2218399740$ UNDOTBS1 OFFLINE
_SYSSMU6_4013851069$ UNDOTBS1 OFFLINE
_SYSSMU5_14816259$ UNDOTBS1 OFFLINE
_SYSSMU4_3531847310$ UNDOTBS1 OFFLINE
_SYSSMU3_3898657637$ UNDOTBS1 OFFLINE
_SYSSMU2_2881644447$ UNDOTBS1 OFFLINE
_SYSSMU1_1093178884$ UNDOTBS1 OFFLINE
_SYSSMU43_2526432190$ UNDOTBS2 ONLINE
_SYSSMU42_1073097992$ UNDOTBS2 ONLINE
_SYSSMU41_4185790622$ UNDOTBS2 ONLINE
_SYSSMU40_4075868025$ UNDOTBS2 ONLINE
_SYSSMU39_1225046965$ UNDOTBS2 ONLINE
_SYSSMU38_1416928319$ UNDOTBS2 ONLINE
_SYSSMU37_425218863$ UNDOTBS2 ONLINE
_SYSSMU36_2283506252$ UNDOTBS2 ONLINE
_SYSSMU35_3741949710$ UNDOTBS2 ONLINE
_SYSSMU34_3108701464$ UNDOTBS2 ONLINE
_SYSSMU33_1440052939$ UNDOTBS2 ONLINE
_SYSSMU31_867868996$ UNDOTBS2 ONLINE
_SYSSMU30_4206031811$ UNDOTBS2 ONLINE
_SYSSMU29_1581029439$ UNDOTBS2 ONLINE
_SYSSMU28_2713846431$ UNDOTBS2 ONLINE
_SYSSMU27_2818503999$ UNDOTBS2 ONLINE
_SYSSMU26_3492104676$ UNDOTBS2 ONLINE
_SYSSMU25_231788078$ UNDOTBS2 ONLINE
_SYSSMU24_494800911$ UNDOTBS2 ONLINE
_SYSSMU23_3011088732$ UNDOTBS2 ONLINE
_SYSSMU22_358374592$ UNDOTBS2 ONLINE
_SYSSMU21_3370208409$ UNDOTBS2 ONLINE
已选择42行。
6、此时该回滚段已经变成了offline状态，将该回滚段drop掉即可
SQL> drop tablespace undotbs1 including contents and datafiles;
7、至此，重建过程结束。
