DBAtlas · Database operations atlas

Tablespace free space query

OracleStorage

Command

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

What it does

Shows the total and free space and the usage percentage of each tablespace (based on currently allocated space, excluding autoextend potential).

When to use it

Space monitoring, finding which tablespace needs to be extended.

Caution

This query is based on the current datafile size; it can grow further with autoextend. For temp use DBA_TEMP_FREE_SPACE. Query only; no risk.

Open in DBAtlas →

Related