Title: Viewing Free Space, Largest Extent, and Fragment Info In One Report

Author: Fan Zhang an Oracle DBA, for ACS/TradeOne Marketing, in Austin, Texas.

Use this script to list database used and free space, largest extent, percent of freespace, number of fragments in each
tablespace and total database space size. By using this script you can quickly determine if you have enough tablespace, the
largest extent to create tables and indexes, and so forth. You can also use the list to determine if you need to use the
defragment process.

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
/

set pau on

exit;


The sample output:

                                               Pct
TABLESPACE    Total(Mb) Used(Mb) Free(Mb)     Free Largest(Mb)  FRAGMENT
------------- --------- -------- -------- -------- ----------- ---------

MR_DATA_00         30.0     17.2     12.8     42.7        12.3         9

MR_INDEX_00        40.0     30.0     10.0     25.0         8.3       103

MR_ROLLBACK        20.0      4.2     15.8     79.0          .2        96

MR_TEMPORARY       30.0      9.8     20.2     67.3         7.7        13

MR_VOX_00          20.0     10.7      9.3     46.5         5.8         2

MR_VOX_01          10.0      9.0      1.0     10.0         1.0         1

MR_WORK_00          5.0      2.1      2.9     58.0         2.9         2

SYSTEM             40.0     38.0      2.0      5.0         2.0         1
              --------- -------- --------
sum               195.0    121.0     74.0