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)
Posts
No comments:
Post a Comment