How to find the Oracle Database size

Question: How to calculate the total size of an Oracle database?

1. Size of datafiles in MB:

sql> select sum(bytes)/(1024*1024) from dba_data_files;

2. Size of tempfiles in MB:

sql> select sum(bytes)/(1024*1024) from dba_temp_files;

3. Size of redo logs in MB:

– Not accounting for mirrored redolog files:

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

4. To get the used-up space of your datafiles:
This will give you the total used-up space inside the database in MB.

sql> select sum(bytes)/(1024*1024) from dba_segments;

Total Size of the 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;

Total Size of the database

You May Also Like