Re: sql question

How about:
 select
   a.tablespace_name,
   sum(a.bytes/(1024*1024)) "MB Alloc",    trunc(sum(b.bytes/(1024*1024)),3) "MB Free",    trunc((sum(b.bytes)/sum(a.bytes))*100,1) "% Free"  from
   (select distinct tablespace_name
    from dba_data_files) a,
   dba_free_space b
 where
   a.tablespace_name = b.tablespace_name  group by a.tablespace_name

Robert Proffitt

In article <80cels$8ra$1_at_nnrp1.deja.com>,   kshave_at_health.gov.mb.ca wrote:
> I want to determine how much space is currently allocated for each
> tablespace, as well as how much is free, and the percentage free. Here
> is my query ...
>
> select a.tablespace_name, sum(a.bytes/(1024*1024)) "MB Alloc",
> trunc(sum(b.bytes/(1024*1024)),3) "MB Free",
> trunc((sum(b.bytes)/sum(a.bytes))*100,1) "% Free"
> from dba_data_files a, dba_free_space b
> where a.tablespace_name = b.tablespace_name
> group by a.tablespace_name
>
> This, however, does not work properly because there are multiple
> entries of each tablespace in each of the two tables. The sum is being
> performed twice because the tablespace I'm testing with exists in each
> table twice. How can I get around this?
>
> --
> -Keith
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 10 1999 - 14:49:33 CST