Database Administration (MOSC)

MOSC Banner

tablespace free percentage

edited Dec 13, 2011 5:11AM in Database Administration (MOSC) 5 comments
I am currently using the following script to find out the free space percentage available in the database:

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center