Nice try. Same result. 1 select a. tablespace_name, sum(b.user_bytes)/(1024*1024) allocated, 2 sum(a.bytes)/(1024*1024) used, round(sum(a.bytes)/sum(b.user_bytes),4) pct 3 from dba_extents a, dba_data_files b 4 where a.tablespace_name=b.tablespace_name 5 and a.tablespace_name='NAUAT' 6* group by a.tablespace_name,b.tablespace_name SQL> /
TABLESPACE_NAME ALLOCATED USED PCT ------------------------------ ---------- ---------- ---------- NAUAT 31772837.3 23018 .0007 Also, note that both the allocated *and* used values are wrong...they should be TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT ------------------------------ -------------- ---------- ---------- NAUAT 22924.25 11509 50 -----Original Message----- Sent: Friday, November 14, 2003 12:44 PM To: Multiple recipients of list ORACLE-L Bambi, I think that the query (2) will return the the same count as query (1) if you use the column user_bytes from the dba_data_files rather than the column bytes. If I sum the bytes from dba_extents for a tablespace_name xxx and sum the user_bytes from dba_data_files for tablespace_name xxx I get the same value. I my case the tablespace is a partitioned table with LMT . size 38M with 2 M uniform extents. I did not use 100 % of each extent there fore the different between bytes and user_bytes. Ron > [EMAIL PROTECTED] 11/14/2003 12:44:26 PM >>> 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Rogers 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).
