You're using auto-allocated extent sizes. Fragmentation is definitely
possible. In a very large tablespace, you could easily have 50GB of free
space with no single extent greater than 1 MB. (or 8MB, as the case may be)
If I recall correctly from long-distant memories, I have run into odd
fragmentation problems in the past with tablespaces housing LOB segments,
too.
Keeping at least one file on hand with the ability to autoextend is a good
way to avoid extent allocation errors resulting from fragmentation. Using
fixed extent sizes, though, is a good way to *prevent* fragmentation in the
first place.
Note, by the way, that the values for INITIAL and NEXT extents are pretty
nearly ignored when you are using locally managed tablespaces.
On Thu, Oct 1, 2015 at 2:25 PM, Deepak Sharma <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
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?
