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

2024年7月15日 490点热度 0人点赞 0条评论

一、报错信息

某项目最近在 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 是可用、不会报错的。