Administration

How to Check Oracle Database Size

At least two scenarios that you may need to know the database size.

  1. Server upgrade or migration
  2. You have to plan the size of storage and create reasonable tablespaces in advance.

  3. Prediction on space growth
  4. You have to log the overall space usage on weekly or monthly basis in order to predict the database size in the future.

Further reading: How Big a Database Can Be??

Logical Database Size

We can take advantage of DBA_SEGMENTS to calculate the logical size of database.

SQL> select sum(bytes)/1024/1024/1024 "DB Size (GB)" from dba_segments;

DB Size (GB)
------------
  269.897979

In the statement, we summed up all the size of segments including tables, indexes and large objects, etc.

Please note that, the term “logical” here means that we calculate only objects containing data, which includes tables, indexes and other segments. It doesn’t count metadata and allocated free space of data files. For calculating full occupied space by the database, you can check the next section.

Physical Database Size

You don’t have to calculate the real usage of all mount points of data files at OS-level, you can use the following SQL statement to know the information.

SQL> select (select sum(bytes/1024/1024/1024) from v$datafile) + (select sum(bytes/1024/1024/1024) from v$tempfile) + (select sum(bytes/1024/1024/1024) from v$log) + (select sum(block_size*file_size_blks/1024/1024/1024) from v$controlfile) "DB Size (GB)" from dual;

DB Size (GB)
------------
  443.017792

I added up all the size of the following files in the above statement:

  • Data files
  • Temp files
  • Log files
  • Control files

In which, control files are relatively small, you can ignore it or include it as you want.

Please note that, the physical db size is always larger than the logical one. This is natural, because the physical size includes free space in files.

Next, I guess you may be interested in checking tablespace usage by a pure SQL statement for your database.

Leave a Reply

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