column "DB Name" format a8;
column "DB Space GB" format 999.99;
column "DB Free GB" format 999.99;
column "DB Used GB" format 999.99;
column "DB Used %" format 99.99;
SELECT c.NAME "DB Name",
a.totalspace "DB Space GB",
b.totalfree "DB Free GB",
a.totalspace -b.totalfree "DB Used GB",
(a.totalspace-b.totalfree)/a.totalspace*100 "DB Used %"
FROM
(SELECT SUM(blocks)*8192/1024/1024/1024 totalspace FROM dba_data_files
) a,
(SELECT SUM(blocks)*8192/1024/1024/1024 totalfree FROM dba_free_space
) b,
v$database c;
I joined v$database for displaying the database name in the result. If this statement can be integrated with a shell script, which will check all the databases that I administer in an execution. It's very convenient to monitor all databases at a time.
And the output will be like this:
DB Name DB Space GB DB Free GB DB Used GB DB Used %
-------- ----------- ---------- ---------- ---------
DBNAME 16.52 1.85 14.67 88.81