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
/
Received on Tue Dec 21 2004 - 08:02:30 CST
