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.
Articles Related
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.