DBA_TABLESPACES - Query Oracle Tablespace Configuration

DBA_TABLESPACES

Overview

DBA_TABLESPACES is the central data dictionary view for tablespace configuration metadata. It contains one row per tablespace defined in the database and is the primary reference for understanding how each tablespace is structured — including its contents type (PERMANENT, TEMPORARY, UNDO), extent management policy (LOCAL or DICTIONARY), block size, segment space management method (AUTO or MANUAL), bigfile status, and encryption state. DBAs query it constantly when planning storage, troubleshooting space errors, or auditing database configuration.

View Type: Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_TABLESPACES or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
TABLESPACE_NAME	VARCHAR2(30)	Name of the tablespace
BLOCK_SIZE	NUMBER	Database block size for the tablespace in bytes
STATUS	VARCHAR2(9)	ONLINE, OFFLINE, or READ ONLY
CONTENTS	VARCHAR2(21)	PERMANENT, TEMPORARY, or UNDO
EXTENT_MANAGEMENT	VARCHAR2(10)	LOCAL (bitmap-managed) or DICTIONARY (data dictionary-managed, legacy)
ALLOCATION_TYPE	VARCHAR2(9)	SYSTEM (system-managed extent sizing), UNIFORM (fixed extent size), or USER (user-specified)
SEGMENT_SPACE_MANAGEMENT	VARCHAR2(6)	AUTO (ASSM using freelists bitmaps) or MANUAL (freelist-based)
BIGFILE	VARCHAR2(3)	YES if the tablespace is a bigfile tablespace (single very large datafile); NO otherwise
RETENTION	VARCHAR2(11)	For undo tablespaces: GUARANTEE, NOGUARANTEE, or NOT APPLY
ENCRYPTED	VARCHAR2(3)	YES if Transparent Data Encryption is enabled for the tablespace; NO otherwise

Essential Queries

All Tablespaces with Status and Configuration

List every tablespace with its type, management settings, and current status — the standard starting point for a storage audit:

SELECT
    t.tablespace_name,
    t.status,
    t.contents,
    t.extent_management,
    t.allocation_type,
    t.segment_space_management,
    t.block_size,
    t.bigfile,
    t.encrypted,
    t.retention
FROM
    dba_tablespaces t
ORDER BY
    t.contents,
    t.tablespace_name;

Tablespace Type Breakdown

Summarise tablespace counts by contents type and extent management method, useful for database configuration reviews:

SELECT
    contents,
    extent_management,
    segment_space_management,
    COUNT(*)                                    AS tablespace_count,
    SUM(CASE WHEN status = 'ONLINE'    THEN 1 ELSE 0 END) AS online_count,
    SUM(CASE WHEN status = 'READ ONLY' THEN 1 ELSE 0 END) AS readonly_count,
    SUM(CASE WHEN status = 'OFFLINE'   THEN 1 ELSE 0 END) AS offline_count,
    SUM(CASE WHEN encrypted = 'YES'    THEN 1 ELSE 0 END) AS encrypted_count,
    SUM(CASE WHEN bigfile   = 'YES'    THEN 1 ELSE 0 END) AS bigfile_count
FROM
    dba_tablespaces
GROUP BY
    contents,
    extent_management,
    segment_space_management
ORDER BY
    contents,
    extent_management;

Autoextend Settings via DBA_DATA_FILES Join

Combine DBA_TABLESPACES with DBA_DATA_FILES to show each tablespace’s total allocated size and whether any datafile can autoextend:

SELECT
    t.tablespace_name,
    t.contents,
    t.status,
    t.bigfile,
    COUNT(d.file_id)                                AS file_count,
    ROUND(SUM(d.bytes)         / 1073741824, 2)     AS allocated_gb,
    ROUND(SUM(d.maxbytes)      / 1073741824, 2)     AS max_possible_gb,
    SUM(CASE WHEN d.autoextensible = 'YES' THEN 1 ELSE 0 END) AS autoext_files,
    SUM(CASE WHEN d.autoextensible = 'NO'  THEN 1 ELSE 0 END) AS fixed_files
FROM
    dba_tablespaces t
    LEFT JOIN dba_data_files d ON d.tablespace_name = t.tablespace_name
WHERE
    t.contents != 'TEMPORARY'
GROUP BY
    t.tablespace_name,
    t.contents,
    t.status,
    t.bigfile
ORDER BY
    allocated_gb DESC NULLS LAST;

Encryption Status Audit

Identify tablespaces that are not encrypted, important for TDE compliance checks on databases holding sensitive data:

SELECT
    t.tablespace_name,
    t.contents,
    t.status,
    t.encrypted,
    t.bigfile,
    t.segment_space_management,
    ROUND(NVL(SUM(d.bytes), 0) / 1073741824, 2)    AS allocated_gb
FROM
    dba_tablespaces t
    LEFT JOIN dba_data_files d ON d.tablespace_name = t.tablespace_name
WHERE
    t.contents    = 'PERMANENT'
    AND t.status != 'OFFLINE'
GROUP BY
    t.tablespace_name,
    t.contents,
    t.status,
    t.encrypted,
    t.bigfile,
    t.segment_space_management
ORDER BY
    t.encrypted,
    allocated_gb DESC NULLS LAST;

Legacy Dictionary-Managed Tablespaces

Find any remaining DICTIONARY extent-managed tablespaces that should be migrated to locally managed:

SELECT
    t.tablespace_name,
    t.contents,
    t.status,
    t.extent_management,
    t.allocation_type,
    t.segment_space_management,
    ROUND(NVL(SUM(d.bytes), 0) / 1073741824, 2)    AS allocated_gb
FROM
    dba_tablespaces t
    LEFT JOIN dba_data_files d ON d.tablespace_name = t.tablespace_name
WHERE
    t.extent_management = 'DICTIONARY'
GROUP BY
    t.tablespace_name,
    t.contents,
    t.status,
    t.extent_management,
    t.allocation_type,
    t.segment_space_management
ORDER BY
    t.tablespace_name;

Common Use Cases

• Storage audits — Quickly review every tablespace’s type, extent management, ASSM setting, and encryption state across the entire database in a single query
• Capacity planning — Join with DBA_DATA_FILES and DBA_FREE_SPACE to establish current allocation and headroom for each tablespace
• TDE compliance checks — Filter ENCRYPTED = ‘NO’ on PERMANENT tablespaces to identify gaps in a Transparent Data Encryption rollout
• Configuration baseline — Confirm all production tablespaces use LOCAL extent management and SEGMENT_SPACE_MANAGEMENT = AUTO before go-live
• Read-only tablespace auditing — Find tablespaces in READ ONLY status that may need to be brought online before maintenance operations
• Undo tablespace tuning — Check the RETENTION column to confirm whether undo guarantee is enabled when troubleshooting ORA-01555 errors

Related Views

• DBA_DATA_FILES — Physical datafile details including sizes, autoextend limits, and online status for each tablespace
• DBA_FREE_SPACE — Available free space within each tablespace broken down by contiguous free chunk
• DBA_SEGMENTS — All segments allocated within tablespaces, showing actual space consumed by objects
• DBA_TEMP_FILES — Tempfile details for temporary tablespaces (not included in DBA_DATA_FILES)
• V$SORT_USAGE — Current temporary segment usage per session, complementing DBA_TEMP_FILES metrics
• DBA_TABLESPACE_USAGE_METRICS — High-level used/free summary view available from Oracle 11g, built on top of DBA_TABLESPACES

Version Notes

• Oracle 8i: Locally managed tablespaces (EXTENT_MANAGEMENT = LOCAL) introduced; DICTIONARY-managed tablespaces became the legacy path
• Oracle 9i: SEGMENT_SPACE_MANAGEMENT column added with the introduction of Automatic Segment Space Management (ASSM)
• Oracle 10g: BIGFILE column added; bigfile tablespaces with single very large datafiles introduced
• Oracle 11g: RETENTION column added for undo tablespaces; DBA_TABLESPACE_USAGE_METRICS view introduced as a convenience layer
• Oracle 12c (Multitenant): ENCRYPTED column added; TDE tablespace encryption became the recommended approach; PDB-local tablespaces visible only from within the PDB
• Oracle 19c: No structural changes to DBA_TABLESPACES; TDE by default configuration options expanded
• Oracle 23ai: Unified auditing enhancements affect tablespace-level encryption audit trails; no column additions to this view