Oracle Default Storage Clause

The default storage clause generally applies to tablespaces and datafiles (even databases as a whole) where all objects and structures within the tablespace (datafile or database) can inherit the tablespace's default storage clause or override the tablespace default storage clause with it's own default storage clause.

• INITIAL and NEXT - INITIAL must be => 2 * DB_BLOCK_SIZE and NEXT => 5 * DB_BLOCK_SIZE.
  • INITIAL - byte size of the initial extent of the object segment. All values rounded to the nearest Oracle block size.
    • Default - 10,240 bytes.
    • Minimum - 4,096 bytes.
    • Maximum - 4,095 megabytes.
  • NEXT - size of the next extent after INITIAL is used. This value will be used for each new extent if PCTINCREASE is zero.
    • Default - 5 blocks.
    • Minimum - 1 block.
    • Maximum - 4,095 megabytes.
• MINEXTENTS - number of initial extents for the object, except for rollback segments it is set to 1.
• MAXEXTENTS - largest number of extents allowed for an object. Defaults to maximum allowed for block size. Setting MAXEXTENTS to UNLIMITED can help to simplify planning for large objects.
• PCTINCREASE - how much to grow each extent after the INITIAL and NEXT extents are used. Thus with PCTINCREASE set to 50 then each subsequent extent will be increased by 50% of the previously created extent. PCTINCREASE is the growth rate for subsequent extents. Note that a tablespace with a default storage setting of PCTINCREASE equal to zero will not be automatically coalesced.
• OPTIMAL - the value to which a rollback segment will shrink after extending. Used for rollback segments only. Non-rollback segments will not be shrunk after extension.
• FREELIST GROUPS - number of freelist groups to maintain for a table or index.
• FREELISTS - generally only meaningful for parallel server databases and does not apply to tablespaces. The number of freelists for each of the freelist groups for a table, index or cluster.
  • Minimum - 1.
  • Maximum - block size dependant.
• BUFFER_POOL - all blocks of the specified object are stored in the specified memory area. The buffer pool can be defined for schema objects and not for tablespaces or rollback segments.
  • KEEP - object blocks are retained in memory for later use that subsequent I/O is avaoided.
  • RECYCLE - object blocks removed from memory when no longer in use.
  • DEFAULT - for objects not assigned to KEEP or RECYCLE.

Tablespaces are subject to fragmentation. Fragmentation is the main cause for space related problems with tablespaces. As tables are created and dropped rollback segments grow and are shrunk back to OPTIMAL or indexes are reorganised or rebuilt. All of these events result in the allocation or dropping of extents. Frequent deallocation of extents results in fragmented tablespaces. The ALTER command will allow coalescence of contiguous free space areas. Contiguous free space areas are deallocated extents which reside next to each other. Note that only rollback segments are shrunk automatically using the OPTIMAL setting.