Table of Contents

About

An extent is a group of contiguous data blocks.

An extent can contain data from only one data file.

An extent is a logical unit of database storage space allocation made up of a number of (contiguous|ordered) data blocks.

One or more extents make up a segment.

For instance, a table table is made up of one segment (if not partitioned) and each extent is made up of oracle blocks. With a common block size of 8k, and a table of 80k of data, you may have a table with 10 extents.

Oracle Segment Extent Data Block

Relation with Data File and Segment

A data file is an ordered collection of extend (extend 1, 2, 3, …) whereas segment are an unordered collections of extends.

dba_extents

dba_extents shows the extents for a segment.

Extent_id is the extent number in the segment. Beware that one extent id may have several segments.

Unique key of dba_extends:

  • owner (segment unique key),
  • segment_name (segment unique key),
  • partition_name (segment unique key),
  • segment_type (segment unique key),
  • extent_id
SELECT 
    owner, 
    segment_name, 
    partition_name, 
    segment_type, 
    extent_id, 
    COUNT(*)
FROM dba_extents
GROUP BY owner, segment_name, partition_name, segment_type, extent_id
HAVING COUNT(*) > 1;

no rows selected

If a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.