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

Your rating: None Average: 5 (3 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
ORDER BY ((a.BYTES - b.BYTES) / a.BYTES) DESC;

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!

Comments

Have a question? Please ask it on the forum instead.

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
12 + 4 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.