Tablespace Free Space and Max Size

Earlier, provided a small script to find the maximum growable size of a tablespace... Here is little more comprehensive script that shows more tablespace space info. Current size, growable size based on the autoextensible data files, free space, biggest chunk...All size is in MB.


SELECT a.tablespace_name TSNAME, 
        ROUND(SUM(a.growable)/1048576) growable, 
        ROUND(SUM(a.tots)/1048576) Tot_Size,
        ROUND(SUM(a.sumb)/1048576) Tot_Free,
        ROUND(SUM(a.sumb)*100/sum(a.tots)) Pct_Free,
        ROUND(SUM(a.largest)/1048576) Large_Ext
FROM   (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
               MAX(bytes) largest, 0 growable
        FROM   dba_free_space a
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_data_files
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0,  SUM (GREATEST (bytes, DECODE (autoextensible, 'YES', maxbytes, 0))) growable
        FROM   dba_temp_files
        GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY rollup(a.tablespace_name)
ORDER by a.tablespace_name

Enjoy!