Blog

Welcome to our guide on how to find Oracle database size. Whether you’re a beginner or an expert, you’ll find valuable insights here.

1. To find out the total size allocated to datafiles in your Oracle database, use the following query:

SQL> SELECT SUM(bytes)/1024/1024/1024 as DB_SIZE_IN_GB from dba_data_files;

DB_SIZE_IN_GB
-------------
 1.61621094

2. Determine how much space is occupied by data including all objects like tables, indexes, etc:

SQL> select sum(bytes)/1024/1024/1024 as DB_SIZE_IN_GB from dba_segments;

DB_SIZE_IN_GB
-------------
   1.40332031

3. Check total database size including datafiles, temp files, redo logs, and control files:

Select (Select Sum(Bytes)/1024/1024/1024 Data_Size From Dba_Data_Files) +
(Select Nvl(Sum(Bytes),0)/1024/1024/1024 Temp_Size From Dba_Temp_Files) +
(Select Sum(Bytes)/1024/1024/1024 Redo_Size From Sys.V_$Log) +
(Select Sum(Block_Size*File_Size_Blks)/1024/1024/1024 Controlfile_Size From V$Controlfile) "DB SIZE(GB)" From Dual;

4. Check free space and used space in the database:

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

Allocated_Space(GB) Used_Space(GB) Free_Space(GB)
------------------- -------------- --------------
         1.64201183     1.42332287     .218688965

I hope you found this article helpful in finding the Oracle database size. Feel free to reach out if you have any questions. you can check our related post on How to check object size in Oracle.

Share on Facebook

Post on X

Follow us

Save