We're seeing issue with a few particularly very large index tablespaces (3-5TB
range) with allocating extents.
When we look at the free space there's plenty free (e.g. 40-50gb) but sometimes
still get cannot allocate error.
The tablespace has been created using something similar as below:
create tablespace <ts_name>
datafile
'...' size 32767M,
'...' size 32767M, .......
extent management local autoallocate
segment space management auto;
Below are few examples of the error and Initial/Next extent sizes from
dba_segments :
1) ORA-3234: unable to extend index <INDEX1> subpartition <SPARTxyz> by 128 in
tablespace <TS_1>
SEGMENT_NAME PARTITION_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT
-------------------- --------------- ---------- -------------- -----------
<INDEX1> <SPARTxyz> 44 65536 1048576
2) ORA-3234: unable to extend index <INDEX2> subpartition <SPARTpqr> by 1024 in
tablespace <TS_1>
SEGMENT_NAME PARTITION_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT
-------------------- --------------- ---------- -------------- -----------
<INDEX2> <SPARTpqr> 139 65536 1048576
In above case, even though the next extent size is 1M, why does the error say
'unable to extend ... by 128' in first case?