主頁(yè) > 知識(shí)庫(kù) > 如何Shrink Undo表空間,釋放過(guò)度占用的空間

如何Shrink Undo表空間,釋放過(guò)度占用的空間

熱門(mén)標(biāo)簽:修改高德地圖標(biāo)注 廣州市400電話辦理 南通防封外呼系統(tǒng)運(yùn)營(yíng)商 淮安自動(dòng)外呼系統(tǒng)開(kāi)發(fā) 語(yǔ)音電話機(jī)器人營(yíng)銷(xiāo)方案 語(yǔ)音電話機(jī)器人缺點(diǎn) 宜賓外呼系統(tǒng)廠家 百變地圖標(biāo)注 地圖標(biāo)注原件

環(huán)境:
OS:Red Hat Enterprise Linux AS release 4 (Nahant)
DB:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

一臺(tái)Oracle10gR2數(shù)據(jù)庫(kù)報(bào)出如下錯(cuò)誤:
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 800m
Tue Nov 29 23:31:38 2005
ORA-1237 signalled during: alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m...

出現(xiàn)ORA-1237錯(cuò)誤,提示空間不足。這時(shí)候我才認(rèn)識(shí)到是磁盤(pán)空間可能被用完了.

是誰(shuí)"偷偷的"用了那么多空間呢(本來(lái)有幾十個(gè)G的Free磁盤(pán)空間的)?
檢查數(shù)據(jù)庫(kù)表空間占用空間情況:
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.
不幸的發(fā)現(xiàn),UNDO表空間已經(jīng)擴(kuò)展至27G,而TEMP表空間也擴(kuò)展至20G,這2個(gè)表空間加起來(lái)占用了47G的磁盤(pán)空間,導(dǎo)致了空間不足。
顯然曾經(jīng)有大事務(wù)占用了大量的UNDO表空間和Temp表空間,Oracle的AUM(Auto Undo Management)從出生以來(lái)就經(jīng)常出現(xiàn)只擴(kuò)展,不收縮(shrink)的情況(通常我們可以設(shè)置足夠的UNDO表空間大小,然后取消其自動(dòng)擴(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          0

11 rows selected.
3.創(chuàng)建新的UNDO表空間
SQL> create undo tablespace undotbs2 ;
(經(jīng)測(cè)試,在9i環(huán)境下后面還要加上datafile '/opt/..../undotbs2.dbf' size 1024M)
Tablespace created.
4.切換UNDO表空間為新的UNDO表空間
SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

創(chuàng)建了新的UNDO表空間以后,如果不知道系統(tǒng)使用的是pfile還是spfile文件,應(yīng)使用參數(shù)both,會(huì)同時(shí)修改spfile文件,避免出現(xiàn)沖突。


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;
(本人經(jīng)測(cè)試覺(jué)得其實(shí)是否可以用drop tablespace undotbs1 including contents and datafiles;直接連硬盤(pán)里面的dbf文件件一起刪除)
Tablespace dropped.

Elapsed: 00:00:03.13
7.檢查空間情況
由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd來(lái)察看空間占用情況.
[oracle@danaly ~]$ export ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD> du
Used_MB      Mirror_used_MB
  21625               21625
ASMCMD> exit

空間已經(jīng)釋放。

您可能感興趣的文章:
  • Mysql在線回收undo表空間實(shí)戰(zhàn)記錄

標(biāo)簽:通化 襄陽(yáng) 聊城 池州 南平 南平 股票投資 嘉峪關(guān)

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《如何Shrink Undo表空間,釋放過(guò)度占用的空間》,本文關(guān)鍵詞  如何,Shrink,Undo,表,空間,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《如何Shrink Undo表空間,釋放過(guò)度占用的空間》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于如何Shrink Undo表空間,釋放過(guò)度占用的空間的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章