Role of Oracle extents

Each segment is composed of extents, which are groups of contiguous data blocks. Oracle manages the use of extents, allocating a new one when one is full. An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents in turn make up a segment. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment.

• When Extents Are Allocated:
  When you create a table, Oracle allocates to the table's data segment an initial extent^[1] 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 previously allocated extent in that segment. For maintenance purposes, the header block of each segment contains a directory of the extents in that segment.

Storage parameters expressed in terms of extents define every segment. Storage parameters apply to all types of segments and control how Oracle allocates free database space for a given segment. For example, you can

1. determine how much space is initially reserved for a table's data segment or
2. 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.

If you do not specify a table's storage parameters, then it uses the default storage parameters of the tablespace. You can have dictionary managed tablespaces, which rely on data dictionary tables to track space utilization, or locally managed tablespaces, which use bitmaps 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

1. uniform extent sizes or
2. variable extent sizes

which are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) clause specifies the type of allocation.

1. 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.
2. 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.

• Extent Size You can specify the size of an extent for a database object with the CREATE command for the object, or alter the size of subsequent extents with the ALTER command for the object. When you first create a database table, Oracle allocates an initial extent for the segment. If the data in the table gets too large to fit in the initial extent, Oracle allocates a new one. The following series of diagrams illustrate how this happens.

To display statistics for "free extents" and "coalescing activity" for each tablespace in an Oracle database, you can use views and SQL queries that provide information about free space and fragmentation in tablespaces. Here’s how you can do it:

1. Query for Free Extents
   • Free extents are portions of free space available in a tablespace. Use the DBA_FREE_SPACE view to get information about free extents.

   • SELECT 
         TABLESPACE_NAME,
         COUNT(*) AS NUMBER_OF_FREE_EXTENTS,
         SUM(BYTES) AS TOTAL_FREE_SPACE,
         MAX(BYTES) AS MAX_FREE_EXTENT_SIZE,
         MIN(BYTES) AS MIN_FREE_EXTENT_SIZE
     FROM 
         DBA_FREE_SPACE
     GROUP BY 
         TABLESPACE_NAME
     ORDER BY 
         TABLESPACE_NAME;
                     

   • This query provides:
   • The number of free extents (NUMBER_OF_FREE_EXTENTS) in each tablespace.
   • Total free space (TOTAL_FREE_SPACE) in bytes.
   • The maximum and minimum sizes of free extents (MAX_FREE_EXTENT_SIZE and MIN_FREE_EXTENT_SIZE).
2. Query for Coalescing Activity
   • Coalescing activity refers to the merging of adjacent free extents into larger extents. You can determine potential fragmentation and coalescing opportunities by analyzing adjacent free extents.

   • SELECT 
         TABLESPACE_NAME,
         COUNT(*) AS FRAGMENTED_EXTENTS,
         SUM(BYTES) AS TOTAL_FRAGMENTED_SPACE
     FROM 
         DBA_FREE_SPACE
     WHERE 
         FILE_ID IN (
             SELECT DISTINCT FILE_ID 
             FROM DBA_FREE_SPACE 
             GROUP BY FILE_ID, BLOCK_ID 
             HAVING COUNT(*) > 1
         )
     GROUP BY 
         TABLESPACE_NAME
     ORDER BY 
         TABLESPACE_NAME;
                     

   • This query identifies fragmented extents in each tablespace.
3. Optionally, Use Oracle Enterprise Manager or Tools
   • If you prefer a graphical interface, Oracle Enterprise Manager provides views and reports for tablespace utilization, free extents, and coalescing activity.
   • Navigate to the Tablespaces section in the Storage tab.
4. Coalescing Free Space
   • To manually coalesce free space, use the ALTER TABLESPACE statement:

   • ALTER TABLESPACE tablespace_name COALESCE;
                     

Automating and Monitoring: You can create a script to automate the monitoring of free extents and fragmentation by scheduling these queries in Oracle Scheduler and sending the results to a monitoring system or an email.

To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:

SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
COUNT(*) "PIECES",
MAX(blocks) "MAXIMUM",
MIN(blocks) "MINIMUM",
AVG(blocks) "AVERAGE",
SUM(blocks) "TOTAL"
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;

PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.
The incremental extent can be the same size as the initial extent, or you can indicate that each subsequent extent must be a certain percentage larger than the previous extent. For example, you could indicate that each subsequent extent must be 50% larger than the previous one. You can also specify the minimum and maximum number of extents. The minimum number of extents controls how many extents are initially allocated when you create the object. If you specify a maximum number of extents, it has the effect of limiting the size of the table.
The next lesson shows how to set extent parameters for database objects.

Click the Quiz link below to answer a few questions about space management.
Space Management - Quiz

[1] initial extent:The INITIAL size is the size of the initial extent allocated when the object is created.