Database Blocks, Extents, Segments, Tablespaces, Datafiles, and Space Allocation

Learning outcomes

Chapter 02 showed the running Oracle instance. Chapter 03 follows a ServiceHub row downward until it reaches persistent storage. A common mistake is to collapse every layer into “the datafile”: a table is not a file, an extent is not an operating-system extent, and free bytes inside a tablespace are not automatically free bytes on the host filesystem. This lesson builds the hierarchy precisely enough to diagnose capacity, placement, and recovery problems later.

1. The storage hierarchy is a chain of ownership and allocation

A table such as SERVICEHUB_OWNER.WORK_ORDERS is a logical schema object. The object consumes storage through a segment. A segment is made from one or more extents; each extent is a set of logically contiguous Oracle data blocks. A segment belongs to one tablespace and cannot span tablespaces, but different extents of that segment can reside in different datafiles of the same smallfile tablespace.

A tablespace is a logical database storage container. A permanent tablespace is backed by one or more physical datafiles; a temporary tablespace is backed by tempfiles. A datafile belongs to one tablespace. Below the datafile, the operating system, filesystem, logical volume, cloud block device, or Oracle Automatic Storage Management (ASM) layer maps file I/O to physical storage. Oracle data blocks and operating-system blocks are therefore related by I/O, but they are not the same unit.

2. Observe the current tablespace and file map before creating anything

Start by discovering the actual Free lab. Oracle AI Database 26ai databases created from current DBCA templates default to bigfile tablespaces, including SYSTEM, SYSAUX, and USER, but databases upgraded from older releases retain their tablespace type. That means a screenshot from another installation is not evidence for your environment.

SHOW CON_NAMESELECT tablespace_name, contents, status, block_size,       extent_management, allocation_type,       segment_space_management, bigfileFROM   dba_tablespacesORDER  BY tablespace_name;SELECT file_id, tablespace_name, file_name,       ROUND(bytes/1024/1024,1) AS size_mb,       autoextensible,       ROUND(maxbytes/1024/1024,1) AS max_mb,       increment_byFROM   dba_data_filesORDER  BY tablespace_name, file_id;

Inside a PDB, these views describe the current container’s visible files/tablespaces. A CDB root has a different scope. Record the container with the evidence so a later operator does not mistake a PDB-local file inventory for the entire CDB.

3. Find the segment behind a ServiceHub table

Oracle allocates a segment when the object needs segment storage. Deferred segment creation can mean an empty table has no allocated segment yet, so “table exists” does not always imply “segment exists.” The established ServiceHub tables contain rows, so they should have allocated table segments.

SELECT owner, segment_name, segment_type, tablespace_name,       header_file, header_block,       ROUND(bytes/1024/1024,2) AS allocated_mb,       blocks, extentsFROM   dba_segmentsWHERE  owner = 'SERVICEHUB_OWNER'ORDER  BY segment_type, segment_name;

DBA_SEGMENTS.BYTES is allocated segment space. It is not “business data size,” and it is not the size of the datafile. Blocks can contain free space, block metadata, row overhead, migrated/chained rows, or reusable space. Later lessons will distinguish logical row volume from segment allocation and optimizer statistics.

4. Extents connect a segment to one or more datafiles

When a segment needs more room, Oracle allocates another extent according to the tablespace’s locally managed allocation policy. An extent is contained within one datafile. In a smallfile tablespace, later extents for the same segment may land in another datafile; in a bigfile tablespace there is only one datafile by definition.

SELECT owner, segment_name, segment_type,       extent_id, file_id, block_id, blocks,       ROUND(bytes/1024,1) AS extent_kbFROM   dba_extentsWHERE  owner = 'SERVICEHUB_OWNER'AND    segment_name = 'WORK_ORDERS'ORDER  BY extent_id;SELECT file_id, tablespace_name, file_nameFROM   dba_data_filesWHERE  file_id IN (  SELECT DISTINCT file_id  FROM   dba_extents  WHERE  owner = 'SERVICEHUB_OWNER'  AND    segment_name = 'WORK_ORDERS')ORDER  BY file_id;

The BLOCK_ID is an Oracle block address within the file, not an operating-system sector number. Oracle controls database-block allocation; the filesystem or ASM separately controls how file blocks map to storage devices.

5. Allocated, free-inside-the-tablespace, and free-on-disk are three measurements

Administrators often say “we have 20 GB free” without naming the layer. DBA_FREE_SPACE reports free extents available for allocation inside permanent tablespaces. DBA_DATA_FILES.BYTES reports file size known to Oracle. The host’s filesystem free space is an operating-system fact. Autoextend can consume host space later, so a tablespace can look comfortable while the filesystem is close to exhaustion.

WITH df AS (  SELECT tablespace_name, SUM(bytes) bytes  FROM   dba_data_files  GROUP  BY tablespace_name), fs AS (  SELECT tablespace_name, SUM(bytes) bytes  FROM   dba_free_space  GROUP  BY tablespace_name)SELECT df.tablespace_name,       ROUND(df.bytes/1024/1024,1) AS file_mb,       ROUND(NVL(fs.bytes,0)/1024/1024,1) AS free_extent_mb,       ROUND((df.bytes-NVL(fs.bytes,0))/1024/1024,1) AS allocated_or_not_free_mbFROM   dfLEFT JOIN fs ON fs.tablespace_name = df.tablespace_nameORDER  BY df.tablespace_name;

This is a capacity view, not an exact “user rows consume X MB” report. SYSTEM metadata, segment headers, extents, and other allocations contribute. It also says nothing about filesystem snapshots, thin provisioning, ASM usable space, or storage-array capacity.

6. Deliberately wrong approach: treat a table as a datafile

An operator sees WORK_ORDERS in the USERS tablespace and says “its file is users01.dbf, so moving or deleting that file only affects this table.” That inference is unsafe. A datafile can contain extents from many segments, and a tablespace can contain many unrelated objects. Removing or corrupting a datafile is a database recovery event, not object cleanup.

The repair is to move downward through metadata: object → segment → extents → file IDs → datafiles. Only after identifying every dependent object and recovery implication should a file-level action even be considered. This chapter does not perform datafile removal.

SELECT e.file_id,       COUNT(DISTINCT e.owner || '.' || e.segment_name) AS distinct_segments,       ROUND(SUM(e.bytes)/1024/1024,1) AS extent_mbFROM   dba_extents eWHERE  e.tablespace_name = (  SELECT tablespace_name  FROM   dba_segments  WHERE  owner='SERVICEHUB_OWNER'  AND    segment_name='WORK_ORDERS'  FETCH FIRST 1 ROW ONLY)GROUP  BY e.file_idORDER  BY e.file_id;

Even this query should be interpreted carefully: segment names are not globally unique across owners and partition/subpartition metadata adds more identity dimensions. Its purpose is to break the “one table = one file” mental model.

7. Hands-on lab: create a disposable segment and watch extents appear

Connect as SERVICEHUB_OWNER to FREEPDB1. Create a probe table in the owner’s current default tablespace rather than hard-coding a datafile path. The payload is deliberately modest so it stays well within the Free 12-GB user-data limit.

CREATE TABLE servicehub_storage_probe ASSELECT LEVEL AS probe_id,       RPAD('x', 180, 'x') AS payloadFROM   dualCONNECT BY LEVEL <= 20000;SELECT segment_name, segment_type, tablespace_name,       ROUND(bytes/1024/1024,2) AS allocated_mb,       blocks, extentsFROM   user_segmentsWHERE  segment_name = 'SERVICEHUB_STORAGE_PROBE';SELECT extent_id, file_id, block_id, blocks,       ROUND(bytes/1024,1) AS extent_kbFROM   user_extentsWHERE  segment_name = 'SERVICEHUB_STORAGE_PROBE'ORDER  BY extent_id;DROP TABLE servicehub_storage_probe PURGE;

Verification: capture the segment’s tablespace, extent count, file IDs, and allocated bytes before dropping it. After the drop, the object’s segment/extents disappear and their space becomes reusable inside the tablespace; the physical datafile does not automatically become smaller. Lesson 3 explains that reclamation boundary.

8. Production judgment

Storage decisions should be made at the correct layer. Tablespace design controls logical placement, allocation policy, and administration. Datafile design affects file count, growth, backup/recovery work, and storage integration. The underlying filesystem/ASM/cloud volume controls physical capacity and redundancy. Monitoring must correlate all three layers, especially when autoextend or thin provisioning can defer a failure until the next growth event.

For production evidence, record the exact CDB/PDB scope, datafile/tablespace type, Oracle Managed Files usage, autoextend/maxsize, filesystem or ASM capacity, backup/recovery implications, and the current Oracle RU. Do not use hidden parameters or “one file per object” folklore as a substitute for measured capacity and recovery design.

9. Summary and next step

You can now follow a ServiceHub row from object semantics to segment, extents, Oracle blocks, tablespace, datafile, and lower storage. You also know that allocated segment bytes, free extents, file size, and host free space are different measurements. Lesson 2 separates permanent, temporary, and undo storage and then evaluates bigfile versus smallfile tablespaces without treating file size as a performance shortcut.

Authoritative references

• Oracle AI Database Concepts — Logical Storage Structures — blocks, extents, segments, tablespaces, and logical/physical relationships
• Oracle AI Database Administrator’s Guide — Managing Tablespaces — tablespace/datafile administration and locally managed allocation
• Oracle AI Database Reference — DBA_SEGMENTS — segment allocation metadata
• Oracle AI Database Reference — DBA_EXTENTS — extent placement metadata
• Oracle AI Database Reference — DBA_DATA_FILES — permanent datafile size and autoextend metadata