Re: max extent size in locally managed tablespace with AUTOALLOCATE
Date: Fri, 13 Jun 2008 06:30:09 -0700 (PDT)
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:
> > 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.
> 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