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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).