So the 1M initial extent allocation is not due to a "5 block minimum
allocation rule" but due to the fact that automatic space management
requires 3 blocks plus 1 block for the segment header plus 1 block for
actual data = 5 blocks, which lifts the request above the 64K threshold for
a tablespace with 16K extents.

At 05:54 PM 9/30/2003 -0800, you wrote:
>Yes, and there is one thing to add:>If you do not specify INTIAL, the extent allocation starts with 5 blocks for>the intial extent. For 8k, it's 40k, but in an autoallocating LMT extent>cannot be smaller then 64k, so it is the amount of the space allocated. The>interesting question is: what happens with blocksize-16k? Will there be 64k>or two extents of 64k, i.e. 128k?>Here is the answer:>>SQL> create tablespace test1> 2 datafile '/data/oradata/data/test101.dbf' size 64M reuse> 3 autoextend on next 64m maxsize 513M> 4 extent management local autoallocate> 5 segment space management auto> 6 blocksize 16k> 7 />>Tablespace created.>>SQL> create table a (a number) tablespace test1;>>Table created.>>SQL> select owner,segment_name,extent_id,blocks> 2 from dba_extents> 3 where segment_name='A'and tablespace_name='TEST1'> 4 and owner=user> 5 />>OWNER SEGMENT_NA EXTENT_ID BLOCKS>------------------------------ ---------- ---------- ---------->OPS$MGOGALA A 0 64>>16k*64=1M. That means that oracle will allocate a full megabyte for the>initial extent. It cannot take 64k, because it's smaller then 5*16k>(that number of 5 blocks is hardwired into the RDBMS since time immemorial)>and it cannot take two extents because that would, in turn, mean that the>initial extent is smaller then 5 blocks. Therefore, it takes 1M. Jonathan>Lewis was right. Here is one tecnique for optimizing the disk consumption in>such cases:>>SQL> drop tablespace test1 including contents and datafiles;>>Tablespace dropped.>>SQL>>>>>>On 2003.09.30 20:34, Jacques Kilchoer wrote:>> > Ive read the book. PCTINCREASE is basically set to 100% so>> > the extent sizes double. Thats 'basically' how it works. I>> > have seen some posts on dejanews saying it doesnt necessarily>> > work this way and some people are finding large extent sizes>> > with just a few extents and when tables are dropped this is>> > leading to fragmentation. It hasnt happened to me, but the>> > posts on dejanews were from some pretty good posters. So Im>> > playing conservative. We also had one of the contributors>> > here mention issues.>>>>I think Jonathan Lewis has explained the algorithm before, but it's also>>something that we have investigated here.>>The algorithm (ignoring some details) is:>>There will be 4 extent sizes used, 64K, 1M, 8M, 64M>>As long as object allocation is 1M or less, 64K extent sizes are used,>>When object allocation is between 1M and 64M, 1M extent sizes are used.>>When object allocation is between 64M and 1G, 8M extent sizes are used.>>When object allocation is more than 1G, 64M extent sizes are used.>>However, when you initially create the object, the extents are determined by>>figuring out the space allocated to the newly created object taking into>>account the INITIAL, NEXT, PCTINCREASE, MINEXTENTS storage parameters. So>>the>>object might start off with 1M extents instead of starting off with 64K>>extents. The algorithm is similar to the one outlined above but it is more>>complicated. The NEXT and PCTINCREASE seem to be ignored after the object is>>created.>>e.g.>>create table ... tablespace locally_managed_autoallocate>> storage (initial 1M next 512K minextents 15 pctincrease 0) ... ;>>Initial allocation will be 1M + (15 - 1) * 512K = 8M>>When you create the table, you will see eight extents, each of one megabyte.>>There are additional wrinkles, but I don't think the algorithm has "bugs".>>I don't think that there really is "fragmentation" in the sense that an>>unused extent will remain unused forever. All extents will be in one of the>>4>>sizes mentioned above, and all are subject to reuse at some point.>>-->>Please see the official ORACLE-L FAQ: http://www.orafaq.net>>-->>Author: Jacques Kilchoer>> INET: Jacques.Kilchoer_at_quest.com>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com>>San Diego, California -- Mailing list and web hosting services>>--------------------------------------------------------------------->>To REMOVE yourself from this mailing list, send an E-Mail message>>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in>>the message BODY, include a line containing: UNSUB ORACLE-L>>(or the name of mailing list you want to be removed from). You may>>also send the HELP command for other information (like subscribing).>>-->Mladen Gogala>Oracle DBA>-->Please see the official ORACLE-L FAQ: http://www.orafaq.net>-->Author: Mladen Gogala> INET: mgogala_at_adelphia.net>>Fat City Network Services -- 858-538-5051 http://www.fatcity.com>San Diego, California -- Mailing list and web hosting services>--------------------------------------------------------------------->To REMOVE yourself from this mailing list, send an E-Mail message>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in>the message BODY, include a line containing: UNSUB ORACLE-L>(or the name of mailing list you want to be removed from). You may>also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
INET: breitliw_at_centrexcc.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).