Skip to content

How to Check Database Overall Space Usage

  • by
  • December 20, 2012April 1, 2018
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.