I can now group the following by tablespace. How can I change the
script to display pct_used and display the total tablespace used based
on the following:
select tablespace_name,sum(free_mb),sum(size_mb),sum(maxsize_mb),max(ae)
ae,sum(free_pct) 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(+))
group by tablespace_name;
Received on Wed Dec 22 2004 - 05:15:11 CST
