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 .
Total_size in GB
----------------
1.9475708
Enjoy :-)
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 :
output :
Total_size in GB
----------------
1.9475708
Enjoy :-)
2 comments:
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;
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;
Post a Comment