Database Table Space Statistics (Free, Used, and Percent Used)

Your rating: None Average: 4.3 (4 votes)

An SQL to help gathering storage tablespace statistics. The SQL will list all tablespace names in a particular database along with available, used and percent used storage space in MB.

SELECT   a.tablespace_name, (a.BYTES / 1024) || 'MB' AS used,
         (b.BYTES / 1024) || 'MB' AS free,
         (b.largest / 1024) || 'MB' AS largest_block,
         ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) AS percent_used
    FROM (SELECT   tablespace_name, SUM (BYTES) BYTES
              FROM dba_data_files
          GROUP BY tablespace_name) a,
         (SELECT   tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) largest
              FROM dba_free_space
          GROUP BY tablespace_name) b
   WHERE a.tablespace_name = b.tablespace_name

Here is some information about the tables used in the above SQL:
DBA_FREE_SPACE describes the free extents in all tablespaces in the database.
DBA_DATA_FILES describes database files.

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!