Re: max extent size in locally managed tablespace with AUTOALLOCATE

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: Fri, 13 Jun 2008 23:36:08 +1000
Message-ID: <g2tt44$hg2$1@registered.motzarella.org>


Carlos wrote,on my timestamp of 13/06/2008 8:33 PM:

> 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.


Yup, definitely. non uniform LMT is kinda dangerous unless one can afford the time - or the tools - to constantly monitor for such problems as shown above. I've got a bell going somewhere that the max extent size is related to the db block size, but I'll be buggered if I can place it. Maybe have a look at one of the "database limits" section in one of the dba manuals? Can't remember which, though. Received on Fri Jun 13 2008 - 08:36:08 CDT

Original text of this message