How to Check Oracle Database Size?

Oracle Database is ranked as the world’s top relational database by DB-Engine, which even provides some additional features that make it a multi-model database. While working with Oracle databases many people sometimes want to know the size of a database, but find it a difficult task. The size of the database is the sum utilization and free space of datafiles, tempfiles, control file, and redo logs of the database.

This post will discuss how to check the size of the oracle database.

Prerequisite: Login to Oracle Database

Before beginning with this post, select the database for which you want to check the size. For that purpose, search and open “SQL PLUS”:

Provide the username and password of the database to log in successfully:

After the successful login, let’s see how to check the size of this database.

How to Check Oracle Database Size?

Oracle allows you to check the size of the data files, temp files, log files, and control files separately or at once.

Check Size of datafiles in MBs

The datafiles are physical files that store the data of all logical structures in the database. Run this command to see the size of datafiles in MBs:

select sum(bytes)/(1024*1024) datafiles_size from dba_data_files;

The output displays the size of datafiles as “2230” MBs:

Check Size of tempfiles in MBs

The temp files aid in storing temporary data, such as global temporary tables. Run this command to check the size of tempfiles in MBs:

Select sum(bytes)/(1024*1024) tempfiles_size from dba_temp_files;

Output

Check Size of redo logs in MBs

The redo logs are files that aid in the recovery of Oracle database in case of any failure. Write out this command to check the size of redo logs in MBs:

select sum(bytes)/(1024*1024) redo_logs_size from v$log;

The output is showing the size of redo logs:

Check Size of the control file in MBs

The control file that contains the physical structure of Oracle database and its relevant size can be checked with the help of the following command:

select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/(1024*1024) controlfile_size from v$controlfile;

The output will provide the size of control file in MBs:

Check Size of Oracle Database

The size of the database depends upon the sum of the temp files, log files, control files and data files used space plus free space. Run this command to find the size of the Oracle database in MBs:

select a.data_size+b.temp_size+c.redo_size+d.cont_size "Oracle_Database_Size"
from ( select sum(bytes)/(1024*1024) data_size
from dba_data_files ) a,
( select nvl(sum(bytes)/(1024*1024) ,0) temp_size
from dba_temp_files ) b,
( select sum(bytes)/(1024*1024) redo_size
from sys.v_$logfile lf, sys.v_$log l
where lf.group# = l.group#) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/(1024*1024) cont_size
from v$controlfile ) d;

It can be observed that the current size of our Oracle database is “3102.6875” MBs:

That was all about checking the size of the Oracle database.

Conclusion

The size of the Oracle database depends upon the sum of used and free space of datafiles, tempfiles, control files and redo logs. Use SQL PLUS to log in to a specific database and run the command given in this article to find its size. This post provided information about the Oracle Database Size and demonstrated the approach to find it using command line.

How to Check Oracle Database Size?

How to Check Oracle Database Size?

Conclusion

About the author

Nimrah Ch