Skip to content

How to Check Database Overall Space Usage

  • by
Do you know how large is your database? And how much space has been used? Here is an example to check it out.

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

Leave a Reply

Your email address will not be published. Required fields are marked *