• Arun Kumar

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

Check db size – large database

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


Please note: this query rounds off the output and hence does not show you the exact utilization.

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

Exact database size

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

"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(GB)",
(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

Analyzing query output

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.

Check users & space used

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

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


Recent Posts

See All

Generate Table DDL Command

The DBMS_METADATA package allows you to generate DDL commands for any of the database objects inside Oracle database. Generate Table DDL Command Generate View DDL Command Generate Materialized View DD

Temp Tablespace Utilization

It is very common that as a DBA you will encounter ‘ORA-1652: unable to extend temp segment’. Handling temporary tablespace is different than permanent tablespaces inside Oracle. On a high level, temp