Tablespaces & Data Files

When you create a table in Oracle, you do not specify a file path. You specify a tablespace — a named, logical storage container. Oracle handles where on disk the data physically lands. This separation between logical and physical storage is one of Oracle's most important architectural features. It lets DBAs add storage, move data, and manage growth without changing a single line of application SQL. This lesson explains how tablespaces and data files work, which ones Oracle creates by default, and how to create and manage your own.

What Is a Tablespace?

A tablespace is a logical storage unit that groups related database objects — tables, indexes, sequences, and other schema objects. Every object in Oracle belongs to exactly one tablespace. A tablespace is made up of one or more physical data files stored on disk. The relationship is:

• Schema objects (tables, indexes) → belong to a tablespace → stored in one or more data files

This three-level separation means you can add a new data file to grow a tablespace without touching any application code. You can move a tablespace to a faster disk by adding new data files and dropping old ones. You can restrict different applications to different tablespaces for easier management and monitoring.

-- dba_tablespaces lists every tablespace in the database
-- Requires DBA privilege — regular users should query user_tablespaces instead
-- contents: PERMANENT stores tables/indexes, TEMPORARY is for sorts, UNDO is for rollback
-- logging: NOLOGGING is used by TEMP — its contents are never written to the redo log
SELECT tablespace_name,
       status,
       contents,
       logging
FROM   dba_tablespaces
ORDER BY tablespace_name;

-- dba_data_files shows the physical .dbf files that back each tablespace
-- A tablespace can span multiple data files — each appears as a separate row here
-- bytes is stored in bytes by Oracle — divide by 1024 twice to convert to MB
-- autoextensible: YES means Oracle grows this file automatically when full
SELECT file_name,
       tablespace_name,
       ROUND(bytes    / 1024 / 1024, 2)  AS size_mb,
       autoextensible,
       ROUND(maxbytes / 1024 / 1024, 2)  AS max_size_mb
FROM   dba_data_files
ORDER BY tablespace_name;

• dba_tablespaces and dba_data_files are Oracle data dictionary views — they require DBA privileges; if you are connected as a regular user, use user_tablespaces instead
• AUTOEXTENSIBLE = YES means Oracle will automatically grow the data file when it runs out of space, up to the MAX_SIZE_MB limit — this prevents the "tablespace full" error in development but should be configured carefully in production where disk capacity must be managed
• The TEMP tablespace is used for sorting, hashing, and other temporary operations that exceed PGA memory — it is not used for permanent storage and its contents are never written to the redo log

Oracle's Default Tablespaces

Oracle XE creates five tablespaces automatically during installation. Each has a specific purpose and should not be used for your own application data.

Creating a Tablespace

In a real project you create a dedicated tablespace for your application data rather than using the shared USERS tablespace. This makes storage management, monitoring, and backup easier. The CREATE TABLESPACE statement defines the tablespace name, its initial data file, and how it should grow.

-- Create a dedicated tablespace for DataplexaStore table data
-- SIZE allocates disk immediately; NEXT is the growth increment; MAXSIZE is the ceiling
CREATE TABLESPACE dataplexa_store_ts
DATAFILE '/opt/oracle/oradata/XE/dataplexa_store01.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 50M
MAXSIZE 2G
LOGGING
ONLINE;

-- Separate tablespace for indexes — allows table scans and index lookups to run
-- on different physical disks, reducing I/O contention
-- Indexes typically need less initial space than table data
CREATE TABLESPACE dataplexa_store_idx
DATAFILE '/opt/oracle/oradata/XE/dataplexa_store_idx01.dbf'
SIZE 50M
AUTOEXTEND ON
NEXT 25M
MAXSIZE 1G
LOGGING
ONLINE;

-- Dedicated tablespace for DataplexaHR — each schema gets its own for clean separation
CREATE TABLESPACE dataplexa_hr_ts
DATAFILE '/opt/oracle/oradata/XE/dataplexa_hr01.dbf'
SIZE 100M
AUTOEXTEND ON
NEXT 50M
MAXSIZE 2G
LOGGING
ONLINE;

• SIZE 100M sets the initial data file size — Oracle allocates this space immediately on disk when the tablespace is created
• AUTOEXTEND ON NEXT 50M MAXSIZE 2G means the file grows by 50 MB each time it fills, up to a maximum of 2 GB — without AUTOEXTEND, a full tablespace causes all INSERT and UPDATE operations on its tables to fail with an ORA-01653 error
• Separating table data and index data into different tablespaces is a production best practice — it allows each to be placed on different physical disks for better I/O performance and makes tablespace-level backups more granular

Adding a Data File and Monitoring Space

As data grows, a tablespace can be expanded in two ways: by enabling autoextend on an existing data file, or by adding a new data file to the tablespace. Adding a new data file is preferred in production because it gives more control over exactly where the new storage is placed.

-- Add a second data file to an existing tablespace
-- Preferred over AUTOEXTEND alone — gives full control over file location and size
-- Second file starts larger as the application has grown
ALTER TABLESPACE dataplexa_store_ts
ADD DATAFILE '/opt/oracle/oradata/XE/dataplexa_store02.dbf'
SIZE 200M
AUTOEXTEND ON
NEXT 100M
MAXSIZE 4G;

-- Monitor free space across all permanent tablespaces
-- Joins dba_data_files for total allocated size and dba_free_space for unused extents
-- TEMP and UNDO manage space differently so they are excluded with the WHERE filter
-- Run this regularly — a tablespace approaching 90% used needs immediate attention
SELECT ts.tablespace_name,
       ROUND(SUM(df.bytes) / 1024 / 1024, 2)       AS total_mb,
       ROUND(SUM(fs.bytes) / 1024 / 1024, 2)       AS free_mb,
       ROUND(
           (1 - SUM(fs.bytes) / SUM(df.bytes)) * 100
       , 2)                                          AS used_pct
FROM   dba_tablespaces  ts
JOIN   dba_data_files   df ON ts.tablespace_name = df.tablespace_name
JOIN   dba_free_space   fs ON ts.tablespace_name = fs.tablespace_name
WHERE  ts.contents = 'PERMANENT'
GROUP BY ts.tablespace_name
ORDER BY used_pct DESC;

-- INCLUDING CONTENTS removes all objects stored inside the tablespace
-- AND DATAFILES deletes the .dbf file from the OS — without it the file becomes orphaned on disk
DROP TABLESPACE dataplexa_store_idx
INCLUDING CONTENTS AND DATAFILES;

• The free space query joins dba_data_files and dba_free_space — monitoring this regularly prevents surprises; a USED_PCT approaching 90% on a non-autoextend tablespace needs immediate attention
• INCLUDING CONTENTS AND DATAFILES in the DROP statement removes both the Oracle tablespace definition and the physical .dbf file from disk — omitting AND DATAFILES removes the tablespace from Oracle but leaves orphaned files on disk that waste storage
• You cannot drop a tablespace that contains objects currently in use — Oracle raises ORA-01549 if any session has an open transaction touching the tablespace's objects

Summary

Practice Questions

Quiz

Next up — Tables, Rows & Columns — Create your first Oracle tables, understand how data is physically organised into rows and columns, and start building the DataplexaStore and DataplexaHR schemas used throughout this course.