I. Error message
A project recently reported occasional errors when importing data in SQL Loader, similar to the following:
SQL loader ORA-01658 unable to creale INITIAL extent for segment in tablespace ADS5GP2P_1
This error means that there is not enough contiguous space to create an INITIAL extent for the table or index.
[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
II. Error analysis
The database version is Oracle 11G, the actual view of the tablespace still has more than 2T of remaining space, according to past experience, the biggest possibility is that most of the remaining space of more than 2T is fragmented, and can not provide enough usable contiguous space when the business is busy, the following verification.
DBA_FREE_SPACE describes the free extents in all tablespaces in the database.
The data dictionary DBA_FREE_SPACE describes all available extent cases:
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
......
As you can see above, there are a lot of fragments in the free space, which may be caused by frequent and long time modification and import of data. The size of these fragments reaches 2T, as follows:
select tablespace_name, sum(bytes/1048576) mb
from dba_free_space
where trunc(bytes/1048576) < 1
group by tablespace_name;
---
ADS5GP2P_1: 2162858.375
Conclusion:
Although there is a lot of free space, most of these free spaces are small fragments of less than 1M, and these small fragments add up to 2T, which may not be able to allocate the INITIAL extent in time for the application to use, and thus report an error.
The exact size of these fragments is further identified below:
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
From the above, we can see that the extent of 15*65536=960k reaches 2228918, totaling more than 2T.
As you can see, most of these small fragments are 960k fragments, which are theoretically usable and error-free for most 64k INITIAL extent.
III. Solutions
So the final solution is to change the INITIAL extent of the table and indexes that are reporting errors so that they are smaller than the size of most shards, i.e., less than 960 k. This should only be done when the business is idle, making sure that the table being operated on doesn't cause any other problems, e.g., by backing up the table first.