How to Find Out the size of your Oracle Database

As an Oracle DBA this is the most important interview question and at any time you may face an issue where you need to find out the size of the Oracle Database. In detail, here you will get to know the actual size of the database that comprises of only data files when no redo and temp are generated. In second case, you may asked to find out the overall database size that contains all the data files, temp files and the redo logs (free+used space). Third, is the size occupied by data in this database or you can say  Database usage details which you can get using dba_segments. 

To Find the Actual size of the Database in GB

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

To Find the Size Occupied by Data in the Database 

Gives the size occupied by data in this database or Database usage details.

SELECT SUM (bytes)/1024/1024/1024 AS GB FROM dba_segments;

To Find the Overall/Total Database Size

Overall database size is the sum of used space plus free space i.e. the size of the data files, temp files, log files and the control files. You can find out the total database size using simple query. This sql gives the total size in GB.  

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/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 controlfile_size from v$controlfile) "Size in GB"
from
dual