freespace.sql

 

---

 rem ======================================================================
 rem Name:  freespace.sql
 rem Desc:  Determine the free space for all of the tablespaces.
 rem
 rem ======================================================================
 
 rem Set the general display parameters for the report:
 set pages 55
 set lines 79
 set pause off
 set echo off
 set feed 1
 set termout on
 
 rem Capture the current date and time:
 set termout off
 col currdate new_value now
 select to_char(sysdate, 'mm/dd/yy hh24:mi') currdate from dual;
 set termout on
 
 rem The title for the report:
 ttitle left   "freespace.sql" -
        center "Tablespace Freespace" -
        right  "Page:" sql.pno  skip 1 -
        right  "Date: " now  skip 3 
 
 rem Set the column formats for the report:
 col tablespace_name form a15 head 'TABLESPACE'
 col free_segments form 999 head 'FREE|SEGS'
 col maxbytes form 99,999,999,999 head 'LARGEST|SEGMENT'
 col sumbytes form 99,999,999,999 head 'TOTAL FREE'
 
 select tablespace_name, count(*) free_segments, 
        sum(bytes) sumbytes, max(bytes) maxbytes
 from   sys.dba_free_space
 group by tablespace_name
 order by 1
 
 
 spool freespace.lst
 /
 spool off
 
 exit
 

---