Find the size of an Oracle database

A very common question in any interview is "How do you find the size of a database?" This question can be answered in numerous ways. For e.g., some might say that the size of the database is the sum of the size of each datafile. Some might add the size of the tempfiles to the afore mentioned answer.

However, according to me, the size of the database can be calculated as below:

Database size = size of datafiles + size of tempfiles + size of redologs + size of controlfiles

The above formula can be put to use using the below mentioned SQL.

set serveroutput on
declare
DB_SIZE number;
dfsize number;
tfsize number;
rlsize number;
cfsize number;
begin
select sum(bytes)/1024/1024 "DATA_FILE_SIZE" into dfsize from dba_data_files;
select sum(bytes)/1024/1024 "TEMP_FILE_SIZE" into tfsize from dba_temp_files;
select sum(bytes)/1024/1024 "REDO_LOG_SIZE" into rlsize from v$log;
select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE" into cfsize from sys.v_$controlfile where rownum = 1;
DB_SIZE:=round((dfsize+tfsize+rlsize+cfsize)/1024,3);
dbms_output.put_line('-------------------------------------');
dbms_output.put_line('The Database Size is ' || DB_SIZE || ' Gigabytes');
dbms_output.put_line('-------------------------------------');
end;
/



Eg:

SQL > set serveroutput on
SQL > declare
2 DB_SIZE number;
3 dfsize number;
4 tfsize number;
5 rlsize number;
6 cfsize number;
7 begin
8 select sum(bytes)/1024/1024 "DATA_FILE_SIZE" into dfsize from dba_data_files;
9 select sum(bytes)/1024/1024 "TEMP_FILE_SIZE" into tfsize from dba_temp_files;
10 select sum(bytes)/1024/1024 "REDO_LOG_SIZE" into rlsize from v$log;
11 select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE" into cfsize from sys.v_$controlfile where rownum = 1;
12 DB_SIZE:=round((dfsize+tfsize+rlsize+cfsize)/1024,3);
13 dbms_output.put_line('-------------------------------------');
14 dbms_output.put_line('The Database Size is ' || DB_SIZE || ' Gigabytes');
15 dbms_output.put_line('-------------------------------------');
16 end;
17 /
-------------------------------------
The Database Size is 88.259 Gigabytes
-------------------------------------

PL/SQL procedure successfully completed.