Wednesday, April 6, 2011

Estimate Oracle Database Size

Oracle database is consists of datafiles, controlfiles and redolog files . Therefore , the size of oracle database can be calculated by adding above files. The below script will estimate the oracle database size .

SQL> select a.datafile_size + b.temp_size + c.redo_size d.controlfile_size  "Total_size in GB"
from ( select sum(bytes)/1024/1024/1024  as datafile_size 
from dba_data_files) a,
( select nvl(sum(bytes),0)/1024/1024/1024 as temp_size 
from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 as  redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 as  controlfile_size 
from v$controlfile) d
output :

Total_size in GB
----------------
       1.9475708


Enjoy     :-) 


No comments: