Script: Free Space Report for all Datafiles and if AUTOEXTENT is ON

-- ################################################################

--

-- %Purpose: Free Space Report for all Datafiles and if AUTOEXTENT is ON

--

--################################################################

--

clear columns -

      breaks -

      computes

set pagesize 100

column file_name format a32

column tablespace_name format a15

column status format a3 trunc

column t format 999,999.000 heading "Total MB"

column a format a4 heading "Aext"

column p format 990.00 heading "% Free"

SELECT  df.file_name,

        df.tablespace_name,

        df. status,

        (df.bytes/1024000) t,

        (fs.s/df.bytes*100) p,

        decode (ae.y,1,'YES','NO') a

  FROM  dba_data_files df,

        (SELECT file_id,SUM(bytes) s

           FROM dba_free_space

           GROUP BY file_id) fs,

        (SELECT file#, 1 y

           FROM sys.filext$

           GROUP BY file#) ae

  WHERE df.file_id = fs.file_id

    AND ae.file#(+) = df.file_id

ORDER BY df.tablespace_name, df.file_id;

column file_name clear

column tablespace_name clear

column status clear

column t clear

column a clear

column p clear

ttitle off