环境:
OS:Red Hat Enterprise Linux AS release 4 (Nahant)
DB:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdUCtion
一台Oracle10gR2数据库报出如下错误:
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 intablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 intablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 intablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 intablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 intablespace SYSAUX
登陆检查，发现是SYSAUX表空间空间用尽,不能扩展,尝试手工扩展SYSAUX表空间:
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
2from dba_data_files group by tablespace_name
3union all
4select tablespace_name,sum(bytes)/1024/1024/1024 GB
5from dba_temp_files group by tablespace_name order by GB;
TABLESPACE_NAMEGB
------------------------------ ----------
USERS.004882813
UNDOTBS2.09765625
SYSTEM .478515625
SYSAUX .634765625
WAPCM_TS_VISIT_DETAIL.9765625
HY_DS_DEFAULT 1
MINT_TS_DEFAULT 1
MMS_TS_DATA21.375
MMS_IDX_SJH 2
MMS_TS_DEFAULT2
IVRCN_TS_DATA 2
TABLESPACE_NAMEGB
------------------------------ ----------
MMS_TS_DATA12
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
2where 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
2from v$rollstat order by rssize;
USNXACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024SHRINKS
------- ---------- --------------------- ---------------------- ----------
00.000358582 .0003585820
20.071517944 .0715179440
30 .13722229.137222290
90.236984253 .2369842530
10 0.625144958 .6251449580
511.22946167 1.229461670
801.27175903 1.271759030
411.27895355 1.278953550
701.56770325 1.567703250
102.02474976 2.024749760
60 2.96719362.96719360
11 rows selected.
3.创建新的UNDO表空间
SQL create undo tablespace undotbs2;
Tablespace created.
4.切换UNDO表空间为新的UNDO表空间
SQL alter system set undo_tablespace=undotbs2 scope=both;
System altered.
此处使用spfile需要注重，以前曾经记录过这样一个案例:Oracle诊断案例-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;
USNXACTS STATUSRSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024SHRINKS
----- ---------- --------------- --------------------- ---------------------- ----------
140 ONLINE .000114441 .0001144410
190 ONLINE .000114441 .0001144410
110 ONLINE .000114441 .0001144410
120 ONLINE .000114441 .0001144410
130 ONLINE .000114441 .0001144410
200 ONLINE .000114441 .0001144410
151 ONLINE .000114441 .0001144410
160 ONLINE .000114441 .0001144410
170 ONLINE .000114441 .0001144410
180 ONLINE .000114441 .0001144410
00 ONLINE .000358582 .0003585820
USNXACTS STATUSRSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024SHRINKS
----------- --------------- --------------------- ---------------------- ----------
60 PENDING OFFLINE 2.96719362.96719360
12 rows selected.
再看:
11:32:11 SQL /
USNXACTS STATUS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---- ---------------------- ------------------------------------------- --------
151ONLINE.000114441 .0001144410
110ONLINE.000114441 .0001144410
120ONLINE.000114441 .0001144410
130ONLINE.000114441 .0001144410
140ONLINE.000114441 .0001144410
200ONLINE.000114441 .0001144410
160ONLINE.000114441 .0001144410
170ONLINE.000114441 .0001144410
180ONLINE.000114441 .0001144410
190ONLINE.000114441 .0001144410
00ONLINE.000358582 .0003585820
11 rows selected.
Elapsed: 00:00:00.00
6.删除原UNDO表空间
11:34:00 SQL drop tablespace undotbs1 including contents;
Tablespace dropped.
Elapsed: 00:00:03.13
7.检查空间情况
由于我使用的ASM治理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.
[oracle@danaly ~]$ eXPort ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD du
Used_MBMirror_used_MB
21625 21625
ASMCMD exit
空间已经释放。
