From time to time, it’s useful to know the total allocated size of a database at OS level, how much of it has been used and what the maximum total size the database can grow to at OS level.

The below query will give you this:

SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
FROM
(SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
df.bytes / (1024 * 1024) curr_ts_size,
df.maxbytes / (1024 * 1024) max_ts_size
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes,df.maxbytes);

Output:

SQL> SELECT round(sum(used_ts_size)/1024/1024, 2) total_used_db_size_tb,
 2 round(sum(curr_ts_size)/1024/1024, 2) total_current_db_size_tb,
 3 round(sum(max_ts_size)/1024/1024, 2) total_max_allocated_db_size_tb
 4 FROM
 5 (SELECT df.tablespace_name, (df.bytes - sum(fs.bytes)) / (1024 * 1024) used_ts_size,
 6 df.bytes / (1024 * 1024) curr_ts_size,
 7 df.maxbytes / (1024 * 1024) max_ts_size
 8 FROM dba_free_space fs,
 9 (select tablespace_name,
 10 sum(bytes) bytes,
 11 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes
 12 from dba_data_files
 13 group by tablespace_name) df
 14 WHERE fs.tablespace_name (+) = df.tablespace_name
 15 GROUP BY df.tablespace_name,df.bytes,df.maxbytes);

TOTAL_USED_DB_SIZE_TB TOTAL_CURRENT_DB_SIZE_TB TOTAL_MAX_ALLOCATED_DB_SIZE_TB
--------------------- ------------------------ ------------------------------
                 7.15                     7.36                           9.04

SQL>

The unit is in Tb, which should be suitable for most, however this can be changed by add/removing division of 1024.

Related Post:
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)