Shrink Undo表空間,釋放過度占用的空間
2024-07-21 02:33:55
供稿：網(wǎng)友
環(huán)境:
OS:Red Hat EnterPRise linux AS release 4 (Nahant)
DB:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdUCtion
一臺Oracle10gR2數(shù)據(jù)庫報出如下錯誤:
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
登陸檢查，發(fā)現(xiàn)是SYSAUX表空間空間用盡,不能擴(kuò)展,嘗試手工擴(kuò)展SYSAUX表空間:
alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800mTue Nov 29 23:31:38 2005ORA-1237 signalled during: alter database datafile
'+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m...
出現(xiàn)ORA-1237錯誤，提示空間不足。這時候我才熟悉到是磁盤空間可能被用完了.
是誰"偷偷的"用了那么多空間呢(本來有幾十個G的Free磁盤空間的)?
檢查數(shù)據(jù)庫表空間占用空間情況:
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 .004882813UNDOTBS2 .09765625SYSTEM .478515625SYSAUX .634765625WAPCM_TS_VISIT_DETAIL .9765625HY_DS_DEFAULT 1MINT_TS_DEFAULT 1MMS_TS_DATA2 1.375MMS_IDX_SJH 2MMS_TS_DEFAULT 2IVRCN_TS_DATA 2TABLESPACE_NAME GB------------------------------ ----------MMS_TS_DATA1 2CM_TS_DEFAULT 5TEMP 20.5498047UNDOTBS1 27.158203115 rows selected.
不幸的發(fā)現(xiàn)，UNDO表空間已經(jīng)擴(kuò)展至27G，而TEMP表空間也擴(kuò)展至20G，這2個表空間加起來占用了47G的磁盤空間,導(dǎo)致了空間不足。
顯然曾經(jīng)有大事務(wù)占用了大量的UNDO表空間和Temp表空間，Oracle的AUM(Auto Undo Management)從出生以來就經(jīng)常出現(xiàn)只擴(kuò)展,不收縮(shrink)的情況(通常我們可以設(shè)置足夠的UNDO表空間大小,然后取消其自動擴(kuò)展屬性).
現(xiàn)在我們可以采用如下步驟回收UNDO空間:
1.確認(rèn)文件
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狀態(tài)
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 011 rows selected.
3.創(chuàng)建新的UNDO表空間
SQL> create undo tablespace undotbs2;Tablespace created.
4.切換UNDO表空間為新的UNDO表空間
SQL> alter system set undo_tablespace=undotbs2 scope=both;System altered.
此處使用spfile需要注重，以前曾經(jīng)記錄過這樣一個案例: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 012 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 011 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 ~]$ asmcmdASMCMD> du Used_MB Mirror_used_MB 21625 21625ASMCMD> exit
空間已經(jīng)釋放。
