Examples Of Odd Extent Sizes In Tablespaces With AUTOALLOCATE

The "normal" AutoAllocate Extent sizes are 64K, 1M, 8M, 64M.
A table is allocated 16 extents of 64K until it reaches a size of 1MB. After that, extent sizes are 1MB each until a size of 64MB. That makes 79 extents.Then, extent sizes are of 8MB till a total size of 1GB.After reaching 1GB, the sizes are 64MB each.
These step functions are *NOT* documented by Oracle but have been observed by many DBAs.

Having said that, we sometimes we see Segments and Tablespaceswith extent sizes that are slightly different.
This can happen when Oracle finds a small "hole" (eg because other segments have been dropped/rebuilt or near the end of the datafile) and uses that "hole"precisely because AutoAllocate allows Oracle to decide ExtentSizes.
{For example : If the table had a static NEXT of 1M and there was no free extent of 1M in the datafile, only say 256K, Oracle would report an"unable to allocate extent of size 128 blocks". But with AutoAllocate,Oracle could actually create a 128K extent at that time, still leaving 128K free for another extent !}

Another case is when a table is created with a very large INITIAL, Oracle might set the Extent Sizes to 1MB from the beginning.

For other examples and discussion also see

http://www.freelists.org/archives/oracle-l/01-2005/msg00782.html

[reference to a posting on HJR's blog deleted as it is no longer available]

http://jonathanlewis.wordpress.com/2007/05/29/autoallocate-and-px/

(repeated parallel create/insert at high degrees of parallelismcan result in objects acquiring a very large number of small extents)

UPDATE 10-Apr-12 : Also see my post on Sizing OR Growing a Table in AUTOALLOCATE






Use such a query to get extent sizes at the tablespace level :

select tablespace_name, bytes/1024, count(*)
from dba_extents
where tablespace_name in
(select tablespace_name from dba_tablespaces where allocation_type = 'SYSTEM')
group by tablespace_name, bytes/1024
order by 1,2
/