This script displays tablespace free space and fragmentation for each tablespace. It prints the total size, the amount of space available, and a summary of free space fragmentation in that tablespace.
This can serve as an early warning system to prevent database space allocation crises. Note that no attempt is made to coalesce adjacent fragments. It is thus possible sometimes to obtain an extent larger than the size reported as the Largest Free Frag.
Since a large space-consuming extent may or may not be populated with rows, this script can mislead a DBA into believing that there is much less free space in a database than there actually is.
The script:
ttitle - center 'Database Freespace Summary' skip 2 comp sum of nfrags totsiz avasiz on report break on report col tsname format a16 justify c heading 'Tablespace' col nfrags format 999,990 justify c heading 'Free|Frags' col mxfrag format 999,999,990 justify c heading 'Largest|Frag (KB)' col totsiz format 999,999,990 justify c heading 'Total|(KB)' col avasiz format 999,999,990 justify c heading 'Available|(KB)' col pctusd format 990 justify c heading 'Pct|Used' select total.tablespace_name tsname, count(free.bytes) nfrags, nvl(max(free.bytes)/1024,0) mxfrag, total.bytes/1024 totsiz, nvl(sum(free.bytes)/1024,0) avasiz, (1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd from dba_data_files total, dba_free_space free where total.tablespace_name = free.tablespace_name(+) and total.file_id=free.file_id(+) group by total.tablespace_name, total.bytes /
Here’s a sample output from the above script:
Database Freespace Summary
Free Largest Total Available Pct
Tablespace Frags Frag (KB) (KB) (KB) Used
---------------- -------- ------------ ------------ ------------ ----
DES2 1 30,210 40,960 30,210 26
DES2_I 1 22,848 30,720 22,848 26
RBS 16 51,198 59,392 55,748 6
SYSTEM 3 4,896 92,160 5,930 94
TEMP 5 130 550 548 0
TOOLS 10 76,358 117,760 87,402 26
USERS 1 46 1,024 46 96
-------- ------------ ------------
sum 37 342,566 202,732