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

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 21 Dec 2004 07:38:55 -0800
Message-ID: <1103643535.235158.303170@c13g2000cwb.googlegroups.com>

Here is a script I use to get the size info.

SELECT Tablespace "Tablespace Name",

ROUND(SUM(Total_space_MB)) "Total Space MB",
ROUND(SUM(Used_space_MB)) "Used Space MB",
ROUND(SUM(Free_space_MB)) "Free Space MB",
ROUND(((SUM(Used_space_MB) / SUM(Total_space_MB)) * 100), 2) "%

Used Space",
ROUND(((SUM(Free_space_MB) / SUM(Total_space_MB)) * 100), 2) "% Free Space"
FROM (SELECT a.tablespace_name Tablespace, SUM(a.bytes)/1024/1024 Total_space_MB,
0 Used_space_MB,
0 Free_space_MB
FROM dba_data_files a
GROUP BY a.tablespace_name
UNION
SELECT a.tablespace_name Tablespace,
0 Total_space_MB,
SUM(a.bytes)/1024/1024 Used_space_MB,
0 Free_space_MB
FROM dba_extents a
GROUP BY a.tablespace_name
UNION
SELECT a.tablespace_name Tablespace,
0 Total_space_MB,
0 Used_space_MB,
SUM(a.bytes)/1024/1024 Free_space_MB
FROM dba_free_space a
GROUP BY a.tablespace_name) combo
GROUP BY combo.tablespace
/

Regards
/Rauf
Received on Tue Dec 21 2004 - 09:38:55 CST