ORA-01658创建表或索引报错分析

一、报错信息

某项目最近在 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。这个只能在业务闲时操作，确保操作的表不要引起其他问题，比如先备份表。