Query to find Tablespace Used & Free Space

set linesize 120

set pages 500

SELECT ts.tablespace_name, "File Count",

TRUNC("SIZE(MB)", 2) "Size(MB)",

TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",

TRUNC(fr."FREE(MB)", 2) "Free(MB)",

df."MAX_EXT" "Max Ext(MB)",

(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free"

FROM (SELECT tablespace_name,

SUM (bytes) / (1024 * 1024) "FREE(MB)"

FROM dba_free_space

GROUP BY tablespace_name) fr,

(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)

"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"

FROM dba_data_files

GROUP BY tablespace_name) df,

(SELECT tablespace_name

FROM dba_tablespaces) ts

WHERE fr.tablespace_name = df.tablespace_name (+)

AND fr.tablespace_name = ts.tablespace_name (+)

ORDER BY ts.tablespace_name;

Using the above query, we can find the following data:

Ø  Tablespace Name

Ø  File Count   à Number of datafiles in the respective tablespace

Ø  Size(MB)     à Total allocated space (in MB) for the respective tablespace

Ø  Used(MB)  à Used space (in MB) in the respective tablespace

Ø  Free(MB)    à Free space (in MB) in the respective tablespace

Ø  Max Ext(MB)à If “auto-extend” enabled for the respective tablespace, the value of this column will be the maximum extendable size. If its value is zero, then the respective tablespace is not auto-extensible