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!