Lesson 3	Tablespaces and datafiles
Objective	Describe how tables, tablespaces, and datafiles fit together in an Oracle RDBMS

Tablespaces and Datafiles

In Oracle (including Oracle 23ai), application data is stored using a layered storage model. You work with logical objects such as tables and indexes, but Oracle ultimately persists that data in physical files managed by the database.

The key to understanding how Oracle stores data is to separate: logical placement (where an object belongs) from physical storage (where bytes live on disk).

How the pieces fit together

Table (logical object): A table is a schema object that stores rows and columns. When you insert rows, Oracle stores them in data blocks that belong to that table’s segments.
Tablespace (logical storage container): A tablespace is a named allocation domain inside the database. It groups storage for segments (tables, indexes, materialized views, LOB segments, undo segments, etc.) and provides administrative control (capacity planning, encryption, backup strategy, IO placement).
Datafile (physical file): A datafile is an operating-system file (or ASM-managed file) that stores Oracle data blocks. A tablespace is made up of one or more datafiles. A datafile belongs to exactly one tablespace (never multiple).

In short: tables (segments) live in tablespaces, and tablespaces are stored in datafiles.

What Oracle actually allocates

Oracle does not allocate space “row by row.” It allocates storage in progressively larger units:

Data blocks – the smallest IO unit used by Oracle (commonly 8 KB, but configurable).
Extents – a set of contiguous blocks allocated to a segment.
Segments – storage for a schema object, such as a table segment, index segment, or LOB segment. A table is stored in one or more segments (for example, the base table segment plus optional LOB segments).
Tablespaces – the container from which extents are allocated.
Datafiles – the physical files that hold the blocks for the tablespace.

Modern Oracle best practice is to use locally managed tablespaces with automatic segment space management (ASSM). In these tablespaces, Oracle manages free space efficiently without legacy parameters such as PCTINCREASE.

Where tablespace placement happens in SQL

When you create a table, you can optionally specify a tablespace. If you do not, Oracle uses the user’s default tablespace.

CREATE TABLE person (
  person_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name  VARCHAR2(30) NOT NULL,
  last_name   VARCHAR2(30) NOT NULL
)
TABLESPACE user1;

The TABLESPACE clause is a logical directive. It tells Oracle which tablespace should own the table segment. Oracle then allocates extents from that tablespace as the segment grows.

Reading the diagrams

The following images illustrate a simple (but accurate) idea: a single tablespace can span multiple datafiles, and Oracle will allocate extents across available free space in those files. The object’s logical home is the tablespace; the object’s physical blocks are distributed wherever free space exists in the tablespace’s datafiles.

This illustration shows a database in which two physical files (FILE1 and FILE2) are assigned to one tablespace (USER1). — 1) One tablespace (USER1) is backed by two datafiles (FILE1 and FILE2). A tablespace can contain many segments, and its datafiles provide the physical storage for all of them.

Here, a new table(PERSON) is created with 50K of initial space allocated. It is assigned to the USER1 tablespace. So, 50K is allocated inside USER1, and in turn 50K is allocated inside USER1, and in turn, 50K is allocated in FILE1. — 2) A table (PERSON) is created in tablespace USER1. Oracle allocates the table segment’s first extent from free space within USER1. Physically, that extent is placed in one of USER1’s datafiles (FILE1 in this illustration).

Next, another new table (ORDERS) is created with 100K of initial space. The table is also assigned to the USER1 tablespace. 100K is allocated inside the USER1 tablespace for this table. In turn, 100K is allocated in the physical files. First, 50K is allocated from FILE1 and then 50K is allocated from FILE2. — 3) A second table (ORDERS) is created in USER1. Oracle allocates extents for the new table from free space across USER1’s datafiles. If one file does not have enough contiguous free space, allocation can span multiple files (FILE1 then FILE2).

Key points for Oracle 23ai

Datafile-to-tablespace is 1:1: each datafile is associated with exactly one tablespace. (A tablespace-to-datafile relationship is 1:N.)
Objects allocate extents, not “kilobytes”: diagrams often speak in KB for clarity, but Oracle allocates in extents (groups of blocks). Extent sizing is managed automatically in locally managed tablespaces.
ASSM is the default expectation: segment free space is tracked and managed automatically; you generally do not tune legacy free-list mechanisms.
Physical storage may be ASM or cloud-managed: whether the datafile is an OS file, ASM file, or cloud volume, the logical model (table → tablespace → datafile) remains the same.
Security and governance are tablespace-driven: features such as tablespace encryption, backup strategy, and IO placement are commonly applied at the tablespace level.

In the next lesson, you will apply this model by specifying storage-related choices when creating tables and by understanding how Oracle grows segments as rows are inserted.