Oracle: Get database size

 

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;

Putting it all together into a single query:

col total_size format 999999999999999

select a.data_size+b.temp_size+c.redo_size "total_size"

from ( select sum(bytes) data_size

         from dba_data_files ) a,

     ( select nvl(sum(bytes),0) temp_size

         from dba_temp_files ) b,

     ( select sum(bytes) redo_size

         from sys.v_$log ) c;

The size of the database is the space the files physically consume on disk. You can find this with:

select sum(bytes)/1024/1024 size_in_mb from dba_data_files;

But not all this space is necessarily allocated. There could be sections of these files that are not used.

You can find the total space that is used with:

select sum(bytes)/1024/1024 size_in_mb from dba_segments;

You can break this down by user by running:

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments

group  by owner;