Re: SQL Query

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
> bbellow_at_chi.navtech.com 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: 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

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).