max extent size in locally managed tablespace with AUTOALLOCATE

From: Vsevolod Afanassiev <vafanassiev_at_yahoo.com>
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

Original text of this message