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;