Re: max extent size in locally managed tablespace with AUTOALLOCATE

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Fri, 13 Jun 2008 03:33:06 -0700 (PDT)
Message-ID: <fd0d1c70-6384-4e5e-8446-bd4634bc9313@m36g2000hse.googlegroups.com>


On 13 jun, 11:31, 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

I think you need 'uniform' LMT tablespaces.

IMHO 'System' LMT's exist only to avoid DBA extra work over personal/ development/nonimportant databases. On a production system you should follow the 'uniform' way.

HTH. Cheers.

Carlos. Received on Fri Jun 13 2008 - 05:33:06 CDT

Original text of this message