一次意外的 ORA-01652

C系統(tǒng)是一個(gè)業(yè)務(wù)繁忙的批量作業(yè)系統(tǒng)，每天加工超過(guò)300GB的數(shù)據(jù)，是一個(gè)數(shù)據(jù)量大、加工時(shí)效要求高的系統(tǒng)。為提高效率，應(yīng)用大量使用"CTAS+分區(qū)交換"替代大事務(wù)的"DELETE+INSERT"操作進(jìn)行數(shù)據(jù)歸檔。

[[207791]]

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

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

為什么說(shuō)意外

1、系統(tǒng)每天監(jiān)控表空間使用量，始終保持使用率在80%以內(nèi)。表空間TBS剩余2.7TB，總空間13TB。

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

以上似乎說(shuō)明：存儲(chǔ)空間容量看起來(lái)沒(méi)問(wèn)題。

繼續(xù)驗(yàn)證分析

經(jīng)過(guò)反復(fù)檢查驗(yàn)證，發(fā)現(xiàn)：當(dāng)給select 語(yǔ)句加上只取40萬(wàn)條記錄的限制，可成功創(chuàng)建N張T2(58MB)表;但是當(dāng)把記錄數(shù)限制放大到50萬(wàn)(72MB)的時(shí)候，就出現(xiàn)ORA-01652錯(cuò)誤。

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

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

以下我們根據(jù)ORACLE數(shù)據(jù)庫(kù)的表空間管理方式，并進(jìn)行驗(yàn)證來(lái)解析這個(gè)問(wèn)題。

表空間的管理方式

圖1 oracle 數(shù)據(jù)的存儲(chǔ)結(jié)構(gòu)

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

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

當(dāng)新建一張表(或者一個(gè)分區(qū))時(shí)，系統(tǒng)就為該表空間分配一個(gè)初始EXTENT，并按需不斷新增EXTENT。新EXTENT的大小根據(jù)表空間的管理方式確定，如下圖：

系統(tǒng)剩余空間分析

本例中，由于TBS表空間管理方式為 SYSTEM，通過(guò)dba_free_space 對(duì)TBS剩余空間分布進(jìn)行分析：

發(fā)現(xiàn)剩余表空間的EXTENT分布如下：

問(wèn)題定位

根據(jù)以上分析，由于表空間碎片化嚴(yán)重，剩余的2.7TB空間中，已經(jīng)沒(méi)有大于8M的EXTENT，所以出現(xiàn) ORA-01652錯(cuò)誤。這就解釋了上面我們提出的兩個(gè)問(wèn)題：

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

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

解決方案

臨時(shí)應(yīng)急方案：

拓展表空間或者清理部分?jǐn)?shù)據(jù)，增加大于8M/64M的空閑EXTENT數(shù)量。

解決方案：

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

實(shí)施步驟：

1)申請(qǐng)13T臨時(shí)存儲(chǔ)空間進(jìn)行過(guò)渡。

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

3)逐漸將TBS中的數(shù)據(jù)表挪到TBS3(alter table xxxx move tablespace TBS3)，需要注意的是：對(duì)于包含long或LOB類型的表，無(wú)法通過(guò)alter table move的方式進(jìn)行移動(dòng)，得通過(guò)數(shù)據(jù)泵導(dǎo)入導(dǎo)出。

4)調(diào)整完成后，刪除TBS表空間，釋放存儲(chǔ)空間并歸還。

拓展閱讀--行內(nèi)表空間碎片化的監(jiān)控

目前行內(nèi)對(duì)于ORACLE數(shù)據(jù)庫(kù)表空間碎片化觸發(fā)告警需要同時(shí)滿足以下條件：

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

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

在本例中，由于系統(tǒng)每日空間需求較大，可用EXTENT(>32MB)為20GB時(shí)，無(wú)法滿足一個(gè)跑批日的空間需求，因此對(duì)于這類應(yīng)用，需要適當(dāng)調(diào)整監(jiān)控閾值。

拓展思考

在使用EXTENT管理使用SYSTEM方式時(shí)：

1、當(dāng)sql執(zhí)行時(shí)加入parallel的hint后，是否會(huì)加快碎片化速度?

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