环境:
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 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表空间空间用尽,不能扩展,尝试手工扩展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
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;
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;
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;
Tablespace dropped.
Elapsed: 00:00:03.13
7.检查空间情况
由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.
[oracle@danaly ~]$ export ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD du
Used_MB
Mirror_used_MB
21625
21625
ASMCMD exit
空间已经释放。
