環境:
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 ;
（經測試，在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提供的信工具asmcmd來察看空間占用情況.
[oracle@danaly ~]$ export ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD> du
Used_MB Mirror_used_MB
21625 21625
ASMCMD> exit
空間已經釋放。
