Re: Tablespace extending issue

From: Deepak Sharma <"Deepak>
Date: Fri, 2 Oct 2015 15:52:17 +0000 (UTC)
Message-ID: <708773782.325996.1443801137911.JavaMail.yahoo_at_mail.yahoo.com>

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_at_gmail.com> 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_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

Received on Fri Oct 02 2015 - 17:52:17 CEST