A Faster Tablespace Sizing Script

     I have some databases with many thousands of tables and many of those tables are quite large containing billions of rows.  Several of them have over a million extents.  As I am sure you can image, this takes a terrible performance toll on the traditional tablespace sizing scripts which utilized sources such as dba_data_files and dba_free_space.
     Then along comes a new view in 11g that has changed my worst performer from around 30 minutes to sub-second response time. Got to love that. It is the dba_tablespace_usage_metrics view.
     This is the same view used by Oracle Enterprise Manager (OEM) to generate tablespace consumptions alerts.  Here is the code for a much faster, and simpler tablespace sizing script without all the union all's and such.
     Enjoy:




set linesize 188 trimspool on pagesize 66


column tablespace_name format a24
column TOTAL_MB format 999,999,999
column MB_USED format 999,999,999
column PERCENT_USED format 999.9


select tablespace_name
      ,round((tablespace_size*32768)/1048576,0) as TOTAL_MB
      ,round((used_space*32768)/1048576,0) as MB_USED
      ,round(USED_PERCENT,1) as PERCENT_USED
from dba_tablespace_usage_metrics
order by PERCENT_USED desc;