/* ************************************************ */
/*                                                  */
/* fnctn:   Fragmented TableSpace                   */
/*                                                  */
/* name:    @c:\oracle\sql\fragmntd.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* The 'dead' column is based on the assumption that*/
/*   any contiguous block smaller then 5 Oracle     */
/*   blocks cannot be used.                         */
/* ************************************************ */
set linesize 132;

spool fragmentd.rslt;

drop table table_utilization;

create table table_utilization
    (tablespace_name      varchar2(50),
     total_bytes          number,
     free_bytes           number,
     fragments            number)
PCTFREE              10
STORAGE (initial     8k
         next        8k
         minextents  1
         maxextents  245
         pctincrease 0);

insert into table_utilization
    (tablespace_name, total_bytes) 
     select tablespace_name,sum(bytes)
       from dba_data_files
       group by tablespace_name;

update table_utilization b
  set free_bytes = (select sum(a.bytes) 
                      from dba_free_space a
                      where a.tablespace_name = b.tablespace_name),
      fragments  = (select count(*) 
                      from dba_free_space c
                      where c.tablespace_name = b.tablespace_name);

commit;

set feedback on
set verify on

col tablespace_name heading "TableSpace|Name" format a30
col total_bytes heading "Size|(Kbytes)" format 999,999,990
col free_bytes heading "Free|(KBytes)" format 999,999,990
col pct heading "%|Util" format 990.990
col fragments heading "Fragments" format 999,990

select tablespace_name,
    total_bytes/1024             total_bytes,
    free_bytes/1024               free_bytes,
    (total_bytes-free_bytes)/total_bytes * 100 PCT,
    fragments
from table_utilization
order by 1 asc;

set verify off
set feedback off

drop table table_utilization;

spool off;
commit;

