Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: brain F*rt question

Re: brain F*rt question

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Wed, 19 Dec 2001 13:30:05 -0800
Message-ID: <F001.003E0ABA.20011219131608@fatcity.com>

Ron Rogers wrote:
>
> Stephane ,
> Thanks for the reply.
> I tried using a join function and I got the sum(data) I wanted but it listed the
>date twice, Once for each table select. In your reply you mentioned a percentage
>calculation problem.??? I think the sums are wrong because they get summed for each
>occurance of an enter in the table.
> What do you mean by an in-line view? I tried creating views to gather the data
>needed but have problems selecting a particular retnbr , saledate range, and grouping
>by saledate across two tables.
> Thanks,
> Ron
>
> Ron,
> Looks to me like the classical percentage computation problem. Sums
> are wrong because applied to the result of the join. Compute your sums
> in an in-line view in the FROM clause.
> --

Have you ever tried to list how full your tablespaces are ? In percentage ? This is what I meant. Basically to do this you need to sum up the blocks on dba_data_files (to see how much has been allocated) and on say dba_segments (to see how much is used). Double sum, and if you join and try to compute a percentage, you get wrong numbers. This is how it is properly done :

select x.tablespace_name, nvl(round(100 * sum(s.blocks) / x.blocks, 2), 0) PCT_FULL
from dba_segments s,

     (select tablespace_name, sum(blocks) blocks <-- This is an in-line view

      from dba_data_files
      group by tablespace_name) x

where x.tablespace_name = s.tablespace_name (+) group by x.tablespace_name, x.blocks

I may have the column names wrong (and possibly the view names :-)) but it's the idea. I think that in spirit your problem is very close.

-- 
Regards,

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

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
Received on Wed Dec 19 2001 - 15:30:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US