Queries to find Database Size

The database mainly comprises of datafiles, temp files and redo log 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 data_size FROM dba_data_files;

To get the size of all TEMP files:

SELECT nvl(sum(bytes),0)/1024/1024 temp_size FROM dba_temp_files;

To get the size of the on-line redo-logs:

SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;

Finally, summing up the three above, total database size can be found:

SELECT (dsize.data_size + tsize.temp_size + rsize.redo_size)/1024/1024 "total_size"
FROM (SELECT sum(bytes) data_size
FROM dba_data_files ) dsize,
(SELECT nvl(sum(bytes),0) temp_size
FROM dba_temp_files ) tsize,
(SELECT sum(bytes) redo_size
FROM sys.v_$log ) rsize;