Re: max extent size in locally managed tablespace with AUTOALLOCATE

On Jun 13, 6:33 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

The auto-allocate tablespace space allocation management method is commonly used in production environments especially to house vendor packages.

The largest extent size I have seen or heard of at least through 10g is 64M.

64K, 1M, 8M, 64M are the stardard sizes used in tablespace created using auto-allocate. Other extent sizes are possilbe due to leftover  space at the end of files.

For tablespace converted to auto-allocate all kind of extent sizes are possible and pctincrease has an effect.

I also prefer uniform extent sizes from a space management point of view.

HTH -- Mark D Powell -- Received on Fri Jun 13 2008 - 08:30:09 CDT