• 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:

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
);


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;



163 views

Recent Posts

See All

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

Estimate flashback destination size

Sometimes application team will ask DBA to enable flashback for x number of days. In such case, a DBA needs to estimate the flashback space required for x number of days in order to store the flashbac

Datafile utilization check

When you want to shrink a datafile, you must always check the single datafile utilization. In case if you shrink datafile more than the used size, it will fail. Below query gives the datafile utilizat

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback