Tip of the Week
Tip for Week of August 31, 2003

Free Tablespace Report (including Autoextend)

This tip comes from Michael Dinh, DBA in San Diego, CA.

       
select 
  a.file_id
 ,a.tablespace_name
 
,trunc(decode(a.autoextensible,'YES',a.maxsize-a.bytes+b.free,'NO',b.fre

e)/1024/1024) free_mb
 ,trunc(a.bytes/1024/1024) size_mb
 ,trunc(a.maxsize/1024/1024) maxsize_mb
 ,a.autoextensible ae
 
,trunc(decode(a.autoextensible,'YES',(a.maxsize-a.bytes+b.free)/a.maxsiz
e*100,'NO',b.free/a.maxsize*100)) free_pct

from
  (select 
     file_id
    ,tablespace_name
        ,autoextensible
        ,bytes
        ,decode(autoextensible,'YES',maxbytes,bytes) maxsize
   from dba_data_files 

   group by file_id, tablespace_name, autoextensible, bytes,
     decode(autoextensible,'YES',maxbytes,bytes)) a,
  (select file_id, tablespace_name, sum(bytes) free
from dba_free_space
group by file_id, tablespace_name) b
where a.file_id=b.file_id(+)
and a.tablespace_name=b.tablespace_name(+)
order by a.tablespace_name asc;

   FILE_ID TABLESPACE    FREE_MB    SIZE_MB MAXSIZE_MB AE    FREE_PCT
---------- ---------- ---------- ---------- ---------- --- ----------
         5 TEST            32752         16      32767 YES         99
         8 TEST                0          5          5 NO          18
         4 TOOLS             240        256        256 NO          93

FREE_MB    - Actual FREE size with consideration for autoextend.
SIZE_MB    - Current TS size.
MAXXIZE_MB - Max TS size;  32768 implies unlimited.
AE         - Autoextend?
FREE_PCT   - Actual FREE percentage with consideration for autoextend.