Re: max extent size in locally managed tablespace with AUTOALLOCATE

On Jun 13, 5:31 am, Vsevolod Afanassiev <vafanass..._at_yahoo.com> wrote:
> What is the maximum extent size possible in locally managed
> tablespaces (LMT) with autoallocate?
> Let me clarify the question: I am interested in conventional datafiles
> (not bigfiles).
> I understand that "strictly speaking" this question does not have an
> answer as
> extent allocation in LMT isn't documented by Oracle and can change
> between versions/platforms/patches, may depend on block size,
> datafile size, empty space, etc. So I am looking for "informal" answer
> applicable in real life.
>
> In my tests (done with 8K block size) max extent size was 64M, even
> for 20 GB table.
> I saw this value mentioned in several discussions, although Jonathan
> Lewis mentions 256M:
>
> http://www.jlcomp.demon.co.uk/faq/lmt_losses.html
>
> Why do I need an answer: we have several databases that were created
> with 20+ filesystems,
> 50+ tablespaces, many tablespaces between 1GB and 3 GB, and with
> random distribution of datafiles between filesystems. Now the
> filesystems are 98% or so full, AUTOEXTEND has been disabled,
> and I need to monitor next extent size as extent allocation in an LMT
> can fail even when tablespace has 15% free space.
>
> Thanks

256M as Jonathan notes it. Its quite rare that you might ever see 256M extnet size but that is the limit stated by Oracle Support also (read in Metalink, Oracle Support replies in forums .. example being the thread that Laurent started. Search "LMT autoallocate laurent" in metalink).

For tablespaces that are prone to the fragmentation issue you talk about regarding auto allocate, you might be better off to make them uniform extent in the first place ... or provide enough free space to minimize this issue from happening.

Anurag Received on Fri Jun 13 2008 - 10:14:25 CDT