-
The following sql
select fs.tablespace_name,
(100*((df.bytes-sum(fs.bytes))/df.bytes)) "% Used"
from sys.dba_data_files df, sys.dba_free_space fs
where df.file_id(+) = fs.file_id
group by fs.tablespace_name, df.bytes;
gives me the output:
TABLESPACE_NAME % Used
------------------------------ ---------
BOSTOCK 68.798828
BOSTOCK 36.044922
BOSTOCK 95.453333
RBS 10.6
RBS 10.466667
RBS 5.6111111
SERVER_INFO 2.5
STAGE 50.25
but as you can see, it is giving me row per number of datafiles in each tablespace. It needs to be one row per tablespace (i.e. here I want the sum of Bostock and RBS instead of three row each)
Is it possible to modify the Group by statement so they will add up? Or is there other ways to do that, like using Sum? (which I also have no clues!)
Thanx in advance
Fiona
-
Use this select instead:
SELECT A.TABLESPACE_NAME TABLESPACE,
ROUND(A.BYTES/1048576,1) MB_TOTAL,
ROUND(NVL(B.BYTES/1048576, 0), 1) MB_USILISE,
ROUND(((NVL(B.BYTES,0) * 100) / A.BYTES), 1) "UTILISATION(%)"
FROM (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) A,
(select tablespace_name,
sum(bytes) bytes
from dba_extents
group by tablespace_name) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+)
ORDER BY 1;
Steeve Bisson
EMail: steeve_2@videotron.ca
-
Thanx for the help: it works fine!
Fiona
