Overview of Extents

An extent is a logical structure of oracle database that comprises of the contigous data blocks. These logical storage units are stored in segments. Oracle allocates a new extent in a segment, when the space in the segment is saturated.

When Extents Are Allocated : On creating a table, initial extents are allocated in the data segment with a specific number of data blocks. Despite no rows exist on creating a table, the Oracle data blocks that correspond to the initial extent are reserved for that table's rows. Oracle automatically allocates an incremental extent for a segment when initial extent is space is exhausted and extra space is needed.An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment. Segments further store information that relates with extents as directory, generaly this directory information stored in the header block is for maintenance purpose.

Extents are allocated somewhat differently in parallel SQL statements, which entail multiple server processes.

Determine the Number and Size of Extents
Storage parameters expressed in terms of extents define every segment. Storage parameters apply to all types of segments. They control how Oracle allocates free database space for a given segment. For example, you can determine how much space is initially reserved for a table's data segment or you can limit the number of extents the table can allocate by specifying the storage parameters of a table in the STORAGE clause of the CREATE TABLE statement. On creating a table user can explicitily define the storage parameters associated with a tbale. In case not custom storage paramenter for a table is defines, Oracle uses the default storage paramnts that is associated with the default tablespace allocated with the tables.

You can have dictionary managed tablespaces, which rely on data dictionary tables to track space utilization, or locally managed tablespaces, which use bitmaps (instead of data dictionary tables) to track used and free space. Because of the better performance and easier manageability of locally managed tablespaces, the default for non-SYSTEM permanent tablespaces is locally managed whenever the type of extent management is not explicitly specified.

A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.

For uniform extents, you can specify an extent size or use the default size, which is 1 MB. Ensure that each extent contains at least five database blocks, given the database block size. Temporary tablespaces that manage their extents locally can only use this type of allocation.

For system-managed extents, Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. If the tablespaces are created with 'segment space management auto', and if the database block size is 16K or higher, then Oracle manages segment size by creating extents with a minimum size of 1M. This is the default for permanent tablespaces.

The storage parameters INITIAL, NEXT, PCTINCREASE, and MINEXTENTS cannot be specified at the tablespace level for locally managed tablespaces. They can, however, be specified at the segment level. In this case, INITIAL, NEXT, PCTINCREASE, and MINEXTENTS are used together to compute the initial size of the segment. After the segment size is computed, internal algorithms determine the size of each extent.

How Extents Are Allocated

Oracle uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed.

With locally managed tablespaces, Oracle looks for free space to allocate to a new extent by first determining a candidate datafile in the tablespace and then searching the datafile's bitmap for the required number of adjacent free blocks. If that datafile does not have enough adjacent free space, then Oracle looks in another datafile.

It is strongly recommended by Oracle stronglto use locally managed tablespaces.

When Extents Are Deallocated
The Oracle Database provides a Segment Advisor that helps you determine whether an object has space available for reclamation based on the level of space fragmentation within the object.

In general, the extents of a segment do not return to the tablespace until you drop the schema object whose data is stored in the segment (using a DROP TABLE or DROP CLUSTER statement). Exceptions to this include the following:

The owner of a table or cluster, or a user with the DELETE ANY privilege, can truncate the table or cluster with a TRUNCATE...DROP STORAGE statement.

A database administrator (DBA) can deallocate unused extents using the following SQL syntax:

ALTER TABLE table_name DEALLOCATE UNUSED;

Periodically, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.

When extents are freed, Oracle modifies the bitmap in the datafile (for locally managed tablespaces) or updates the data dictionary (for dictionary managed tablespaces) to reflect the regained extents as available space. Any data in the blocks of freed extents becomes inaccessible.

Extents in Non clustered Tables

As long as a non-clustered table exists or until you truncate the table, any data block allocated to its data segment remains allocated for the table. Oracle inserts new rows into a block if there is enough room. Even if you delete all rows of a table, Oracle does not reclaim the data blocks for use by other objects in the tablespace.

After you drop a non-clustered table, this space can be reclaimed when other extents require free space. Oracle reclaims all the extents of the table's data and index segments for the tablespaces that they were in and makes the extents available for other schema objects in the same tablespace.

In dictionary managed tablespaces, when a segment requires an extent larger than the available extents, Oracle identifies and combines contiguous reclaimed extents to form a larger one. This is called coalescing extents. Coalescing extents is not necessary in locally managed tablespaces, because all contiguous free space is available for allocation to a new extent regardless of whether it was reclaimed from one or more extents.

Extents in Clustered Tables
Clustered tables store information in the data segment created for the cluster. Therefore, if you drop one table in a cluster, the data segment remains for the other tables in the cluster, and no extents are deallocated. You can also truncate clusters (except for hash clusters) to free extents.

Extents in Materialized Views and Their Logs
Oracle deallocates the extents of materialized views and materialized view logs in the same manner as for tables and clusters.

Extents in Indexes

All extents allocated to an index segment remain allocated as long as the index exists. When you drop the index or associated table or cluster, Oracle reclaims the extents for other uses within the tablespace.

Extents in Temporary Segments
When Oracle completes the execution of a statement requiring a temporary segment, Oracle automatically drops the temporary segment and returns the extents allocated for that segment to the associated tablespace. A single sort allocates its own temporary segment in a temporary tablespace of the user issuing the statement and then returns the extents to the tablespaces.

Multiple sorts, however, can use sort segments in temporary tablespaces designated exclusively for sorts. These sort segments are allocated only once for the instance, and they are not returned after the sort, but remain available for other multiple sorts.

A temporary segment in a temporary table contains data for multiple statements of a single transaction or session. Oracle drops the temporary segment at the end of the transaction or session, returning the extents allocated for that segment to the associated tablespace.

Extents in Rollback Segments
Oracle periodically checks the rollback segments of the database to see if they have grown larger than their optimal size. If a rollback segment is larger than is optimal (that is, it has too many extents), then Oracle automatically deallocates one or more extents from the rollback segment.