What is the overall database size

An oracle database consists of data files, redo log files, control files, temporary files. Whenever you say the size of the database this actually means the summation of these files.

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:
select sum(bytes)/1024/1024 "Meg" from dba_data_files;

To get the size of all TEMP files:
select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;

To get the size of the on-line redo-logs:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;


To get the size of the control files use,
SQL> select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "MEG" from v$controlfile;

So to get the total size of the database just sum these.

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;


Related Documents

Maximum Oracle Database Size.

Choose an optimal Data block size in Oracle

How to determine OS block size for windows or unix

Estimate the dumpfile size before taking dump using data pump export

Settting maximum Size of Backup Set or Backup Piece