Re: Tablespace extending issue
Date: Fri, 02 Oct 2015 16:27:03 +0000
Message-ID: <CAOHpfbFxzHvHEJ=X0iToc2wC5LDR22LWjmJdtGnD3cENp_ekLw_at_mail.gmail.com>
You can work this out by having an ordered look at DBA_EXTENTS for a segment name, or in enterprise manager you can get a graphical extent map for all segments in a tablespace. The latter gets a bit flakey for large numbers of segments, I guess it depends on your desktop spec and possibly how much resource is available to your OMS.
Regards
JT
On Fri, 2 Oct 2015 16:53 Deepak Sharma <dmarc-noreply_at_freelists.org> wrote:
> 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-lReceived on Fri Oct 02 2015 - 18:27:03 CEST