Tutorial: Tablespace Management in Oracle Database

What is a Tablespace?

A tablespace is the logical storage unit in Oracle Database. It logically groups data segments (tables, indexes, etc.) and is physically composed of one or more datafiles on the operating system.

Every Oracle database has at least the following default tablespaces:

Tablespace Description
SYSTEM Data dictionary and Oracle internal objects
SYSAUX Auxiliary components (AWR, statspack, etc.)
TEMP Temporary operations (sorts, joins, group by)
UNDOTBS1 Undo data storage (rollback)
USERS Default tablespace for user objects

Listing Tablespaces

List all tablespaces in the database

SELECT
    tablespace_name,
    status,
    contents,
    logging,
    extent_management,
    segment_space_management
FROM dba_tablespaces
ORDER BY tablespace_name;

List datafiles associated with each tablespace

SELECT
    tablespace_name,
    file_name,
    bytes / 1024 / 1024        AS size_mb,
    autoextensible,
    maxbytes / 1024 / 1024     AS max_size_mb
FROM dba_data_files
ORDER BY tablespace_name, file_name;

List tempfiles (temporary tablespaces)

SELECT
    tablespace_name,
    file_name,
    bytes / 1024 / 1024    AS size_mb,
    autoextensible
FROM dba_temp_files
ORDER BY tablespace_name;

Checking Usage and Free Space

View free space per tablespace

SELECT
    df.tablespace_name                                        AS tablespace,
    ROUND(df.total_mb, 2)                                     AS total_mb,
    ROUND(fs.free_mb, 2)                                      AS free_mb,
    ROUND(df.total_mb - fs.free_mb, 2)                        AS used_mb,
    ROUND((df.total_mb - fs.free_mb) / df.total_mb * 100, 2) AS pct_used
FROM
    (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb
     FROM dba_data_files
     GROUP BY tablespace_name) df
JOIN
    (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_mb
     FROM dba_free_space
     GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;

Warning: Tablespaces exceeding 85% usage require immediate attention to avoid errors such as ORA-01653: unable to extend table.

View full details including autoextend

SELECT
    d.tablespace_name,
    d.file_name,
    ROUND(d.bytes / 1024 / 1024, 2)        AS current_size_mb,
    d.autoextensible,
    ROUND(d.maxbytes / 1024 / 1024, 2)     AS max_size_mb,
    ROUND(f.bytes / 1024 / 1024, 2)        AS free_space_mb
FROM dba_data_files d
LEFT JOIN dba_free_space f ON d.file_id = f.file_id
ORDER BY d.tablespace_name;

Increasing Tablespace Size

There are two main ways to increase a tablespace:

  1. Resize an existing datafile (resize)
  2. Add a new datafile to the tablespace

Option 1: Resize an existing datafile

Use this command when you want to increase the size of an already existing file:

-- Syntax
ALTER DATABASE DATAFILE '<full_file_path>' RESIZE <new_size>;

-- Example: increase to 2 GB
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 2048M;

Verify the correct datafile path using the dba_data_files query before executing.

Option 2: Increase a temporary tablespace

For TEMP type tablespaces, use TEMPFILE instead of DATAFILE:

ALTER DATABASE TEMPFILE '/u01/oradata/ORCL/temp01.dbf' RESIZE 1024M;

Enabling Autoextend

Autoextend allows Oracle to automatically expand the datafile when space runs out, up to a defined limit.

Enable autoextend on an existing datafile

-- Syntax
ALTER DATABASE DATAFILE '<file_path>'
    AUTOEXTEND ON
    NEXT <increment>
    MAXSIZE <maximum_size>;

-- Example: 512 MB increment up to 10 GB maximum
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf'
    AUTOEXTEND ON
    NEXT 512M
    MAXSIZE 10240M;

Disable autoextend

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND OFF;

Autoextend Parameters

Parameter Description
NEXT Size of each automatic extension increment
MAXSIZE Maximum size the file can reach
UNLIMITED Allows growth up to the file system limit (use with caution!)

Adding New Datafiles

When it is not possible (or desirable) to increase existing files, the best alternative is to add a new datafile to the tablespace.

Add a datafile to an existing tablespace

-- Syntax
ALTER TABLESPACE <tablespace_name>
    ADD DATAFILE '<new_file_path>'
    SIZE <size>
    AUTOEXTEND ON
    NEXT <increment>
    MAXSIZE <maximum>;

-- Example: add a new 1 GB datafile to the USERS tablespace
ALTER TABLESPACE USERS
    ADD DATAFILE '/u01/oradata/ORCL/users02.dbf'
    SIZE 1024M
    AUTOEXTEND ON
    NEXT 256M
    MAXSIZE 4096M;

Add a tempfile to a temporary tablespace

ALTER TABLESPACE TEMP
    ADD TEMPFILE '/u01/oradata/ORCL/temp02.dbf'
    SIZE 512M
    AUTOEXTEND ON
    NEXT 128M
    MAXSIZE 2048M;

Best Practices

Monitoring

  • Set up alerts for tablespaces that exceed 80% usage.
  • Use the dba_tablespace_usage_metrics view for a consolidated overview on 11g+ databases:
SELECT
    tablespace_name,
    ROUND(used_space * 8192 / 1024 / 1024, 2)      AS used_mb,
    ROUND(tablespace_size * 8192 / 1024 / 1024, 2)  AS total_mb,
    ROUND(used_percent, 2)                           AS pct_used
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

Datafile Organization

  • Distribute datafiles across different disks to improve I/O performance.
  • Avoid using MAXSIZE UNLIMITED; always define a safe limit.
  • Prefer multiple smaller datafiles over a single large file.
  • Name files consistently: <tablespace>NN.dbf (e.g., users01.dbf, users02.dbf).

Common Errors and Solutions

Oracle Error Likely Cause Solution
ORA-01653 Tablespace out of free space Increase datafile or add a new one
ORA-01652 TEMP tablespace out of space Increase tempfile
ORA-19502 Error writing to datafile Check disk space on the OS
ORA-01144 File size exceeds the limit Use multiple smaller datafiles

Required permissions: To execute the commands in this tutorial, the user must have the DBA privilege or, at minimum, ALTER TABLESPACE and access to the views DBA_DATA_FILES, DBA_FREE_SPACE, and DBA_TABLESPACES.