RE: SQL Query

But Stephane, I am aggregating by tablespace for both extents and for data_files. There is nothing here that is separating out anything by datafile. And, if I take away the GROUP BY, I lose the ability to aggregate at all, which is the point of this...

-----Original Message-----
Sent: Friday, November 14, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L

Bambi,

    Your second query is wrong because all extents in a tablespace don't necessarily belong to the same datafile. Try the query without the aggregate functions and the GROUP BY, and you'll understand your mistake.

HTH, SF

"Bellow, Bambi" wrote:
>
> Friends --
>
> Why would these two queries return different results?
>
> This query works.
>
> SQL> l
> 1 select
>

a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
> 2)*100 pct
> 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
> 3 from dba_extents group by tablespace_name) a,
> 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
> 5 from dba_data_files group by tablespace_name) b
> 6 where a.tablespace_name=b.tablespace_name
> 7* and a.tablespace_name='NAUAT'
> SQL> /
>
> TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT
> ------------------------------ -------------- ---------- ----------
> NAUAT 22924.25 11509 50
>
> This query does not work
>
> 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
> 2 sum(a.bytes)/(1024*1024) megs_used,
> 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct
> 4 from dba_extents a, dba_data_files b
> 5 where a.tablespace_name=b.tablespace_name
> 6 and a.tablespace_name='NAUAT'
> 7* group by a.tablespace_name,b.tablespace_name
> SQL> /
>
> TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT
> ------------------------------ -------------- ---------- ----------
> NAUAT 31773010.5 23018 .07
>
> Bambi.
> --

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bellow, Bambi
  INET: bbellow_at_chi.navtech.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).