Extract Oracle Tablespace Information

As a DBA, you’ll regularly check what tablespaces exist, how much space is free/used, which files back them, and which segments consume space. Oracle exposes this via dictionary and dynamic performance views. For fleet-level monitoring and automation, prefer Enterprise Manager Cloud Control for dashboards, alerts, and jobs.

DBA_TABLESPACES lists one row per tablespace and key attributes (contents, extent/segment management, bigfile flag, status).

SELECT tablespace_name,
       contents,                 -- PERMANENT | TEMPORARY | UNDO
       status,                   -- ONLINE | OFFLINE | READ ONLY
       bigfile,                  -- YES for bigfile TS
       extent_management,        -- LOCAL (recommended)
       segment_space_management  -- AUTO (ASSM recommended)
FROM   dba_tablespaces
ORDER  BY tablespace_name;

Use DBA_DATA_FILES for permanent tablespaces and DBA_TEMP_FILES for TEMP. These show file names, sizes, and autoextend settings.

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

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

DBA_FREE_SPACE shows free extents; aggregate it by tablespace for a quick view. (For TEMP usage at runtime, see V$TEMP_SPACE_HEADER.)

-- Permanent tablespaces: free capacity snapshot
SELECT  df.tablespace_name,
        ROUND(SUM(df.bytes)/1024/1024) AS size_mb,
        ROUND(SUM(NVL(fs.bytes,0))/1024/1024) AS free_mb,
        ROUND( (SUM(df.bytes) - SUM(NVL(fs.bytes,0))) /1024/1024) AS used_mb,
        TO_CHAR(ROUND( ( (SUM(df.bytes)-SUM(NVL(fs.bytes,0))) / SUM(df.bytes) )*100, 1), '990D0') AS pct_used
FROM    dba_data_files df
LEFT JOIN (SELECT tablespace_name, SUM(bytes) bytes
           FROM   dba_free_space
           GROUP  BY tablespace_name) fs
ON      df.tablespace_name = fs.tablespace_name
GROUP BY df.tablespace_name
ORDER BY df.tablespace_name;

DBA_SEGMENTS breaks down usage by segment (table, index, LOB, etc.). Start with the heavy hitters.

SELECT owner, segment_name, segment_type, tablespace_name,
       ROUND(bytes/1024/1024) AS size_mb
FROM   dba_segments
WHERE  tablespace_name = UPPER(:target_tablespace)
ORDER  BY bytes DESC
FETCH  FIRST 50 ROWS ONLY;

V$TABLESPACE and V$TEMP_SPACE_HEADER help with online/offline state and TEMP usage during peak operations.

SELECT t.ts#, t.name AS tablespace_name, t.included_in_database_backup, ts.online$
FROM   v$tablespace ts
JOIN   sys.ts$ t ON ts.ts# = t.ts#
ORDER  BY t.name;

-- TEMP usage summary (approximate, runtime)
SELECT tablespace_name,
       SUM(bytes_used)/1024/1024 AS used_mb,
       SUM(bytes_free)/1024/1024 AS free_mb
FROM   v$temp_space_header
GROUP  BY tablespace_name;

Quotas limit per-user consumption within a tablespace. Combine DBA_TS_QUOTAS with segment usage to find top users.

SELECT username, tablespace_name, bytes/1024/1024 AS quota_mb, max_bytes/1024/1024 AS max_quota_mb
FROM   dba_ts_quotas
ORDER  BY tablespace_name, username;

-- Top space consumers by owner in a TS
SELECT owner,
       ROUND(SUM(bytes)/1024/1024) AS used_mb
FROM   dba_segments
WHERE  tablespace_name = UPPER(:target_tablespace)
GROUP  BY owner
ORDER  BY used_mb DESC;

• Scripting: Wrap the above queries into scheduled SQL*Plus scripts or SQLcl; export CSV for reporting.
• Enterprise Manager Cloud Control: Use out-of-the-box tablespace dashboards, threshold-based alerts, and corrective actions (resize/add file) for proactive operations.
• Change tracking: Log snapshots of capacity to a history table (e.g., daily) for forecasting.

• Mandatory TS present and online: SYSTEM, SYSAUX, appropriate UNDO, and TEMP.
• EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO (ASSM) enabled for user tablespaces.
• Autoextend policies set thoughtfully with MAXSIZE aligned to storage capacity and backup windows.

TABLESPACE_NAME  CONTENTS   STATUS  BIGFILE EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT
---------------  ---------  ------  ------- ----------------- ------------------------
SYSTEM           PERMANENT  ONLINE  NO      LOCAL             AUTO
SYSAUX           PERMANENT  ONLINE  NO      LOCAL             AUTO
UNDO_TBS         UNDO       ONLINE  NO      LOCAL             AUTO
TEMP             TEMPORARY  ONLINE  NO      LOCAL             AUTO
APP_TBS          PERMANENT  ONLINE  NO      LOCAL             AUTO

Click on the Quiz link below to test your knowledge of datafiles and tablespaces.
Tablespaces Datafiles - Quiz