How to Check Tablespace Usage in Oracle Database (SQL + Easy Report)

Do you want a quick way to check how much space is left in your Oracle tablespaces? This guide shows simple SQL queries to view tablespace size, used space, free space, and percentage used so you can avoid “tablespace full” issues.

1) Check tablespace usage (basic)

Use this query to see total, used, free, and % used:

SELECT
df.tablespace_name,
ROUND(df.total_mb) AS total_mb,
ROUND(df.total_mb – fs.free_mb) AS used_mb,
ROUND(fs.free_mb) AS free_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;

2) Include TEMP tablespace usage

TEMP uses a different view. Use this query:

SELECT
  tablespace_name,
  ROUND(total_blocks * block_size / 1024 / 1024) AS total_mb,
  ROUND(used_blocks * block_size / 1024 / 1024) AS used_mb,
  ROUND((total_blocks - used_blocks) * block_size / 1024 / 1024) AS free_mb,
  ROUND((used_blocks / total_blocks) * 100, 2) AS pct_used
FROM v$temp_space_header
ORDER BY pct_used DESC;

3) Find the top tablespaces (alert style)

Want to find only tablespaces above 80%?

SELECT *
FROM (
  SELECT
    df.tablespace_name,
    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
)
WHERE pct_used >= 80
ORDER BY pct_used DESC;

4) What to do if a tablespace is getting full?

Ask yourself: do you want to add space or clean space?

Option A: Add a new datafile

ALTER TABLESPACE USERS
ADD DATAFILE '/u02/oradata/DB01/users02.dbf' SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE 10G;

Option B: Resize an existing datafile (carefully)

ALTER DATABASE DATAFILE '/u02/oradata/DB01/users01.dbf' RESIZE 5G;