Oracle Tablespaces, Segments, Extents, and Datafiles

Lesson 2 defined the columns, data types, defaults, and integrity rules of a relational table. This lesson follows that logical definition into storage. Oracle AI Database 26ai places a storage-bearing table or table partition in a segment, allocates the segment's extents from a tablespace, and stores the extents' Oracle data blocks in the tablespace's datafiles.

The important distinction is between logical organization and physical persistence. Tables, tablespaces, segments, extents, and Oracle data blocks are structures that Oracle recognizes and manages. Datafiles provide the physical files in which those blocks persist. The operating system, Oracle Automatic Storage Management (ASM), or a managed database service can control the storage beneath the files without changing this logical model.

A table definition does not always consume storage immediately. With deferred segment creation, Oracle can record the table in the data dictionary before creating its segment. The segment normally appears when the first row is inserted or another operation requires storage. Therefore, a table and a table segment are related, but they are not the same kind of object.

Each layer has a specific role and boundary. Keeping these boundaries clear prevents several common errors, such as describing a tablespace as a disk or assuming that one extent can be divided between two files.

Structure	Layer	Role	Key boundary
Table	Logical schema object	Defines rows, columns, constraints, and table behavior	Can temporarily exist without a segment when segment creation is deferred
Segment	Logical storage	Contains the storage allocated to one database object or object partition	Belongs to one tablespace
Extent	Logical allocation unit	Supplies a set of Oracle data blocks to one segment	Resides wholly within one datafile
Oracle data block	Logical storage and I/O unit	Stores row pieces, index entries, or other segment data	Maps to persistent storage within a datafile
Tablespace	Logical container	Groups segments and defines space-allocation policies	Uses one bigfile or one or more smallfiles
Datafile	Physical database file	Contains Oracle data blocks for a tablespace	Belongs to exactly one tablespace

The storage hierarchy can be summarized as follows:

1. A table or table partition uses a segment when storage has been allocated.
2. The segment consists of one or more extents.
3. Each extent consists of logically contiguous Oracle data blocks.
4. Every extent is stored within one datafile belonging to the segment's tablespace.

An Oracle data block is the smallest logical storage unit and the minimum unit of database I/O. The database's standard block size is determined by DB_BLOCK_SIZE; 8 KB is common, but it is not the only supported size. Oracle blocks are also distinct from operating-system blocks. A logically contiguous sequence of Oracle blocks does not guarantee physically adjacent sectors because file systems, RAID, ASM, and managed storage can distribute the underlying bytes.

A nonpartitioned heap-organized table commonly has one table segment after storage is allocated. A partitioned table normally has a separate segment for each storage-bearing partition or subpartition. This distinction matters because each segment remains in one tablespace, while different partitions of the same logical table can be assigned to different tablespaces.

Other structures associated with a table can have their own segments. A primary-key or secondary index uses an index segment. A large object can use a LOB data segment and a LOB index segment. An index-organized table, overflow area, or materialized view can introduce other segment types. These are not pieces of one universal table segment; they are separate segments with their own placement and allocation.

Tablespaces are also not owned by schemas. One tablespace can contain segments belonging to objects in several schemas, and one schema can place different objects in several tablespaces. In a multitenant database, each pluggable database or application container has its own tablespace context. The same storage hierarchy applies inside that container.

The statement that a tablespace contains one or more datafiles needs one qualification. Oracle supports smallfile and bigfile tablespaces. The consolidated diagram shows a smallfile tablespace because USERS is backed by users01.dbf and users02.dbf.

Tablespace form	Datafile relationship	Typical capacity action
Smallfile	Can contain multiple datafiles	Resize or autoextend an existing file, or add an eligible new datafile
Bigfile	Contains one datafile or tempfile	Resize or otherwise manage the single file; a second datafile cannot be added

Regardless of the form, a datafile belongs to exactly one tablespace. A segment also belongs to exactly one tablespace. Within a smallfile tablespace, however, one segment can have different extents in different datafiles. Oracle might place extent P1 for the PERSON segment in users01.dbf and extent P2 in users02.dbf. One extent can never be split between the files.

Modern Oracle databases use locally managed tablespaces by default. A locally managed tablespace maintains bitmaps in its datafiles to record free and used extents. This avoids updating dictionary tables for every extent allocation and enables Oracle to track adjacent free space efficiently.

Mechanism	What it manages	Meaning
EXTENT MANAGEMENT LOCAL AUTOALLOCATE	Extent tracking and extent sizing	Oracle selects extent sizes according to its allocation policy
EXTENT MANAGEMENT LOCAL UNIFORM SIZE	Extent tracking and fixed extent sizing	Every extent uses the size declared for the tablespace
SEGMENT SPACE MANAGEMENT AUTO	Reusable space inside segments	Automatic Segment Space Management uses bitmaps instead of manual freelists

Extent management and segment space management solve different problems. AUTOALLOCATE or UNIFORM controls how extents are sized and tracked in the tablespace. Automatic Segment Space Management (ASSM) tracks which blocks within a segment have room for inserted or updated rows. ASSM is also different from Oracle Automatic Storage Management (ASM), which manages database files across storage disk groups.

The old diagrams used a sequence of manually chosen 15K, 20K, 25K, 50K, and 100K allocations. That sequence should not be treated as the current growth model. In an AUTOALLOCATE locally managed tablespace, Oracle determines subsequent extent sizes, and a table-level NEXT value does not control those later allocations. In a UNIFORM tablespace, the declared uniform extent size applies. The legacy PCTINCREASE parameter does not govern subsequent extent growth in locally managed tablespaces.

The TABLESPACE clause identifies the logical container for the table segment. It does not name a particular datafile. The following statement aligns the table with the USERS tablespace shown in the diagram:

CREATE TABLE person (
    person_id  NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(30 CHAR)
        CONSTRAINT person_first_name_nn NOT NULL,
    last_name  VARCHAR2(30 CHAR)
        CONSTRAINT person_last_name_nn NOT NULL,
    CONSTRAINT person_pk PRIMARY KEY (person_id)
)
TABLESPACE users;

Oracle chooses an eligible extent in the USERS tablespace according to the tablespace's allocation policy. The statement does not instruct Oracle to use users01.dbf or users02.dbf. If the clause is omitted, Oracle normally uses the table owner's default permanent tablespace. The owner must still have an appropriate quota or applicable privilege for that tablespace.

Deferred segment creation can postpone the first physical allocation. In that case, the table appears in metadata after CREATE TABLE, but its segment and first extent are created only when storage is required. This is why metadata views and segment views can initially report different aspects of the same table.

1. Inserts and updates first consume usable free space in blocks already allocated to the segment.
2. When the segment needs more blocks, Oracle requests another extent from the segment's tablespace.
3. The locally managed tablespace selects an available extent according to its AUTOALLOCATE or UNIFORM policy.
4. Oracle allocates the entire extent within one eligible datafile.
5. A later extent for the same segment can be placed in another datafile belonging to the same smallfile tablespace.
6. If Oracle cannot allocate the required extent, capacity must be reclaimed or increased before the operation can succeed.

A configured datafile can use AUTOEXTEND ON to grow by its specified increment, subject to its maximum size and the capacity of the underlying storage. AUTOEXTEND enlarges that file; it does not create another datafile. A DBA or database service can resize a file or add a new datafile to an eligible smallfile tablespace. A bigfile tablespace retains its single-file model and cannot use ALTER TABLESPACE ... ADD DATAFILE to acquire a second file.

The data dictionary lets you observe each layer. First, query USER_TABLES to see a table's declared tablespace and whether its segment has been created:

SELECT table_name,
       tablespace_name,
       segment_created
FROM   user_tables
WHERE  table_name IN ('PERSON', 'ORDERS')
ORDER  BY table_name;

Next, query USER_SEGMENTS to see the storage currently allocated to each segment:

SELECT segment_name,
       segment_type,
       tablespace_name,
       bytes,
       blocks,
       extents
FROM   user_segments
WHERE  segment_name IN ('PERSON', 'ORDERS')
ORDER  BY segment_name, segment_type;

Finally, query USER_EXTENTS. Each row represents one extent, and FILE_ID identifies the datafile containing that entire extent:

SELECT segment_name,
       extent_id,
       file_id,
       block_id,
       blocks,
       bytes
FROM   user_extents
WHERE  segment_name IN ('PERSON', 'ORDERS')
ORDER  BY segment_name, extent_id;

Do not expect these queries to reproduce the diagram exactly. The rows returned depend on whether segments have been created, how much data has been stored, which extents were available, and how the database is configured. One small table might have all its extents in one file even when its tablespace has several files.

Users with suitable data-dictionary privileges can join DBA_TABLESPACES and DBA_DATA_FILES to inspect the tablespace form, allocation policy, segment space management, file sizes, and autoextension settings:

SELECT t.tablespace_name,
       t.bigfile,
       t.extent_management,
       t.allocation_type,
       t.segment_space_management,
       f.file_id,
       f.file_name,
       f.autoextensible,
       f.bytes,
       f.maxbytes
FROM   dba_tablespaces t
JOIN   dba_data_files f
       ON f.tablespace_name = t.tablespace_name
WHERE  t.tablespace_name = 'USERS'
ORDER  BY f.file_id;

Access to DBA_ views requires appropriate privileges; broad administrative privileges should not be granted merely to run a lesson query. Ordinary schema users can learn most of the object-level concepts from the corresponding USER_ views.

A datafile can be an explicitly named operating-system file, an Oracle Managed File, or a file managed through ASM. Oracle Managed Files can generate and manage file names when the environment is configured for them. ASM can stripe and mirror file extents across disks in a disk group, so a database datafile should not be equated with one physical disk spindle.

Cloud database services can automate or restrict file operations, but the durable relationship remains the same: segments obtain extents from a tablespace, extents contain Oracle blocks, and those blocks persist in the tablespace's datafile or datafiles. Tablespace encryption, backup and recovery planning, and storage placement can all involve tablespaces and datafiles, but assigning an object to a named tablespace does not by itself guarantee a particular performance result or physical device.

• A table is a logical schema object; its allocated storage is represented by one or more appropriate segments.
• A segment belongs to one tablespace and consists of one or more extents.
• Each extent consists of logically contiguous Oracle data blocks and remains wholly within one datafile.
• Different extents of one segment can reside in different datafiles belonging to the same smallfile tablespace.
• A datafile belongs to exactly one tablespace; a bigfile tablespace has one file, while a smallfile tablespace can have several.
• Locally managed AUTOALLOCATE or UNIFORM policies govern extent allocation.
• ASSM separately manages usable space inside segments, while ASM manages database files on storage.
• The TABLESPACE clause selects a logical container, not a specific datafile or disk.