一次意外的 ORA-01652

C系統是一個業務繁忙的批量作業系統，每天加工超過300GB的數據，是一個數據量大、加工時效要求高的系統。為提高效率，應用大量使用"CTAS+分區交換"替代大事務的"DELETE+INSERT"操作進行數據歸檔。

[[207791]]

某日日終跑批時，在表空間TBS(剩余空間約2.7TB)上新建表T1(約1GB)時，出現“表空間不足”的錯誤。報錯信息如下：

ORA-01652 文檔解釋是： Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated。

為什么說意外

1、系統每天監控表空間使用量，始終保持使用率在80%以內。表空間TBS剩余2.7TB，總空間13TB。

2、通過CTAS(Create Table As Select)新建的T1表，記錄數為七百多萬，數據大小約為1G。

以上似乎說明：存儲空間容量看起來沒問題。

繼續驗證分析

經過反復檢查驗證，發現：當給select 語句加上只取40萬條記錄的限制，可成功創建N張T2(58MB)表;但是當把記錄數限制放大到50萬(72MB)的時候，就出現ORA-01652錯誤。

為什么2.7G的可用空間卻建不了1GB的表?

為什么建n張58MB的表可以，一張72MB的表就不行呢?

以下我們根據ORACLE數據庫的表空間管理方式，并進行驗證來解析這個問題。

表空間的管理方式

圖1 oracle 數據的存儲結構

如圖1所示：Oracle存儲層級：TABLESPACE>SEGMENT(TABLE/INDEX/PARTITION)->EXTENT->BLOCKS。

在ORACLE數據庫中，表空間中的空閑空間是以extent的形式組織的，分配空間時也是按照extent分配的。extent存在兩種管理方式：SYSTEM、UNIFORM，需要在創建表空間時指定。

當新建一張表(或者一個分區)時，系統就為該表空間分配一個初始EXTENT，并按需不斷新增EXTENT。新EXTENT的大小根據表空間的管理方式確定，如下圖：

系統剩余空間分析

本例中，由于TBS表空間管理方式為 SYSTEM，通過dba_free_space 對TBS剩余空間分布進行分析：

發現剩余表空間的EXTENT分布如下：

問題定位

根據以上分析，由于表空間碎片化嚴重，剩余的2.7TB空間中，已經沒有大于8M的EXTENT，所以出現 ORA-01652錯誤。這就解釋了上面我們提出的兩個問題：

1)為什么2.7G的可用空間卻建不了1GB的表?(缺少>=8MB的EXTENT)

2)為什么建n張58MB的表可以(<=1MB的EXTENT充足)，一張72MB的表就不行呢?(缺少>=8MB的EXTENT)

解決方案

臨時應急方案：

拓展表空間或者清理部分數據，增加大于8M/64M的空閑EXTENT數量。

解決方案：

將表空間管理方式由SYSTEM改為UNIFORM。

實施步驟：

1)申請13T臨時存儲空間進行過渡。

2)新建表空間TBS3，新表空間采用uniform size空間管理方式，size設置為2M

3)逐漸將TBS中的數據表挪到TBS3(alter table xxxx move tablespace TBS3)，需要注意的是：對于包含long或LOB類型的表，無法通過alter table move的方式進行移動，得通過數據泵導入導出。

4)調整完成后，刪除TBS表空間，釋放存儲空間并歸還。

拓展閱讀--行內表空間碎片化的監控

目前行內對于ORACLE數據庫表空間碎片化觸發告警需要同時滿足以下條件：

1、可用EXTENT(<32MB) / 全部可用表空間 > 40%;

2、可用EXTENT(>32MB)總計小于20GB;

在本例中，由于系統每日空間需求較大，可用EXTENT(>32MB)為20GB時，無法滿足一個跑批日的空間需求，因此對于這類應用，需要適當調整監控閾值。

拓展思考

在使用EXTENT管理使用SYSTEM方式時：

1、當sql執行時加入parallel的hint后，是否會加快碎片化速度?

2、當使用alter table move ...... compress ...... 后，是否可以減少表空間的碎片化?