Check the percentage free space on autoextend tabelspaces

From Peter Pap's Technowiki
Jump to: navigation, search
 select  df.TABLESPACE_NAME,
       round(((df.BYTES - fs.BYTES) / df.BYTES) * 100) usage_pct,
       round(decode(df.MAXBYTES, 34359721984, 0, (df.BYTES - fs.BYTES) / df.MAXBYTES * 100)) max_pct,
       df.AUTOEXTENSIBLE
 from
   (
       select  TABLESPACE_NAME,
               sum(BYTES) BYTES,
               AUTOEXTENSIBLE,
               decode(AUTOEXTENSIBLE, 'YES', sum(MAXBYTES), sum(BYTES)) MAXBYTES
       from    dba_data_files
       group   by TABLESPACE_NAME,
               AUTOEXTENSIBLE
   )
   df,
   (
       select  TABLESPACE_NAME,
               sum(BYTES) BYTES
       from    dba_free_space
       group   by TABLESPACE_NAME
   )
   fs
 where   df.TABLESPACE_NAME=fs.TABLESPACE_NAME
 order   by df.TABLESPACE_NAME asc;

This will generate output like this:

 TABLESPACE_NAME                 USAGE_PCT    MAX_PCT AUT
 ------------------------------ ---------- ---------- ---
 MYDATA                                 99         18 YES
 SYSAUX                                 97         28 YES
 SYSTEM                                 98         18 YES
 UNDOTBS1                                2          0 YES
 USERS                                   1          0 YES

where USAGE_PCT is the current % usage of unextended data files, and MAX_PCT is the percentage of the extendable space that has been used, i.e. the actual percentage free space.