導讀網站優化seo培訓:1建站知識如何Shrink Undo表空間,釋放過度占用的空間seo網站排名優化軟件營銷型網站建設。

環境: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 800mTue Nov 29 23:31:38 2005ORA-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                          .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.不幸的發現，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          278102.檢查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        網seo優化趨勢;  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.創建新的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文件，避免出現沖突。

關鍵詞標簽: 空間 標簽