ORA-01658建立表或索引報錯分析

likingzi發表於2024-07-29
索引

一、報錯資訊

某專案最近在 SQL Loader 導資料時偶爾會報錯，類似如下：

SQL loader ORA-01658 unable to creale INITIAL extent for segment in tablespace ADS5GP2P_1

這個報錯的意思是，沒有足夠的連續空間為表或索引建立 INITIAL extent:

[oracle@node1:1 ~]$ oerr ora 1658
01658, 00000, "unable to create INITIAL extent for segment in tablespace %s"
// *Cause:  Failed to find sufficient contiguous space to allocate INITIAL
//          extent for segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
//          tablespace or retry with a smaller value for INITIAL

二、報錯分析

資料庫版本是 Oracle 11G，實際檢視該表空間仍有2T多的剩餘空間，根據以往經驗，最大的可能是這2T多的剩餘空間大多是碎片，在業務忙時無法提供足夠可用的連續空間，以下做驗證。
DBA_FREE_SPACE describes the free extents in all tablespaces in the database.
資料字典 DBA_FREE_SPACE 描述了所有的可用 extent 情況：

select trunc(bytes/1048576) mb, count(*)
  from dba_free_space
 where tablespace_name = 'ADS5GP2P_1'
 group by trunc(bytes/1048576)
 order by 1;
 0	2374933
 1	61526
 2	21622
 3	13995
 4	34797
 5	5133
 6	6851
 7	3687
 8	16463
 9	2883
10	1785
11	1348
12	5552
13	742
14	666
15	615
16	6029
17	326
18	300
19	398
20	2553
21	94
22	62
23	49
24	82
25	41
26	21
27	9
28	26
29	15
30	12
......

以上可見空閒的空間裡有大量的碎片，可能的原因是頻繁、長時間的修改、匯入資料逐步導致的。這些碎片的大小達到了 2T，如下：

select tablespace_name, sum(bytes/1048576) mb
  from dba_free_space
 where trunc(bytes/1048576) < 1
 group by tablespace_name;
---
ADS5GP2P_1: 2162858.375

結論是：
雖然空閒空間很多，但是這些空閒空間大都是小於 1M 的小碎片，這些小碎片加起來達到了2T，導致可能有時沒法及時分配 INITIAL extent 給應用使用，從而報錯。
以下進一步確認這些碎片的具體大小：

select trunc(bytes/65536) k64, count(*)
  from dba_free_space
 where tablespace_name = 'ADS5GP2P_1'
 group by trunc(bytes/65536)
 order by 1;
 1	31756
 2	8567
 3	6803
 4	10116
 5	3230
 6	1748
 7	2027
 8	2492
 9	11143
10	4988
11	1183
12	1875
13	21457
14	43512
15	2228918
16	1251
17	151
18	152
19	230
20	177

以上可見 15*65536=960k 的 extent 達到了 2228918，合計 2T 多。
可見這些小碎片大多是 960k 的小碎片，理論上對於大多數 64k 的 INITIAL extent 是可用、不會報錯的。

三、解決方案

因此最終的解決方案是，修改報錯表和索引的 INITIAL extent，讓他們小於多數碎片的大小，即小於 960k。這個只能在業務閒時操作，確保操作的表不要引起其他問題，比如先備份表。

相關文章