Check Oracle database size

There are several ways to measure the size of an Oracle database. As an Oracle DBA, you may face the requirement to get the current database size. Find below the queries which you can use to find the size of the Oracle database.

Find Oracle Database Size

For very big databases where the size run into multiple TBs, below command will help you get a bird’s eye view on the database size, used space and free space.

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Here is another query that finds the database size based on the physical space consumed on the disks

select
"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)",
"Free_Space(GB)"
from(
select 
(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)",
(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)"
from dual
);

When you run the above query, you will see below output

Reserved_Space(GB) Used_Space(GB) Free_Space(GB)
------------------ -------------- --------------
        1.43491124     1.34488439     .090026855

We can see that 1.4 GB is the allocated space across all the data files in the database. Out of the 1.4 GB allocated segments, 1.3 GB is used and 0.09 GB is free space.

Find Space Used by Users Inside Database

We can even check the amount of disk space used by individual users inside the database using below query

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