Find Tablespace Size

col "Tablespace"          for a22

col "Used MB"             for 9,999,999.99

col "Free MB"             for 9,999,999.99

col "Total MB"            for 9,999,999.99

col Extend_free_space for 9,999,999.99

col "Pct Free"           for 999,999.99

col "Max Space"            for 999,999.99

set linesize 120

select df.tablespace_name "Tablespace",

totalusedspace "Used MB",

(df.totalspace - tu.totalusedspace) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace),2)

"Pct. Free"

from

(select tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

from dba_data_files

group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name

from dba_segments

group by tablespace_name) tu

where df.tablespace_name = tu.tablespace_name;

select df.tablespace_name "Tablespace",

nvl(totalusedspace,0) "Used MB",

(df.totalspace - nvl(tu.totalusedspace,0)) "Free MB",

df.totalspace "Total MB",

round(100 * ( (df.totalspace - nvl(tu.totalusedspace,0))/ df.totalspace),2)

"Pct. Free",

nvl(fs.free_space,0) extendable_free_space

, round(maxspace,2) maxspace

from

(select tablespace_name,

round(sum(bytes) / 1048576) TotalSpace

, sum(maxbytes)/1024/1024 maxspace

from dba_data_files

group by tablespace_name) df,

(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name

from dba_segments

group by tablespace_name) tu

,

(

select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space

       from dba_free_space

       group by tablespace_name

) fs

where df.tablespace_name = tu.tablespace_name(+)

AND df.tablespace_name = fs.tablespace_name(+)

ORDER BY "Pct. Free";