I may have to rephrase the question a little after this background.
For 'uniform extent size', the extent size is known (it's whatever value was
used).
However, for 'AUTOALLOCATE', is there a way to find out what the NEXT_EXTENT
size is or would be, next time an extent is needed?
I've heard that the NEXT_EXTENT size is calculated internally by Oracle and is
not visible anywhere else.
Anyone, thoughts ?
On Thursday, October 1, 2015 11:43 PM, Mark Brinsmead
<mark.brinsmead@xxxxxxxxx> wrote:
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?
