Manage Learn to apply best practices and optimize your operations.

Understanding autoallocate and segment space management

What is difference between autoallocate property and segment space management auto in Oracle9i?

AUTOALLOCATE refers to a method in which Oracle can manage space locally in the tablespace. Prior to Locally Managed Tablespaces (LMTs), all tablespaces were Dictionary Managed Tablespaces (DMTs). DMTs used two data dictionary tables to keep track of used space (SYS.UET$) and free space (SYS.FET$). Allocation and deallocation of extents caused a large burden to be placed on these two dictionary tables. LMTs moved the space management of extents into bitmaps in the datafile's header. When you have a LMT, you have two methods of letting Oracle allocate extents. You can specify that each extent in a tablespace will always be a UNIFORM size. Or, you can let Oracle automatically determine the next extent size with the AUTOALLOCATE policy. This has nothing to do with SEGMENT SPACE MANAGEMENT. Rather, LMTs, be they UNIFORM or AUTOALLOCATE, are only concerned with allocating an extent for a segment.

It is confusing because SEGMENT SPACE MANAGEMENT uses the same words that I've used above. But SEGMENT SPACE MANAGEMENT is used to manage space within each data block. When you insert a row of data into a table, Oracle must place that row into a block that has room for it. When you delete a row of data from a table, Oracle must have a way of knowing that the block can now accept a new row. The old mechanism for this was the Freelist, or list of free blocks capable of accepting new rows of data. Oracle 9i introduced a successor to Freelists. When you set SEGMENT SPACE MANAGEMENT to AUTO, then Oracle will use bitmaps for each segment to denote if the block can accept a new row or not.

SEGMENT SPACE MANAGEMENT and LMTs both use bitmaps for managing space. The former manages space for rows of data within a block. The latter manages space for allocation and deallocation of extents within a datafile.

Dig Deeper on Oracle database design and architecture