freespace.sql

REM name: freespace.sql
REM     This script is used to list database freespace, total database
REM space, largest extent, fragments and percent freespace. 
REM  
REM  Usage sqlplus system/passwd @freespace
REM  
REM     Date         Create           Description
REM    30-Oct-96   F. Zhang           Initial creation
REM 
REM  dba tool key: freespace.sql -- list database freespace, total space and percent free 
REM 
set pau off
col free heading 'Free(Mb)' format 99999.9
col total heading 'Total(Mb)' format 999999.9
col used heading 'Used(Mb)' format 99999.9
col pct_free heading 'Pct|Free' format 99999.9
col largest heading 'Largest(Mb)' format 99999.9
compute sum of total on report
compute sum of free on report
compute sum of used on report
break on report
select substr(a.tablespace_name,1,13) tablespace,
round(sum(a.total1)/1024/1024, 1) Total,
round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1) used,
round(sum(a.sum1)/1024/1024, 1) free,
round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1) pct_free,
round(sum(a.maxb)/1024/1024, 1) largest,
max(a.cnt) fragment
from
(select tablespace_name, 0 total1, sum(bytes) sum1, 
max(bytes) MAXB,
count(bytes) cnt
from dba_free_space
group by tablespace_name
union
select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files
group by tablespace_name) a
group by a.tablespace_name;