环境: [b]OS:Red Hat Enterprise Linux AS release 4 (Nahant) DB:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production[/b] 一台Oracle10gR2数据库报出如下错误: ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX 登陆检查，发现是SYSAUX表空间空间用尽,不能扩展,尝试手工扩展[url=http://www.eygle.com/10g/10g_sysaux_tbs.htm]SYSAUX[/url]表空间:  alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m Tue Nov 29 23:31:38 2005 ORA-1237 signalled during: alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m... 出现ORA-1237错误，提示空间不足。这时候我才认识到是磁盘空间可能被用完了. 是谁"偷偷的"用了那么多空间呢(本来有几十个G的Free磁盘空间的)? 检查数据库表空间占用空间情况: SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB   2  from dba_data_files group by tablespace_name   3  union all   4  select tablespace_name,sum(bytes)/1024/1024/1024 GB   5  from dba_temp_files group by tablespace_name order by GB; TABLESPACE_NAME                        GB ------------------------------ ---------- USERS                          .004882813 UNDOTBS2                        .09765625 SYSTEM                         .478515625 SYSAUX                         .634765625 WAPCM_TS_VISIT_DETAIL            .9765625 HY_DS_DEFAULT                           1 MINT_TS_DEFAULT                         1 MMS_TS_DATA2                        1.375 MMS_IDX_SJH                             2 MMS_TS_DEFAULT                          2 IVRCN_TS_DATA                           2 TABLESPACE_NAME                        GB ------------------------------ ---------- MMS_TS_DATA1                            2 CM_TS_DEFAULT                           5 TEMP                           20.5498047 UNDOTBS1                       27.1582031 15 rows selected. 不幸的发现，UNDO表空间已经扩展至27G，而TEMP表空间也扩展至20G，这2个表空间加起来占用了47G的磁盘空间,导致了空间不足。 显然曾经有大事务占用了大量的UNDO表空间和Temp表空间，Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性). 现在我们可以采用如下步骤回收UNDO空间: 1.确认文件 SQL> select file_name,bytes/1024/1024 from dba_data_files   2  where tablespace_name like 'UNDOTBS1'; FILE_NAME -------------------------------------------------------------------------------- BYTES/1024/1024 --------------- +ORADG/danaly/datafile/undotbs1.265.600173875           27810 2.检查UNDO Segment状态 SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks   2  from v$rollstat order by rssize;        USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS ---------- ---------- --------------------- ---------------------- ----------          0          0            .000358582             .000358582          0          2          0            .071517944             .071517944          0          3          0             .13722229              .13722229          0          9          0            .236984253             .236984253          0         10          0            .625144958             .625144958          0          5          1            1.22946167             1.22946167          0          8          0            1.27175903             1.27175903          0          4          1            1.27895355             1.27895355          0          7          0            1.56770325             1.56770325          0          1          0            2.02474976             2.02474976          0          6          0             2.9671936              2.9671936          0 11 rows selected. 3.创建新的UNDO表空间 SQL> create undo tablespace undotbs2 ; （经测试，在9i环境下后面还要加上datafile '/opt/..../undotbs2.dbf' size 1024M） Tablespace created. 4.切换UNDO表空间为新的UNDO表空间 SQL> alter system set undo_tablespace=undotbs2 scope=both; System altered. 创建了新的UNDO表空间以后，如果不知道系统使用的是pfile还是spfile文件，应使用参数both,会同时修改spfile文件，避免出现冲突。 5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks   2 from v$rollstat order by rssize;        USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS ---------- ---------- --------------- --------------------- ---------------------- ----------         14          0 ONLINE                     .000114441             .000114441          0         19          0 ONLINE                     .000114441             .000114441          0         11          0 ONLINE                     .000114441             .000114441          0         12          0 ONLINE                     .000114441             .000114441          0         13          0 ONLINE                     .000114441             .000114441          0         20          0 ONLINE                     .000114441             .000114441          0         15          1 ONLINE                     .000114441             .000114441          0         16          0 ONLINE                     .000114441             .000114441          0         17          0 ONLINE                     .000114441             .000114441          0         18          0 ONLINE                     .000114441             .000114441          0          0          0 ONLINE                     .000358582             .000358582          0        USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS ---------- ---------- --------------- --------------------- ---------------------- ----------          6          0 PENDING OFFLINE             2.9671936              2.9671936          0 12 rows selected. 再看: 11:32:11 SQL> /        USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS ---------- ---------- --------------- --------------------- ---------------------- ----------         15          1 ONLINE                     .000114441             .000114441          0         11          0 ONLINE                     .000114441             .000114441          0         12          0 ONLINE                     .000114441             .000114441          0         13          0 ONLINE                     .000114441             .000114441          0         14          0 ONLINE                     .000114441             .000114441          0         20          0 ONLINE                     .000114441             .000114441          0         16          0 ONLINE                     .000114441             .000114441          0         17          0 ONLINE                     .000114441             .000114441          0         18          0 ONLINE                     .000114441             .000114441          0         19          0 ONLINE                     .000114441             .000114441          0          0          0 ONLINE                     .000358582             .000358582          0 11 rows selected. Elapsed: 00:00:00.00 6.删除原UNDO表空间 11:34:00 SQL> drop tablespace undotbs1 including contents; （本人经测试觉得其实是否可以用drop tablespace undotbs1 including contents and datafiles;直接连硬盘里面的dbf文件件一起删除） Tablespace dropped. Elapsed: 00:00:03.13 7.检查空间情况 由于我使用的ASM管理,可以使用10gR2提供的信工具[url=http://www.eygle.com/archives/2005/09/oracle_10g_rele.html]asmcmd[/url]来察看空间占用情况. [oracle@danaly ~]$ export ORACLE_SID=+ASM [oracle@danaly ~]$ asmcmd ASMCMD> du Used_MB      Mirror_used_MB   21625               21625 ASMCMD> exit 空间已经释放。