Re: Generic "Can I extend?" check

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:3D518D84.4A77_at_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

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