本文來此：http://blog.sina.com.cn/talentchen 雨后彩虹的空間

1.查看表空间使用情况
SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 / 1024 GB
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
UNION ALL
SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 / 1024 GB
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME
ORDER BY GB;

有时UNDO TEMP 表空间会扩大到几十G,而不会缩小.

2.查看表空间文件存放的地方
SELECT FILE_NAME, BYTES / 1024 / 1024
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'UNDOTBS1';

3.检查UNDO Segment状态
SELECT USN,
       XACTS,
       RSSIZE / 1024 / 1024 / 1024,
       HWMSIZE / 1024 / 1024 / 1024,
       SHRINKS
FROM V$ROLLSTAT
ORDER BY RSSIZE;

XACTS = 0 为没UNDO表空间没使用,1为正在使用.

4.创建新的UNDO表空间:
CREATE UNDO TABLESPACE UNDOTBS02
DATAFILE '/oracle/oradata/ora9i/UNDOTBS02.dbf' SIZE 50M

5.切换UNDO表空间到新的表空间
alter system set undo_tablespace=UNDOTBS02 scope=both;

6.等待原UNDO表空间所有UNDO SEGMENT OFFLINE,
SELECT USN,
       XACTS,
       RSSIZE / 1024 / 1024 / 1024,
       HWMSIZE / 1024 / 1024 / 1024,
       SHRINKS
FROM V$ROLLSTAT
ORDER BY RSSIZE;

等XACTS = 所有为0 的时候就可以DROP掉原来的UNDO表空间

7.DROP掉原来的UNDO表空间最好先:
alter tablespace undotbs1 offline;
等过了几分钟后再执行下面这个命令
drop tablespace undotbs1 including contents AND DATAFILES;

UNDO表空間的切換