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     :-) 


2 comments:

Unknown said...

it big command for find db_size try this one.

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select sum(bytes)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 v$controlfile_size from $controlfile) "Size in GB"
from dual;

Unknown said...

try this one for findselect
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select sum(bytes)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 v$controlfile_size from $controlfile) "Size in GB"
from dual;