/* ************************************************ */
/*                                                  */
/* fnctn:   Detailed Free Space                     */
/*                                                  */
/* name:    @c:\oracle\sql\freed.sql                */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

set termout off
break on today
column today new_value dba_date
select to_char(sysdate, 'mm/dd/yy  hh:mi AM') today
       from dual;
clear breaks
set termout on

set pagesize 60 linesize 80 verify off
set space 5

ttitle left 'Date: ' format a18 dba_date -
       center 'Detailed Free Space' -
       right 'Page: ' format 999 sql.pno skip 2
btitle center '    '
rem set echo on

spool freed.lst

break on tablespace skip page duplicates
compute sum of blocks on tablespace
compute sum of bytes on tablespace
column tablespace heading "Tablespace Name|"
column blocks heading "Free Extent |Size (Blocks)"
column bytes heading "Free Extent |Size  (Bytes)"
column count(blocks) heading "Number  Of      |Free Extents This Size"

select substr(tablespace_name,1,15) tablespace, 
    blocks, 
    bytes, 
    count(blocks)
from dba_free_space
group by substr(tablespace_name,1,15), 
    blocks, 
    bytes
order by substr(tablespace_name,1,15), 
    blocks desc;
commit;