Calculate Oracle Database Size


Objective:  to calculate all kinds of database sizes.
Items:

Size of datafiles in MB
select sum(bytes)/(1024*1024) from dba_data_files;
 
 
Size of tempfiles in MB
select sum(bytes)/(1024*1024) from dba_temp_files;
 
 
Size of redo logs in MB 
Not accounting for mirrored redolog files:
select sum(bytes)/(1024*1024) from v$log;
 
 
To get the used-up space of your datafiles:
This will give you the total used-up space inside the database in MB.
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 and free size:
select round(sum(used.bytes) / 1024 1024 ) || ' MB' "Database Size"
, round(free.p / 1024 1024) || ' MB' "Free space"
from (select bytes from v$datafile
 union all
 select bytes from v$tempfile
 union all
 select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p;
 
Individual tablespace size:
select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Size"
from dba_data_files
 group by tablespace_name
 ORDER BY 1;
 
Individual tablespace used size:
select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Used Size"
from dba_segments
 GROUP BY TABLESPACE_NAME
 order by 1;
 
Individual tablespace free space:
select tablespace_name as "Tablespace Name", sum(bytes)/(1024*1024) as "Free Space"
 from dba_free_space
 group by tablespace_name
 order by 1;

References:
How to Calculate the Size of the Database (Doc ID 1360446.1)

No comments:

Post a Comment