max extent size in locally managed tablespace with AUTOALLOCATE
Date: Fri, 13 Jun 2008 02:31:43 -0700 (PDT)
Message-ID: <61f2c571-e6b3-4d08-8d4b-e25176b6072a@l42g2000hsc.googlegroups.com>
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 Received on Fri Jun 13 2008 - 04:31:43 CDT