Re: Space Usage Question

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Wed, 04 Feb 2004 12:51:06 GMT
Message-ID: <_U5Ub.42357$Wa.22097@news-server.bigpond.net.au>

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:401e606c$0$9392$ed9e5944_at_reading.news.pipex.net...
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:IUrTb.39847$Wa.31380_at_news-server.bigpond.net.au...
> > There has always been an issue with suggestions that autoallocate could
> lead
> > to fragmentation due to the fact that it allocates extents of differing
> > sizes. Well, I had a little play with this today (Windows, Oracle 9.2)
> <snip>
> > Would the next allocation succeed as I now only had 64K and 1M areas of
> free
> > space ?
> >
> > The answer is a resounding "YES" !!
> >
> > It grabbed a 1M extent. As did the next allocation, and the next and the
> > next ...
> >
> > So faced with kinda wanting 8M of free space but not being able to get
it
> > "contiguously" within the tablespace, the autoallocate algorithm was
quite
> > happy to accept the next biggest piece of free space available. In fact
> all
> > the remaining free space was able to be utilized by the table A.
> >
> > Conclusion ?
> >
> > That the so-called "disadvantage" of autoallocate causing fragmentation
> > issues is somewhat exaggerated and (as far as my little experiment
showed)
> > is a non-issue when considering using autoallocate.
> >
> > If anyone wants me to send/post the actual test, let me know (bedtime
now
> > !!)

>

> Morning Richard :(

>
> I have different results. I attempted to fill up an autoallocate
tablespace
> with 32 tables - 16 extents each, drop half the tables and then allocate a
> 17th extent to table1.

>

Hi Niall,

Thanks for the example.

I followed it and too got Oracle to fail to reuse the 64K chunks.

Little more digging to go ....

Cheers ;)

Richard Received on Wed Feb 04 2004 - 06:51:06 CST