Oracle7 Server Concepts

Extents

An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. Each segment is composed of one or more extents. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment.

This section describes how extents are allocated for segments.

When Extents Are Allocated for Segments

No matter what type, each segment in a database is created with at least one extent to hold its data. This extent is called the segment's initial extent.

Note: Rollback segments always have at least two extents.

For example, when you create a table, its data segment contains an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table's rows.

If the data blocks of a segment's initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previous extent in that segment. The next section explains the factors controlling the size of incremental extents.

For maintenance purposes, each segment in a database contains a segment header block that describes the characteristics of that segment and a directory (list) of the extents in that segment.

Extents and the Parallel Query Option

When you use the parallel query option to create indexes and non-clustered tables in parallel, each query server allocates a new extent and fills the extent with the table or index's data. Thus, if you create an index with a degree of parallelism of three, there will be at least three extents for that index initially.

Serial operations require the object to have at least one extent. Parallel creations require that non-clustered tables or indexes have at least as many extents as there are query servers that create the object.

When you create a table or index with the parallel query option, it is possible to create "pockets" of free space. This occurs when you specify more query servers than there are datafiles in the tablespace. Oracle cannot coalesce this free space with other free space, so this space is available only for subsequent inserts into that table.

For example, if you specify a degree of parallelism of three for a CREATE TABLE ... AS <subquery> statement but there is only one datafile in the tablespace, the situation illustrated in Figure 3 - 6 can arise. Oracle can only coalesce the free space in the last extent of a table or index in each datafile, so all "pockets" of free space within internal table extents of a datafile cannot be coalesced with other free space and allocated as extents.

Figure 3 - 6. Unusable Free Space

To alleviate the free space problem, set the degree of parallelism to less than or equal to the number of datafiles in the tablespace that you are placing the non-clustered table or index. Oracle assigns query servers to datafiles in a round-robin fashion, so specifying fewer query servers than datafiles ensures that all free space can be used later by all tables in the tablespace for subsequent extent allocation.

For more information about the parallel query option and creating non-clustered tables and indexes in parallel, see Oracle7 Server Tuning.

For more information about datafiles and tablespaces, see Chapter 4, "Tablespaces and Datafiles".

How Extents Are Allocated for Segments

Oracle controls the allocation of extents for a given segment. The procedure to allocate a new extent for a segment is as follows:

1. Oracle searches through the free space (in the tablespace that contains the segment) for the first free, contiguous set of data blocks of an incremental extent's size or larger. Oracle finds the free space for the new extent by using the following algorithm:

1.1 Oracle searches for a contiguous set of data blocks that matches the size of new extent, then adds one block to reduce internal fragmentation. For example, if a new extent requires 19 data blocks, Oracle searches for exactly 20 contiguous data blocks. However, if the new extent is 5 or fewer blocks, Oracle does not add an extra block to the request.

1.2 If an exact match is not found, Oracle then searches for a set of contiguous data blocks equal to or greater than the amount needed. If Oracle finds a group of contiguous blocks that is at least five blocks greater than the size of the extent that is needed, it splits the group of blocks into separate extents, one of which is the size it needs; if Oracle finds a group of blocks that is larger than the size it needs, but less than five blocks larger, it allocates all the contiguous blocks.

Continuing with the example, if Oracle does not find a set of exactly 20 contiguous data blocks, Oracle then searches for a set of contiguous data blocks greater than 20. If the first set that Oracle finds contains 25 or more blocks, it breaks the blocks up and allocates twenty of them to the new extent. Otherwise, it allocates all of the blocks (between 21 and 24) to the new extent.

1.3 If Oracle does not find a larger set of contiguous data blocks, Oracle then coalesces any free, adjacent data blocks in the corresponding tablespace so that larger sets of contiguous data blocks are formed. (The SMON background process also periodically coalesces adjacent free space.) After coalescing a tablespace's data blocks, Oracle performs the searches described in 1.1. and 1.2.. again. If an extent cannot be allocated after the second search, Oracle returns an error.

2. Once Oracle finds the necessary free space in the tablespace, Oracle allocates a portion of the free space that corresponds to the size of the incremental extent. If Oracle had found a larger amount of free space than was required for the extent, Oracle leaves the remainder as free space (no smaller than five contiguous blocks).

3. Oracle updates the segment header and data dictionary to show that a new extent has been allocated and that the allocated space is no longer free. Usually, Oracle clears the blocks of a newly allocated extent when the extent is first used. In a few cases, however, such as when a database administrator issues an ALTER TABLE or ALTER CLUSTER statement with the ALLOCATE EXTENT option while using free list groups, Oracle clears the extent's blocks when it allocates the extent.

When Extents Are Deallocated

In general, the extents of a segment do not return to the tablespace until you drop the 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.

Periodically, Oracle may deallocate one or more extents of a rollback segment.

A DBA can deallocate unused extents using the following SQL syntax:

		ALTER TABLE table_name DEALLOCATE UNUSED

For More Information on Deallocating Extents

See Oracle7 Server Administrator's Guide and Oracle7 Server SQL Reference.

When extents are freed, Oracle updates the data dictionary to reflect the regained extents as available space. All data in the blocks of freed extents is inaccessible, and Oracle clears out the data when the blocks are subsequently reused for other extents.

Non-Clustered Tables, Snapshots, and Snapshot Logs

As long as a non-clustered table (including an underlying table for a snapshot or snapshot log) 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.

When you drop a non-clustered table, Oracle reclaims all the extents of its data and index segments for the tablespaces that they were in and makes the extents available for other objects in the tablespace. Subsequently, when other segments require large extents, Oracle identifies and combines contiguous reclaimed extents to form the requested larger extents.

Clustered Tables and Snapshots

Clustered tables and snapshots store their information in the data segment created for the cluster. Therefore, if you drop a clustered table, 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.

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.

Rollback Segments

Oracle periodically checks to see if the rollback segments of the database 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. See "How Extents Are Deallocated from a Rollback Segment"

for more information.

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.

Determining Sizes and Limits of Segment 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.