SQL query to check Tablespace Utilization in Oracle Database.

SET LINESIZE 150

SET PAGESIZE 9999

SET VERIFY OFF

COLUMN status FORMAT a9 HEADING 'Status'

COLUMN name FORMAT a25 HEADING 'Tablespace Name'

COLUMN ts_size FORMAT 999999.99 HEADING 'Tablespace Size'

COLUMN used FORMAT 999999.99 HEADING 'Used (in megabytes)'

COLUMN free FORMAT 999999.99 HEADING 'Free (in megabytes)'

COLUMN pct_used FORMAT 999.99 HEADING 'Pct. Used'

SELECT d.tablespace_name name,

       d.status status,

       NVL(a.bytes, 0)/1024/1024 ts_size,

       NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 used,

       NVL(f.bytes, 0)/1024/1024 free,

       NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used

  FROM sys.dba_tablespaces d,

       ( select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,

       ( select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f

 WHERE d.tablespace_name = a.tablespace_name(+)

   AND d.tablespace_name = f.tablespace_name(+)

ORDER BY pct_used desc;

Sample output: