Path: news.easynews.com!easynews!newshosting.com!news-xfer2.atl.newshosting.com!news-hub.siol.net!bnewsfeed00.bru.ops.eu.uu.net!bnewsifeed00.bru.ops.eu.uu.net!lnewspost00.lnd.ops.eu.uu.net!emea.uu.net!not-for-mail
From: "Niall Litchfield" <n-litchfield@audit-commission.gov.uk>
Newsgroups: comp.databases.oracle.server
References: <3D518D84.4A77@yahoo.com>
Subject: Re: Generic "Can I extend?" check
Date: Thu, 8 Aug 2002 10:22:00 +0100
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Lines: 62
Message-ID: <3d523838$0$238$ed9e5944@reading.news.pipex.net>
NNTP-Posting-Host: host9.audit-commission.gov.uk
X-Trace: 1028798520 reading.news.pipex.net 238 193.128.236.219
X-Complaints-To: abuse@uk.uu.net
Xref: easynews comp.databases.oracle.server:157246
X-Received-Date: Thu, 08 Aug 2002 02:19:06 MST (news.easynews.com)

"Connor McDonald" <connor_mcdonald@yahoo.com> wrote in message
news:3D518D84.4A77@yahoo.com...
> I've been asked to write a generic routine to determine if a segment can
> extend.  Easy enough except for auto-alloc lmt's where 'next_extent' is
> null (ie indeterminate) in xxx_SEGMENTS.
>
> So here is my current compromise (in pseudo-code)
>
> where nvl(next_extent,
>   case
>     when initial_extent < 1m then
>       case when extents < 16 then next = 64k,
>            when extents < 80 then next = 1m,
>            when extents < 200 then next = 8m,
>            else next = 64m
>     when initial_extent >= 1m then
>       case when extents < 64 then next = 1m,
>            when extents < 184 then next = 8m,
>            else next = 64m )
>   > largest_free_space_chunk_in_tablespace
>
> The rough translation being:
> - auto-alloc extents are 16x64k, then 64x1m, then 120x8m, then 64m, when
> the initial_extent is less than 1m
> - auto-alloc extents are 64x1m, then 120x8m, then 64m, when the
> initial_extent is equal/more than 1m
>
> In the spirit of "open source", I'm throwing this out the community
> asking for counter-examples which break the above formula.

Jonathan pointed out to me back in March that the results you get above are
what happens when you test creating one table in an otherwise empty
tablespace, (and that even these values differ between oracle versions yours
looks like it came from 9i). This discussion is here

http://shrinkalink.com/307

I haven't actually done the test (I'm in the middle of upgrading our ERP
system so don't get much time for playing) but I suspect that you would be
able to break your code by filling a small tablespace with a number of
differently sized objects leaving random chunks of differently sized free
space scattered about the place. In other words if the tablespace has say a
64k and a 1m chunk of free space left but you are now in the 8mb extent
range I rather suspect that an autoallocate tablespace will allocate first
the 1m and then the 64 k as required.


--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************