Configuring Oracle7 EXTENTS

• The STORAGE clause of Table
  Each table when created may specify the size of its initial extent as well as the size of its second extent. The storage clause can also specify a minimum number of extents to be allocated when the table is created and the maximum number of extents which the table can extend to.
  

• How many EXTENTs should a table have?
  There is an argument for keeping the number of extent small but insisting a single extent for the whole table can cause problems in disk space allocation. The facts that table will occupy ALL the intended space right from the beginning and a single extent cannot span across multiple data files, make the "Single Extent" configuration inflexible.
  The other approach is to have multiple extents (not too many) of an appropriate size. As Oracle cannot read across multiple extents, the full table scan's performance should not be affected by the number of extents if the size is set to a multiple of Oracle read (determined by the parameters DB_BLOCK_SIZE & DB_FILE_MULTIBLOCK_READ_COUNT)
  Caveat: a large number of the extents will increase the cost of space management as Oracle sometimes needs to apply dyanmic space management (changing the extent size) to free up fragmented storage space.

• How to REUSE extents efficiently?
  You need to consider the REUSE issue globally. The impact is not on the tables and indexes you create, rather it is the problem of how the other users on the same tablespace can reuse the released extents after a table or index has been dropped.
  When table or index are dropped (dropping segments) their free extents can be reused by the other tables or indexes in the same tablespace. However, if these tables and indexes have different extent-configurations, the free extents might need to be coalesced to form the right size. For instance, your dropped table's extent size is 1 MB, and your colleage uses 2 MB as the extent size. 2 of the released extents need to be coalesced to form a 2 MB extent before the free space can be reused.
  All the users on the same tablespace should adopt a policy to use multiples of Oracle read size as the size of extent. In that way, the released extent can be re-used when the requirement is of the same size or a fraction of that size (1/2, 1/3,...).
  Notice that Oracle SMON process automatically coalesces extents only when the PCTINCREASE on the tablespace is set to non-zero. (the problem of non-zero PCTINCREASE is that the storage space will grow at accelerated rate) If the PCTINCREASE of the tablespace is set to zero, the tablespace has to be manually coalesced. (alter tablespace ... coalesce) The trick is to set the tablespace's PCTINCREASE to 1 (to enable automatic coalesce), and then use a specific STORAGE clause when the table or index is created. (to avoid accelerated growth on storage space)

  	-- the tablespace has a non-zero PCTINCREASE setting
  	create table test_table (x varchar2(10))
  	tablespace app_ref storage (initial 1M next 1M pctincrease 0);