REM
REM Check for available free space
REM
TTITLE CENTER 'Tablespace free space' SKIP 1
COLUMN tsname        FORMAT a25
COLUMN allocation_type FORMAT A4 TRU HEADING ALLO
COLUMN contents      FORMAT A4 TRU HEADING MGMT
COLUMN Tot_Size      FORMAT 999,999 HEADING "TOTAL(M)"
COLUMN Tot_Free      FORMAT 999,999 HEADING "FREE(M)"
COLUMN Pct_Free      FORMAT 999 HEADING "FREE %"
COLUMN Fragments     FORMAT 99,999 HEADING "FRAGMTS"
COLUMN Large_Ext     FORMAT 999,999 HEADING "LARG(M)"
spool /tmp/freespace8i.lst
set feedback off pages 999 trims on 
SELECT a.tablespace_name TSNAME, SUM(a.tots)/1048576 Tot_Size,
       SUM(a.sumb)/1048576 Tot_Free,
       SUM(a.sumb)*100/sum(a.tots) Pct_Free,
       SUM(a.largest)/1048576 Large_Ext, SUM(a.chunks) Fragments,
       b.contents, b.allocation_type
FROM   (SELECt tablespace_name, 0 tots, SUM(bytes) sumb,
               MAX(bytes) largest, COUNT(*) chunks
        FROM   dba_free_space a
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0
        FROM   dba_data_files
        GROUP BY tablespace_name
        UNION
        SELECT tablespace_name, SUM(bytes) tots, 0, 0, 0
        FROM   dba_temp_files
        GROUP BY tablespace_name) a, dba_tablespaces b
WHERE b.tablespace_name = a.tablespace_name
GROUP BY a.tablespace_name, b.contents, b.allocation_type
/
spool off
ttitle off
set feedback on pages 24
prompt 
prompt Output saved at /tmp/freespace8i.lst
prompt