A maxed-out tablespace won't be listed in dba_free_space. Use an outer join
and order by ddf.tablespace_name.
"Steve" <no_at_anon> wrote in message
news:1017160356.28045.0.nnrp-07.c2d92da9_at_news.demon.co.uk...
> 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 - 11:52:01 CST
