How to Check Database Size in Oracle 19c

You often need to know the size of an Oracle database to move it from one server to another. Or just to know if your export or whatever is going to take a lot of space and time.

In this article I show you a simple way to calculate the size of your Oracle database.

Size of Oracle datafiles in GB

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

Size of Oracle tempfiles in GB

select sum(bytes)/(1024*1024*1024) G 
from dba_temp_files;

Size of Oracle redo logs in GB

Not accounting for mirrored redolog files:

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

Size of used-up space of your Oracle datafiles

This will give you the total used-up space inside the database in GB.

select sum(bytes)/(1024*1024*1024) G 
from dba_segments;

Total Size of the Oracle database

Also accounting for controlfiles and mirrored redolog files.

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

Leave a Reply

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