Re: next extent query

"Arcangelo" <xxx_at_yyy.com> wrote in message news:3eee3431$0$16256$afc38c87_at_news.optusnet.com.au...
>
> "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
> news:3eee2ed6$0$11380$cc9e4d1f_at_news.dial.pipex.com...
> > "Arcangelo" <xxx_at_yyy.com> wrote in message
> > news:3eee2847$0$24424$afc38c87_at_news.optusnet.com.au...
> > > (B) The next extent will be the same size as your last one (so check
out
> > > BYTES in dba_extents), unless you happen to be on one of the
> autoallocate
> > > boundaries. The barebones autoallocate algorithm goes: first 16
extents
> > will
> > > be 64K, then to extent #79 will be 1M, then to extent #199 will be 8M,
> > > thereafter 64M. So unless you have 15, 79 or 199 extents, your next
> extent
> > > will be the same as the last one. However, the algorithm gets more
> > > complicated if you specify an INITIAL at the time of table creation,
and
> > if
> > > that's the case, see answer (A) above.
> >
> > I'm still not entirely convinced that the above is true - since with
> objects
> > growing at different rates it could lead to 'unable to allocate extent'
> when
> > Oracle could by choosing a different size from the available list. I
feel
> a
> > test coming on. (but not after 4 beers that would be silly - thats the
> time
> > for theorizing)
>
> Maybe it's the beers, but which bit don't you think is true?
>
> I did mention that the numbers quoted were for the *barebones*
autoallocate
> algorithm. In other words, take an empty autoallocate LMT, create a table
> with no storage clause.

What I don't believe to be true - but it might be. Is the implication that any given table in an ALMT (autoallocate-lmt) will grow according to the pattern you state. In normal usage it wouldn't surprise me if a table grew at

64k for the first 16 extents
case(max(free_space)>1m 1m) else 64k for the next 79 extents, case(max(free_space)>8m 8m,max(free_space)>1m 1m) else 64k etc

In other words pick the largest available free extent under my limit, which should simplify to the rule you give in the event of a single growing table only. This would seem more sensible since it avoids out of space errors when 64k or more is in fact free.

> Anything else, and it gets extremely complicated indeed, granted.

Which may mean we don't disagree - just I misinterpreted your rule.

My Hypothesis above seems to me eminently testable (at least for small tablespaces).

-- 
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
******************************************