At least two scenarios that you may need to know the database size.
- Server upgrade or migration
- Prediction on space growth
You have to plan the size of storage and create reasonable tablespaces in advance.
You have to log the overall space usage on weekly or monthly basis in order to predict the database size in the future.
Further reading: How Big a Database Can Be??
Logical Database Size
We can take advantage of DBA_SEGMENTS to calculate the logical size of database.
SQL> select sum(bytes)/1024/1024/1024 "DB Size (GB)" from dba_segments;
DB Size (GB)
In the statement, we summed up all the size of segments including tables, indexes and large objects, etc.
Please note that, the term “logical” here means that we calculate only objects containing data, which includes tables, indexes and other segments. It doesn’t count metadata and allocated free space of data files. For calculating full occupied space by the database, you can check the next section.
Physical Database Size
You don’t have to calculate the real usage of all mount points of data files at OS-level, you can use the following SQL statement to know the information.
SQL> select (select sum(bytes/1024/1024/1024) from v$datafile) + (select sum(bytes/1024/1024/1024) from v$tempfile) + (select sum(bytes/1024/1024/1024) from v$log) + (select sum(block_size*file_size_blks/1024/1024/1024) from v$controlfile) "DB Size (GB)" from dual;
DB Size (GB)
I added up all the size of the following files in the above statement:
- Data files
- Temp files
- Log files
- Control files
In which, control files are relatively small, you can ignore it or include it as you want.
Please note that, the physical db size is always larger than the logical one. This is natural, because the physical size includes free space in files.
Next, I guess you may be interested in checking tablespace usage by a pure SQL statement for your database.