Database Table Space Statistics

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.

转载于:https://www.cnblogs.com/GoDevil/archive/2009/07/27/1531998.html