To do this correctly you need a query similar to this:
SELECT dfs.tablespace_name tablespace_name,
ddf.total_size total_size,
ddf.total_size - dfs.total_free total_used,
dfs.total_free total_free,
(ddf.total_size - dfs.total_free) / ddf.total_size * 100 cap,
dfs.total_chunks total_chunks,
dfs.largest_chunk largest_chunk
FROM (SELECT a.tablespace_name,
SUM(a.bytes) / 1024 / 1024 total_free,
COUNT(a.bytes) total_chunks,
MAX(a.bytes) / 1024 / 1024 largest_chunk
FROM dba_free_space a
GROUP BY a.tablespace_name) dfs,
(SELECT b.tablespace_name,
SUM(b.bytes) / 1024 / 1024 total_size
FROM dba_data_files b
GROUP BY b.tablespace_name) ddf
WHERE dfs.tablespace_name = ddf.tablespace_name
ORDER BY dfs.tablespace_name;
Joining dba_data_files and dba_free_space then doing the sum aggregate as in
your query will give you incorrect results.
"Sunit Joshi" <sjoshi_at_ingr.com> wrote in message
news:8f8ffe67.0203260707.53018305_at_posting.google.com...
> Hello
> I can't seem to get correct values for some of the tablespace with this
query.
>
> select o.tablespace_name, round(sum(o.bytes/(1024 * 1024)),2) "Total(MB)",
> round(sum(u.bytes/(1024 *1024)),2) "FreeMB"
> from dba_data_files o, dba_free_space u
> where o.tablespace_name = u.tablespace_name
> group by o.tablespace_name;
>
> thanks
> Sunit
Received on Tue Mar 26 2002 - 10:33:38 CST
