Wednesday, June 13, 2007

Datafile usage in tablespace

select substr(a.file_name,1,30),
substr(a.tablespace_name,1,20),
a.bytes allocated,
nvl(b.free,0) free,
a.bytes-nvl(b.free,0) used,
(nvl((a.bytes-nvl(b.free,0)),0)*100)/a.bytes usage_perc
from dba_data_files a,
( select file_id, sum(bytes) free
from dba_free_space
group by file_id ) b
where a.file_id = b.file_id (+)
AND a.tablespace_name='&tablespace'
/