Space used by Tablespaces

Find the space used by Tablespaces:

set pages 10000 line 200

col tablespace_name format a20

col file_name format a50

col file_mb format 999999

col data_mb format 999999

col free_mb format 999999

col max_mb format 999999

select distinct files.tablespace_name, sum(ceil(files.mb)) file_mb, sum(ceil(files.mb - nvl(free.freemb,0))) data_mb,

sum(nvl(free.freemb,0)) free_mb, sum(files.maxmb) max_mb, round(sum(files.maxmb)-sum(ceil(files.mb - nvl(free.freemb,0)))) gth_cap,

round(sum(ceil(files.mb - nvl(free.freemb,0)))/sum(files.maxmb)*100) PCT_USED

from

(select file_id, file_name, tablespace_name, bytes/1024/1024 mb, autoextensible,

decode(autoextensible,'NO',bytes/1024/1024,decode(sign(maxbytes-bytes),-1,bytes/1024/1024,maxbytes/1024/1024)) maxmb

from dba_data_files) files,

(select file_id, sum(bytes)/1024/1024 freemb

from dba_free_space

group by file_id) free

where files.file_id = free.file_id (+)

group by files.tablespace_name

order by 1,2;