Deepak,
As to your last question, I guess your block size is 8K?
8*128=1024K or 1M.
As you say your tablespaces are 3-5TB and you are using 30GB datafiles, my
guess would be you have a lot of datafiles that are nearly full and no
contiguous space available. You have over 100 datafiles presumably? Seems
strange it can't find 1MB though. I assume PCTINCREASE is set to zero?
If a rebuild is in prospect, I'd suggest looking at bigfile tablespaces.
Good luck,
JT
On Thu, 1 Oct 2015 at 21:26 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?
