How To Check Database Size in Oracle

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

The 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 results in bytes. To display the result in MB divide bytes by 1024*1024, and similarly to get the result in GB divide the 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 the 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 stores 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

Also Read,

• How to unlock Scott user in SQL Plus
• Error ORA 01031 insufficient privileges