Re: How to sum dba_data_files by tablespace? Help!!

Frodo wrote:

> How can I in the following situation sum the datafiles by tablespace? Any ideas?
> select tablespace_name, sum(free_mb) from
> select a.file_id,a.tablespace_name,
> trunc(decode(a.autoextensible,'YES',a.maxsize-a.bytes+nvl(b.free,0),
> 'NO',nvl(b.free,0))/1024/1024) free_mb,
> trunc(a.bytes/1024/1024) size_mb,trunc(a.maxsize/1024/1024) maxsize_mb,
> a.autoextensible ae,
> trunc(decode(a.autoextensible,
> 'YES',(a.maxsize-a.bytes+nvl(b.free,0))/a.maxsize*100,
> 'NO',nvl(b.free,0)/a.maxsize*100)) free_pct
> from (select file_id,tablespace_name,autoextensible,bytes,
> decode(autoextensible,'YES',maxbytes,bytes) maxsize
> from dba_data_files
> group by file_id, tablespace_name, autoextensible, bytes,
> decode(autoextensible,'YES',maxbytes,bytes)) a,
> (select file_id, tablespace_name, sum(bytes) free
> from dba_free_space
> group by file_id, tablespace_name) b
> where a.file_id=b.file_id(+)
> and a.tablespace_name=b.tablespace_name(+)
> order by a.tablespace_name asc
> /

Use GROUP BY.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)


-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
   http://www.newsfeed.com       The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----