I myself prefer to use uniformly sized extents for all objects. That is -- I do not size discrete objects "perfectly", I do not spend time trying to figure out the initial extent should be 137.2 megabytes and the next should be 13meg with a pctincrease of 50%. Rather, I lump objects into classes of sizes. Small objects (lookup tables and such) go into a tablespace where all objects use 64k extents. Slightly larger objects might go into a tablespace that uses 256 or 512k extents. Medium sized objects go into a tablespace with 1meg extents. Truely large objects go into appropriately sized tablespaces.

The goal here is to keep all extent sizes homogenous within a tablespace -- in tablespace T1 all extents are Xmeg, regardless of the object. In tablespace T2, all extents are Ymeg, regardless of the object. The removes any possibility of fragmentation (it just cannot happen if all extents are the same size -- its impossible to have fragmentation). You never have to worry about coalescing free space or anything like that.

There is a myth that some people believe in -- it is the myth that you should attempt to size objects such that all data fits in the initial extent and that having more then a handful of extents is bad. This is quite simply not true. It is perfectly OK to have dozens or even low hundreds of extents for objects. Going overboard into the thousands, while possible, isn't recommended. With locally managed tablespaces in Oracle8i release 8.1, they (tables with more then hundreds of extents) are more feasible.

So, if I have a table with about a 2k record size and 100k records initially we are talking about a 200meg table that grows slowly over time. I would need a better definition of slowly but lets say you add 5meg of data per month (2,500 records per month @2k per record). Every year thats about 60meg of data. I might consider a 50meg extent size for this object. Initially it would consume 4 or 5 extents and add about 1 extent per year.