tablespace free percentage
select tablespace_name, TO_CHAR(100*sum_free_blocks/sum_alloc_blocks, '999.99')
AS percent_free
from (select tablespace_name, sum(blocks) as sum_alloc_blocks
from dba_data_files
group by tablespace_name),
(select tablespace_name as fs_ts_name, max(blocks) AS max_blocks, count(blocks)
AS count_blocks, SUM(blocks) AS sum_free_blocks
FROM dba_free_space
GROUP BY tablespace_name)
where tablespace_name = fs_ts_name
order by percent_free desc;
Howevery, this query does not seem to give accurate results if the datafile is autoextensible. How can I modify this query so that it will take the autoextensible datafiles into account? Please advice. Thank you very much.