set lines 100
TTITLE OFF
column todays_date NEW_VALUE title_date
column db_name NEW_VALUE database_name
set termout off
select to_char(sysdate,'DD-MON-YY') todays_date from sys.dual;
select name db_name from v$database;
TTITLE LEFT &title_date CENTER &database_name SKIP 2
column tablespace_name format a16
column files heading "no|file" format 9999
column total_kb heading "total|KB" format 999,999,999
column free_kb heading "free|KB" format 999,999,999 print
column fragments heading "no|frags" format 9999
column max_frag heading "lrgst|frag" format 9,999,999
column min_frag heading "smlst|frag" format 9,999,999
column pct_used heading "PCT|Used" format 999
column lfi heading "FSFI" format 999 noprint
column tfi heading "TFI" format 999 noprint
column used_kb heading "Used|KB" format 99,999,999 noprint

break on report
compute sum of files on report
compute sum of total_kb on report
compute sum of free_kb on report
compute sum of used_kb on report

set pages 60
spool space.lis

select a.tablespace_name tablespace_name,
round(a.total_bytes/1024) total_KB,
a.no_of_files files,
round(b.free_bytes/1024) free_KB,
round((a.total_bytes-b.free_bytes)/1024) used_kb,
b.fragments ,
round(b.max_frag/1024) max_frag,
round(b.min_frag/1024) min_frag,
round((a.total_bytes-b.free_bytes)*100/a.total_bytes) pct_used,
a.lfi,c.tfi
from (select tablespace_name,
sum(bytes) total_bytes,
count(bytes) no_of_files,
sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)))) lfi
from sys.dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) free_bytes,
count(bytes) fragments,
max(bytes) max_frag,
min(bytes) min_frag
from sys.dba_free_space
group by tablespace_name) b,
(select tablespace_name,avg(extents) avg_ext,max(extents) max_ext,
(1/sqrt(greatest(avg(extents)/5,1))*
(1/sqrt(greatest(max(extents)/10,1))))*100 tfi
from sys.dba_segments
group by tablespace_name) c
where a.tablespace_name =b.tablespace_name(+)
and c.tablespace_name(+) =b.tablespace_name
/
spool off
ttitle off
undefine 1
set termout on