/* ************************************************ */
/*                                                  */
/* fnctn:   Table Utilization                       */
/*                                                  */
/* name:    @c:\oracle\sql\tbl_util.sql             */
/* date:    12-MAY-95                               */
/* creator: Rick Kupcunas (Oracle)                  */
/*                                                  */
/* ************************************************ */

truncate table table_utilization;

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;

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 9990
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 4 desc
/
