Re: wrong output from Join

On 22 Jul 2005 09:04:05 -0700, "Jagjeet Singh" <jagjeet_malhi_at_hotmail.com> wrote:

>Hi,
>
> I need to calculate the sum of tablespace's size and its free size.
>
>Select a.tablespace_name,sum(a.bytes) total_mb,sum(b.bytes) Free_mb
>from dba_data_files a , dba_free_space b
>where a.tablespace_name = b.tablespace_name
>group by a.tablespace_name
>/
>
>This query is givien wrong output.
>
>Can anyone is explain it why is it giving wrong output.
>
>Thanks,
>Js

One record from dba_data_files will join to all corresponding records of dba_free_space, so to all corresponding free segments.

This will work
select used.tablespace_name, used.bytes, free.bytes from
(select tablespace_name, sum(bytes) bytes  from dba_data_files
 group by tablespace_name) used,
(select tablespace_name, sum(bytes) bytes  from dba_free_space
 group by tablespace_name
) free

--
Sybrand Bakker, Senior Oracle DBA