How To Check Database Size in Oracle

How To Check Database Size in Oracle | Database size is nothing but sum of the physical data file sizes.

Actual size of the Oracle database

The information about files is stored under dba_data_files. It stores file_id, bytes, status, maxbytes, maxblocks, increment_by, user_bytes, user_blocks, online status.

SQL> SELECT SUM(bytes) FROM dba_data_files;

SUM(BYTES)
----------
1950351360

We can also use v$datafile view.

SQL> SELECT SUM(bytes) FROM v$datafile;

SUM(BYTES)
----------
1950351360

It gives result in bytes. To display the result in MB divide bytes by 1024*1024, and similarly to get result in GB divide result by 1024*1024*1024. Example:-

SQL> SELECT SUM(bytes / (1024*1024)) 
     "DB Size (in MB)" FROM dba_data_files;

DB Size in MB
-------------
         1860
SQL> SELECT SUM(bytes / (1024*1024*1024))
     "DB Size (in GB)" FROM dba_data_files;

DB Size (in GB)
---------------
     1.81640625

Size occupied by Data

It is not necessary that all spaces are allocated. There could be sections of these files that are not used. The total space that is currently used can be calculated from dba_segments.

SQL> SELECT SUM(bytes)/(1024*1024) 
     "DB Size (in MB)" FROM dba_segments;

DB Size (in MB)
---------------
       1717.125

We can get the size based on owner/user.

SQL> SELECT owner, SUM(bytes)/(1024*1024) "DB Size(in MB)"
     FROM dba_segments GROUP BY owner;

Overall size of Oracle database

If you also want to include temporary files along with dba_data_files then we have to use dba_temp_files, which store the information about temporary files in the Oracle database.

SQL> SELECT (SELECT SUM(bytes)/(1024*1024) FROM dba_data_files) +
     (SELECT SUM(bytes)/(1024*1024) FROM dba_temp_files)
     "Size in MB" FROM dual;

Size in MB
----------
      1880

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or you find anything incorrect? Let us know in the comments. Thank you!

Also Read,

Leave a Comment

Your email address will not be published. Required fields are marked *