Re: Tablespace extending issue

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_at_freelists.org> 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?
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l